筆記 T-SQL

取每一群組的第一筆

T-SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE @table TABLE (ID nvarchar(10), Memo nvarchar(10))
INSERT INTO @table (ID, Memo) VALUES ('AA', 'A7')
INSERT INTO @table (ID, Memo) VALUES ('AA', 'A2')
INSERT INTO @table (ID, Memo) VALUES ('AA', 'A6')
INSERT INTO @table (ID, Memo) VALUES ('BB', 'B6')
INSERT INTO @table (ID, Memo) VALUES ('BB', 'B5')
INSERT INTO @table (ID, Memo) VALUES ('CC', 'C9')
INSERT INTO @table (ID, Memo) VALUES ('CC', 'C3')
SELECT ID, 
(
    SELECT TOP 1 Memo 
    FROM  @table AS T2
    WHERE T2.ID = T1.ID
    GROUP BY Memo
) AS [Memo]
FROM @table AS T1
GROUP BY ID

結果

T-SQL

參考