我和哥伦斯有意见现在我要每个发货区域的前2个元素。我以前是这样问的
SELECT Region, CustomerID, SUM(ExtendedPrice) AS Price FROM Invoices GROUP BY CustomerID, Region ORDER BY Region, Price DESC;
SELECT Region, CustomerID, SUM(ExtendedPrice) AS Price
FROM Invoices
GROUP BY CustomerID, Region
ORDER BY Region, Price DESC;
该查询生成如下值现在我只需要为每个区域选择两个最大值。
ifmq2ha21#
使用 row_number() :
row_number()
select rc.*from (select region, customerid, sum(extendedPrice) as price, row_number() over (partition by region order by sum(extendedprice) desc) as seqnum from invoices group by region, customerid ) rcwhere seqnum <= 2;
select rc.*
from (select region, customerid, sum(extendedPrice) as price,
row_number() over (partition by region order by sum(extendedprice) desc) as seqnum
from invoices
group by region, customerid
) rc
where seqnum <= 2;
nqwrtyyt2#
看来这样做是不对的。试试这样的。
WITH cte AS SELECT Region , CustomerID , SUM(ExtendedPrice) AS Price , ROW_NUMBER over( Region , CustomerID, SUM(ExtendedPrice) ) ORDER BY SUM(ExtendedPrice) DESC as 'row_number' FROM Invoices GROUP BY CustomerID , Region ORDER BY Region , Cena DESCSELECT region FROM cteGROUP BY region , valueHAVING row_number < 2
WITH cte AS
SELECT
Region
, CustomerID
, SUM(ExtendedPrice) AS Price
, ROW_NUMBER over( Region , CustomerID, SUM(ExtendedPrice) ) ORDER BY SUM(ExtendedPrice) DESC as 'row_number'
FROM
Invoices
GROUP BY
CustomerID
, Region
ORDER BY
, Cena DESC
region
cte
, value
HAVING
row_number < 2
此外，SQLServer还天真地支持for循环。https://docs.microsoft.com/en-us/sql/t-sql/queries/select-for-clause-transact-sql?view=sql-server-ver15这将允许您指定一个子句并按您所期望的那样对其进行迭代。
2条答案
按热度按时间ifmq2ha21#
使用
row_number():nqwrtyyt2#
看来这样做是不对的。
试试这样的。
此外，SQLServer还天真地支持for循环。https://docs.microsoft.com/en-us/sql/t-sql/queries/select-for-clause-transact-sql?view=sql-server-ver15
这将允许您指定一个子句并按您所期望的那样对其进行迭代。