窗口函數(window function) over 與 partition 的使用

Posted on Edited on In Views: Disqus:

 

準備資料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Drop TABLE #testTable

CREATE TABLE #testTable
(
    [id] int,
    [name] varchar(10),
	[age] int,
);

INSERT INTO #testTable ([id],[name],[age]) values (1,'Bill',40);
INSERT INTO #testTable ([id],[name],[age]) values (2,'Tom',10);
INSERT INTO #testTable ([id],[name],[age]) values (3,'Mary',60);
INSERT INTO #testTable ([id],[name],[age]) values (4,'Joe',10);
INSERT INTO #testTable ([id],[name],[age]) values (5,'Tim',60);
INSERT INTO #testTable ([id],[name],[age]) values (6,'Alice',5);

原資料內容如下

1
SELECT * FROM #testtable order by id;
id name age
1 Bill 40
2 Tom 10
3 Mary 60
4 Joe 10
5 Tim 60
6 Alice 5


除了列出原本資料外，想每筆資料再多附加一行，列出該同年齡的人數

1
select *, count(*) over (partition by age) cnt from #testTable order by id
id name age cnt
1 Bill 40 1
2 Tom 10 2
3 Mary 60 2
4 Joe 10 2
5 Tim 60 2
6 Alice 5 1