SQL - RANK() OVER 與 MAX(RANK) 問題 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

SQL - RANK() OVER 與 MAX(RANK) 問題

sql mysql ms sql sqlserver oracle sql

dork300916 2020-12-13 11:28:25 ‧ 3511 瀏覽

分享至

以下問題想詢問大大解法，感恩

問題的情境是帶入一次序號之後，取得相同的ProductPlanID、WorkStationID的排名

然後顯示出帶入序號的資訊、排名跟排名的最大值

資料樣本

INSERT INTO [dbo].[ProductStatus]
           ([SerialNumber]
           ,[ProductPlanID]
           ,[WorkStationID]
           ,[CompleteTime])
     VALUES
           ('AS500-100001','AB200-2500001','CNC-25101','2020-12-12 08:09:35.557'),
		   ('AS500-100002','AB200-2500001','CNC-25101','2020-12-12 08:10:35.557'),
		   ('AS500-100003','AB200-2500001','CNC-25101','2020-12-12 08:11:35.557'),
		   ('AS500-100004','AB200-2500001','CNC-25101','2020-12-12 08:25:35.557'),
		   ('AS500-100005','AB200-2500001','CNC-25101','2020-12-12 08:34:35.557'),
		   ('AS500-100006','AB200-2500001','CNC-25101','2020-12-12 08:35:35.557'),
		   ('AS500-100007','AB200-2500001','CNC-25101','2020-12-12 08:36:35.557'),
		   ('AS500-100008','AB200-2500001','CNC-25101','2020-12-12 08:40:35.557'),
		   ('AS500-100009','AB200-2500001','CNC-25101','2020-12-12 09:09:35.557'),
		   ('AS500-100010','AB200-2500001','CNC-25101','2020-12-12 10:09:35.557'),
		   ('AS500-100011','AB200-2500001','CNC-25101','2020-12-12 11:09:35.557'),
		   ('AS500-100012','AB200-2500002','CNC-25101','2020-12-12 12:09:35.557'),
		   ('AS500-100013','AB200-2500002','CNC-25101','2020-12-12 12:10:35.557'),
		   ('AS500-100014','AB200-2500002','CNC-25101','2020-12-12 12:11:35.557'),
		   ('AS500-100015','AB200-2500002','CNC-25101','2020-12-12 12:12:35.557'),
		   ('AS500-100016','AB200-2500001','CNC-35101','2020-12-12 12:13:35.557'),
		   ('AS500-100017','AB200-2500001','CNC-35101','2020-12-12 12:14:35.557'),
		   ('AS500-100018','AB200-2500001','CNC-35101','2020-12-12 12:15:35.557'),
		   ('AS500-100019','AB200-2500001','CNC-35101','2020-12-12 12:16:35.557'),
		   ('AS500-100020','AB200-2500001','CNC-35101','2020-12-12 12:17:35.557')

預顯示出的結果 (紅字是目前缺少，且需要的)

目前組出來的

select s1.*,s2.rank
from ProductStatus s1
 join (select s.* , RANK() OVER(order by s.SerialNumber) as rank from ProductStatus s) s2 on  s1.ProductPlanID = s2.ProductPlanID and s1.WorkStationID = s2.WorkStationID and s2.SerialNumber = 'AS500-100007'
where s1.SerialNumber = 'AS500-100007'

但是有以下幾點需要修正
1.SerialNumber的值只能帶入一次，上面的搜尋語法已經帶入兩次了
2.缺少排名的最大值是多少

感謝大大幫忙!!

看更多先前的討論...收起先前的討論...

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2020-12-13 11:55:18 檢舉

有考慮過用count嗎, 如果資料不重覆的話, 總筆數應該跟排名相同? 想法上應該會簡單些, 但會重覆就不work了XD, 像這樣
with raw as (
select s.*, rank() OVER(partition by ProductPlanID, WorkStationID order by s.SerialNumber) rnk
,count(SerialNumber) over(partition by ProductPlanID, WorkStationID) cnt from ProductStatus s)
select * from raw r
where r.SerialNumber = 'AS500-100007';

dork300916 iT邦新手 5 級 ‧ 2020-12-13 14:03:47 檢舉

原來count可以結合over功能，原本是一直卡在要設Group By的問題~ 感恩

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2020-12-13 14:13:47 檢舉

over本身就有group by的特性, 可以找相關的文件研究看看:)

dork300916 iT邦新手 5 級 ‧ 2020-12-13 22:32:49 檢舉

感謝!!

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 7 級 ‧ 2020-12-13 14:12:48

最佳解答

CREATE TABLE ProductStatus(
SerialNumber varchar(60), 
ProductPlanID varchar(60), 
WorkStationID varchar(60), 
CompleteTime varchar(60));

INSERT INTO ProductStatus
           (SerialNumber
           ,ProductPlanID
           ,WorkStationID
           ,CompleteTime)
VALUES
('AS500-100001','AB200-2500001','CNC-25101','2020-12-12 08:09:35.557'),
('AS500-100002','AB200-2500001','CNC-25101','2020-12-12 08:10:35.557'),
('AS500-100003','AB200-2500001','CNC-25101','2020-12-12 08:11:35.557'),
('AS500-100004','AB200-2500001','CNC-25101','2020-12-12 08:25:35.557'),
('AS500-100005','AB200-2500001','CNC-25101','2020-12-12 08:34:35.557'),
('AS500-100006','AB200-2500001','CNC-25101','2020-12-12 08:35:35.557'),
('AS500-100007','AB200-2500001','CNC-25101','2020-12-12 08:36:35.557'),
('AS500-100008','AB200-2500001','CNC-25101','2020-12-12 08:40:35.557'),
('AS500-100009','AB200-2500001','CNC-25101','2020-12-12 09:09:35.557'),
('AS500-100010','AB200-2500001','CNC-25101','2020-12-12 10:09:35.557'),
('AS500-100011','AB200-2500001','CNC-25101','2020-12-12 11:09:35.557'),
('AS500-100012','AB200-2500002','CNC-25101','2020-12-12 12:09:35.557'),
('AS500-100013','AB200-2500002','CNC-25101','2020-12-12 12:10:35.557'),
('AS500-100014','AB200-2500002','CNC-25101','2020-12-12 12:11:35.557'),
('AS500-100015','AB200-2500002','CNC-25101','2020-12-12 12:12:35.557'),
('AS500-100016','AB200-2500001','CNC-35101','2020-12-12 12:13:35.557'),
('AS500-100017','AB200-2500001','CNC-35101','2020-12-12 12:14:35.557'),
('AS500-100018','AB200-2500001','CNC-35101','2020-12-12 12:15:35.557'),
('AS500-100019','AB200-2500001','CNC-35101','2020-12-12 12:16:35.557'),
('AS500-100020','AB200-2500001','CNC-35101','2020-12-12 12:17:35.557');

SELECT *
FROM (
SELECT *,
RANK() OVER(PARTITION BY ProductPlanID,WorkStationID ORDER BY SerialNumber) AS RankNum,
COUNT(1) OVER (PARTITION BY ProductPlanID,WorkStationID) AS RankMax
FROM ProductStatus
) AS TEMP
WHERE SerialNumber='AS500-100007'

Demo