每个国家前3行
每个国家前3行
提问于 2014-12-02 05:51:27
我制作了一份关于“每个国家销售了哪些产品和多少产品”的报告。我正在使用Northwind数据库，SQL Server 2012。
以下是守则：
SELECT
o.ShipCountry AS 'Country',od.ProductID,
p.ProductName, p.UnitPrice,
SUM(od.Quantity) AS 'Number of Units Sold'
FROM
Products p
INNER JOIN
[Order Details] od ON od.ProductID = p.ProductID
INNER JOIN
Orders o ON o.OrderID = od.OrderID
GROUP BY
p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
ORDER BY
o.ShipCountry, 'Number of Units Sold' DESC结果显示超过900行，每个国家大约有10至20行：
但是我想把它提升一个档次，现在我想生产“每个国家销售的前3种产品”，所以我尝试了ROW_NUMBER() OVER (PARTITION BY，但是我在使用Row_NUMBER()方面很笨拙
以下是我的错误代码：
WITH CTE AS
(
SELECT
o.ShipCountry AS 'Country',od.ProductID,
p.ProductName, p.UnitPrice,
SUM(od.Quantity) AS 'Number of Units Sold',
ROW_NUMBER() OVER (PARTITION BY o.ShipCountry ORDER BY ('Number of Units Sold') DESC) AS 'Number of Units Sold'
FROM
Products p
INNER JOIN
[Order Details] od ON od.ProductID = p.ProductID
INNER JOIN
Orders o ON o.OrderID = od.OrderID)
SELECT
'Country', ProductID,
ProductName, UnitPrice, 'Number of Units Sold'
FROM
CTE
WHERE
'Number of Units Sold' < 4
GROUP BY
p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
ORDER BY
o.ShipCountry DESC回答 3
Stack Overflow用户
回答已采纳
发布于 2014-12-02 06:20:45
试试这个：
WITH CTE AS
(
SELECT
o.ShipCountry, od.ProductID,
p.ProductName, p.UnitPrice,
SUM(od.Quantity) AS UnitsSold,
RowNum = ROW_NUMBER() OVER (PARTITION BY o.ShipCountry ORDER BY SUM(od.Quantity) DESC)
FROM
Products p
INNER JOIN
[Order Details] od ON od.ProductID = p.ProductID
INNER JOIN
Orders o ON o.OrderID = od.OrderID
GROUP BY
p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
)
SELECT *
FROM CTE
WHERE CTE.RowNum <= 3基本上，在CTE中，您定义了您想要的列--警告词：不要在空格和诸如此类的东西中使用列名！在屏幕上做了一个很好的演示，但是在查询中真的很难使用！
然后添加ROW_NUMBER()，从1开始对每个国家的每个条目进行编号。
最后，您从CTE中选择，并且只使用带有RowNum <= 3 ==>的行作为每个国家的前3行。
Stack Overflow用户
发布于 2014-12-02 06:14:26
;with CTE as(
SELECT o.ShipCountry AS 'Country',
od.ProductID,
p.ProductName,
p.UnitPrice,
SUM(od.Quantity) AS 'Number of Units Sold'
FROM Products p
INNER JOIN [Order Details] od
ON od.ProductID=p.ProductID
INNER JOIN Orders o
ON o.OrderID=od.OrderID
GROUP BY p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
)
,CTE2 as
( Select
CTE.Country,
CTE.ProductID,
CTE.ProductName,
CTE.UnitPrice,
CTE.[Number of Units Sold],
ROW_NUMBER() OVER (PARTITION BY CTE.Country
ORDER BY CTE.[Number of Units Sold] DESC) AS rownum
from CTE
)
select CTE2.Country,
CTE2.ProductID,
CTE2.ProductName,
CTE2.UnitPrice,
CTE2.[Number of Units Sold]
FROM CTE2
WHERE CTE2.rownum<4
ORDER BY CTE2.Country, CTE2.[Number of Units Sold] DESC Stack Overflow用户
发布于 2014-12-02 06:27:31
试试这个：
SELECT Country, ProductID,
ProductName,UnitPrice,Number_of_Units_Sold
FROM
(
SELECT o.ShipCountry AS Country, od.ProductID as ProductID,
p.ProductName as ProductName, p.UnitPrice as UnitPrice,
SUM(od.Quantity) AS Number_of_Units_Sold,
ROW_NUMBER() OVER (PARTITION BY o.ShipCountry ORDER BY (SUM(od.Quantity)) DESC) AS MYRANK
FROM Products p
INNER JOIN [OrderDetails] od
ON od.ProductID=p.ProductID
INNER JOIN Orders o
ON o.OrderID=od.OrderID
GROUP BY o.ShipCountry, p.ProductName, od.ProductID, p.UnitPrice, o.ShipCountry
) tmp
where MYRANK <= 3
ORDER BY Country, Number_of_Units_Sold DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27242749
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号