iT邦幫忙

0

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

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

問題的情境是帶入一次序號之後,取得相同的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')

預顯示出的結果 (紅字是目前缺少,且需要的)
https://ithelp.ithome.com.tw/upload/images/20201213/20125052N2kXnfILoB.png

目前組出來的

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.缺少排名的最大值是多少

感謝大大幫忙!!

看更多先前的討論...收起先前的討論...
有考慮過用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';
原來count可以結合over功能,原本是一直卡在要設Group By的問題~ 感恩
over本身就有group by的特性, 可以找相關的文件研究看看:)
感謝!!

1 個回答

1
rogeryao
iT邦大師 5 級 ‧ 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

謝謝!!

我要發表回答

立即登入回答