如何从使用group by命令检索的结果中获取每个组的第一条记录

Suppose if my Input is:

假设我的输入是：

ID  GroupID  Qty
1         1  100
2         1  200
3         1  300
4         2  98
5         2  198
6         3  175
7         3  275
8         3  375
9         4  215

Output should be

输出应该是

ID   GroupID    Qty
 1         1    100
 4         2    98
 6         3    175
 9         4    215

Can any one help me how to do it with SQL Server T-SQL query?

任何人都可以帮我如何使用SQL Server T-SQL查询吗？

3 个解决方案

#1

declare @T table (ID int, GroupID int, Qty int)
insert into @T values
(1, 1, 100),
(2, 1, 200),
(3, 1, 300),
(4, 2, 98),
(5, 2, 198),
(6, 3, 175),
(7, 3, 275),
(8, 3, 375),
(9, 4, 215)

;with cte as
(
  select
    ID,
    GroupID,
    Qty,
    rank() over(partition by GroupID order by ID) as rn
  from @T
)  
select ID, GroupID, Qty
from cte
where rn = 1

#2

EDIT

编辑

SELECT 
    MIN(ID) ,
    GroupID,
    (SELECT TOP 1 Qty FROM @TABLE T2 WHERE T2.ID = MIN(T1.ID))
FROM 
    @TABLE T1
GROUP BY
    GroupID

Input

输入

 ID GroupID   Qty
    1   1   100
    2   1   200
    3   1   300
    4   2   98
    5   2   198
    6   3   175
    7   3   275
    8   3   375
    9   4   215

Output

产量

1   1   100
4   2   98
6   3   175
9   4   215

#3

The best and more flexible way in my opinion would be to use ROW_NUMBER(). The below I've tested for your example, just replace tmpTable with your table name:

在我看来，最好和更灵活的方法是使用ROW_NUMBER（）。下面我测试了你的例子，只需用你的表名替换tmpTable：

SELECT a.* FROM tmpTable a INNER JOIN 
(SELECT    ROW_NUMBER() over(PARTITION BY GroupID ORDER BY ID, GroupID) AS SEQ, tmpTable.*
FROM            tmpTable) b
ON a.ID = b.ID AND a.GroupID = b.GroupID
WHERE b.SEQ = 1

read more about how to use ROW_NUMBER: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

阅读有关如何使用ROW_NUMBER的更多信息：https：//docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

#1

declare @T table (ID int, GroupID int, Qty int)
insert into @T values
(1, 1, 100),
(2, 1, 200),
(3, 1, 300),
(4, 2, 98),
(5, 2, 198),
(6, 3, 175),
(7, 3, 275),
(8, 3, 375),
(9, 4, 215)

;with cte as
(
  select
    ID,
    GroupID,
    Qty,
    rank() over(partition by GroupID order by ID) as rn
  from @T
)  
select ID, GroupID, Qty
from cte
where rn = 1

#2