我试图返回客户数据库中订购量最高的2个项目。以下是我为最常订购商品提供的商品,但我在弄清楚如何为第二个最受订购商品创建另一列时遇到了麻烦。
创建第二列的最佳方法是什么?
SELECT FirstName, EmailAddress, Id, PreferredLocationId,
(
SELECT TOP 1 [Description] FROM [Order] o
INNER JOIN [OrderItem] oi ON oi.OrderId = o.OrderId
WHERE o.CustomerId = Customer.Id
GROUP BY [Description]
ORDER BY COUNT(*) DESC
) AS MostOrderedItem
FROM Customer
GROUP BY FirstName, EmailAddress, Id, PreferredLocationId
如果您使用的是SQL Server 2012,则有很多不同的处理方式。我将使用CTE来获取前两行并使用 ROW_NUMBER()
WITH cte AS (
SELECT CustomerId, [Description]
, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY COUNT(*) DESC) [RowID]
FROM [Order] o
INNER JOIN [OrderItem] oi ON oi.OrderId = o.OrderId
GROUP BY CustomerId, [Description]
)
SELECT FirstName, EmailAddress, Id, PreferredLocationId, cte1.Description, cte2.Description
FROM Customer
LEFT JOIN cte cte1 ON cte1.CustomerID = Customer.CustomerId AND cte1.RowID = 1
LEFT JOIN cte cte2 ON cte2.CustomerID = Customer.CustomerId AND cte2.RowID = 2
公用表表达式创建所有客户,描述及其行号的列表。请注意,如果您有关系,则不会保证先描述哪个。您可以添加到开窗功能说明中,以便在有平局的情况下,打破平局的任何字母都在字母表中。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句