sql - 按州返回前 5 名销售额

Question

我正在尝试编写一个查询，该查询将返回每个州中提供债务人姓名和净总额的前 5 名销售额。我想出了以下方法可以完成这项工作，但是，我确信有更好的方法，尤其是在添加了新状态的情况下。

我试图理解 WHERE IN 语法，但我没有理解。如有任何帮助，我将不胜感激。

SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "VI"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "NS"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "sa"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "wa"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "ta"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "ac"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "ql"
ORDER BY NetTotal DESC
UNION ALL SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "nt"
ORDER BY NetTotal DESC;

Answer 1

基于 TOP 语法，我猜您正在使用 SQL Server。您应该能够使用 row_number() 返回每个状态的前 5 个值:

select DebtorNameShort, State, NetTotal
from
(
  select DebtorNameShort, State, NetTotal,
    row_number() over(partition by state order by NetTotal desc) seq
  from data 
) d
where seq <= 5

row_number()是一个窗口函数，它允许为分区中的每个项目创建一个顺序值 - 对于您的示例，您将按 state 对数据进行分区。只要您按降序对 NetTotal 进行排序，那么您可以过滤最终结果以仅返回那些行号为 1-5 的行。