代码之家 › 专栏 › 技术社区 › alextansc

SQL语句帮助-选择表中记录最多的CustomerID、OrderDate列表

sql-server-2005 sql

alextansc · 技术社区 · 17 年前

我将使用 AdventureWorks Database 来说明我的问题。

SELECT CustomerID, OrderDate, COUNT(1) Cnt
FROM Sales.SalesOrderHeader
WHERE CustomerID = 11300
GROUP BY CustomerID, OrderDate
ORDER BY Cnt DESC

这将得到以下结果:

CustomerID    OrderDate             Cnt
-----------   ----------                ----
11300        2003-11-22 00:00:00.000     2
11300        2004-01-28 00:00:00.000     2
11300        2004-02-18 00:00:00.000     2
11300        2004-02-08 00:00:00.000     2
11300        2004-02-15 00:00:00.000     1
11300        2004-03-11 00:00:00.000     1
11300        2004-03-24 00:00:00.000     1
11300        2004-03-30 00:00:00.000     1
11300        2004-04-28 00:00:00.000     1
11300        2004-05-03 00:00:00.000     1
11300        2004-05-17 00:00:00.000     1
11300        2004-06-18 00:00:00.000     1
...

CustomerID    OrderDate             Cnt
-----------   ----------                ----
11300        2003-11-22 00:00:00.000     2
11300        2004-01-28 00:00:00.000     2
11300        2004-02-18 00:00:00.000     2
11300        2004-02-08 00:00:00.000     2

我被困住了,因为我无法集中精力解决两个问题:

1) 客户可能有多个具有相同Cnt值的OrderDate。这意味着我不能像TOP 1那样获得期望的结果。

SELECT CustomerID, OrderDate, COUNT(1) Cnt
FROM Sales.SalesOrderHeader
WHERE CustomerID = 11300
GROUP BY CustomerID, OrderDate HAVING COUNT(1) > 1
ORDER BY Cnt DESC

这将有助于为该客户获得正确的结果,但如果下一个客户在某一天只有一个订单,则肯定是错误的。

因此,要么在这种情况下查询是不可能的,要么我以错误的方式处理查询。对这个问题的任何意见都将不胜感激。

此外,由于这将是存储过程中的查询,因此任何关于在T-SQL中解决此问题的想法都是可以接受的。

Mehrdad ,我被介绍给 Common Table Expressions ,生活是美好的

2 回复 | 直到 9 年前

Mehrdad Afshari 17 年前

你应该使用 TOP n WITH TIES

SELECT TOP 1 WITH TIES CustomerID, OrderDate, COUNT(*) Cnt
FROM Sales.SalesOrderHeader
WHERE CustomerID = 11300
GROUP BY CustomerID, OrderDate
ORDER BY Cnt DESC

或者,您可以使用解决这个问题。此解决方案需要SQL Server 2005或更高版本:

WITH MyTable AS (SELECT CustomerID, OrderDate, COUNT(*) Cnt
                 FROM Sales.SalesOrderHeader
                 WHERE CustomerID = 11300
                 GROUP BY CustomerID, OrderDate)
SELECT CustomerID, OrderDate, Cnt
FROM MyTable
WHERE Cnt = (SELECT MAX(Cnt) FROM MyTable);

如果使用CTE方法,请确保前一条语句以分号结尾(如果它不是第一条语句)。它在CTE表达式之前是必需的。

Matt Hamilton 17 年前

我手头没有AdventureWorks的副本可供测试,但with TIES子句能帮上忙吗?你可以这样做:

SELECT TOP 1 WITH TIES CustomerID, OrderDate, COUNT(*) Cnt
...
ORDER BY COUNT(*) DESC