MSSQL 除了 ORDER BY 排序外,我們還可以用 ROW_NUMBER() ,配合 PARTITION BY 做分群組排序。ROW_NUMBER() 會將查詢出來的每一列資料加上一個序號(從1開始累加),且序號不會重複。接下來就為大家介紹用法。
ROW_NUMBER() -分群組排序
首先我們先建一個資料表( CREATE TABLE ) 如下:
CREATE TABLE dbo.Cars(stype VARCHAR(15), Price MONEY, Color VARCHAR(10), YearM date);
INSERT dbo.Cars VALUES
('轎車', 20000, 'red', '2010-01-01'), ('敞篷車', 35000, 'blue', '2000-12-01'),
('轎跑車', 25000, 'red', '2020-01-01'), ('麵包車', 16000, 'blue', '2015-01-01');
接下來我們依顏色及價錢來做群組排序,我們可以看到加了 PARTITION BY 後,產生的ROW_ID欄位,會依照組別來產生流水號。
語法:
select ROW_NUMBER() OVER (ORDER BY Color ASC,Price desc) as ROW_ID ,* from Cars
select ROW_NUMBER() OVER (PARTITION BY Color order by Price desc) as ROW_ID ,* from Cars
實作:
如果我們只要在每種顏色中列出價錢最高的一輛車,要怎們下語法呢 ?
語法:
select t1.*
from (select ROW_NUMBER() OVER (PARTITION BY Color order by Price desc) as ROW_ID ,* from Cars) as t1
where t1.ROW_ID = 1
實作: