t-SQL 查找每个组的前 10 条记录

问题描述：

我想弄清楚如何为每组 Trans.TranSID 返回前 10 条记录.

I am trying to figure out how to return the top 10 records for each group of Trans.TranSID.

SELECT a.ABID, a.ABName, t.TranSID, SUM(IIF(TranTypeID = 'CO', td.Qty * CAST(td.Price AS money) * - 1, 
                      td.Qty * CAST(td.Price AS money))) AS TotalSales
FROM         Trans t INNER JOIN
                      TransDetail td ON t.TranID = td.TranID INNER JOIN
                      ABook a ON t.TranABID = a.ABID
WHERE     (t.TranDate BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-01-01 00:00:00', 102)) AND 
           t.TranTypeID in ('SO','CA','CO') AND (t.TranStatus <> 'V')
GROUP BY a.ABID, a.ABName, t.TranSID
HAVING  (NOT (a.ABName LIKE '%cash%'))
ORDER BY t.TranSID, TotalSales Desc

我可以将TOP 10"添加到 select 语句中，但这会为我提供前 10 个帐户，而不考虑组.Trans.TranSID 有 25 组，我试图只为每个组获得前 10 名.

I can add "TOP 10" to the select statement, but that gives me the top 10 accounts regardless of the group. There are 25 groups of Trans.TranSID and I'm trying to get the top 10 only for each group.

答

我认为您正在寻找带有 PARTITION BY

I think you're looking for ROW_NUMBER() with a PARTITION BY

SELECT * 
FROM (
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY t.TranSID ORDER BY t.TranSID, SUM(IIF(TranTypeID = 'CO', td.Qty * CAST(td.Price AS money) * - 1, td.Qty * CAST(td.Price AS money))) DESC) as RowNum,            
        a.ABID, 
        a.ABName, 
        t.TranSID, 
        SUM(IIF(TranTypeID = 'CO', td.Qty * CAST(td.Price AS money) * - 1, td.Qty * CAST(td.Price AS money))) AS TotalSales
    FROM Trans t 
       INNER JOIN TransDetail td 
           ON t.TranID = td.TranID 
       INNER JOIN ABook a 
           ON t.TranABID = a.ABID
    WHERE (t.TranDate BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-01-01 00:00:00', 102)) 
       AND t.TranTypeID in ('SO','CA','CO')
       AND (t.TranStatus <> 'V')
    GROUP BY a.ABID, a.ABName, t.TranSID
    HAVING  (NOT (a.ABName LIKE '%cash%'))
) a
WHERE a.RowNum <=10

这将为分组中的每个记录分配一个行号(由 PARTITION 定义的列，从 1 到 n.从那里，您可以运行 SELECT 在它上面抓取每组任意数量的记录.

This will assign a row number to each record in the grouping (the column defined by the PARTITION, going from 1 to n. From there, you can run a SELECT on it to grab any number of records per group.

t-SQL 查找每个组的前 10 条记录

相关推荐