如何使用groupby檢索不同的資料

我有下表及其樣本資料如下，

 ------------------ ------- -------- --------------------- ------- 
| PhoneCode        | SeqID | Active | Token               | CUSID |
 ------------------ ------- -------- --------------------- ------- 
| e29a5e1c695352b8 | 3898  | I      | JYN8CYRVzaKWL-l_K   | 50002 |
 ------------------ ------- -------- --------------------- ------- 
| e29a5e1c695352b8 | 3900  | I      | JYN8CYRVzaKWL-l_K   | 50002 |
 ------------------ ------- -------- --------------------- ------- 
| 741fb28bc72183e3 | 3899  | I      | RU3ReKEw0yin9LxZWCO | 50002 |
 ------------------ ------- -------- --------------------- ------- 
| 741fb28bc72183e4 | 3901  | A      | RU3ReKEw0yin9LxZWCO | 50002 |
 ------------------ ------- -------- --------------------- ------- 

我需要PhoneCode與 latest區別開來SeqID。所以我嘗試了以下查詢。

SELECT UD.PHONECODE, UD.SeqID, UD.ACTIVE, UD.Token
FROM   DEVICE UD
WHERE UD.CUSID = '50002' AND UD.ACTIVE = 'I'
GROUP BY PHONECODE

但它會出錯。如何檢索以下輸出？

 ------------------ ------- -------- --------------------- ------- 
| PhoneCode        | SeqID | Active | Token               | CUSID |
 ------------------ ------- -------- --------------------- ------- 
| e29a5e1c695352b8 | 3900  | I      | JYN8CYRVzaKWL-l_K   | 50002 |
 ------------------ ------- -------- --------------------- ------- 
| 741fb28bc72183e3 | 3899  | I      | RU3ReKEw0yin9LxZWCO | 50002 |
 ------------------ ------- -------- --------------------- ------- 

uj5u.com熱心網友回復：

您可以在沒有GROUP BY和沒有自連接的情況下執行此操作：

SELECT PHONECODE,
       SeqID,
       ACTIVE,
       Token
FROM   (
  SELECT d.*,
         ROW_NUMBER() OVER (PARTITION BY phonecode ORDER BY seqid DESC) AS rn
  FROM   device d
  WHERE  cusid = 50002
  AND    active = 'I'
)
WHERE  rn = 1;

或者，如果你想使用，GROUP BY那么你也可以使用KEEP：

SELECT PHONECODE,
       MAX(SeqID) AS seqid,
       MAX(ACTIVE) KEEP (DENSE_RANK LAST ORDER BY SeqID) AS active,
       MAX(Token) KEEP (DENSE_RANK LAST ORDER BY SeqID) AS token
FROM   device
WHERE  cusid = 50002
AND    active = 'I'
GROUP BY phonecode;

其中，對于樣本資料：

CREATE TABLE device (PhoneCode, SeqID, Active, Token, CUSID ) AS
SELECT 'e29a5e1c695352b8', 3898, 'I', 'JYN8CYRVzaKWL-l_K',   50002 FROM DUAL UNION ALL
SELECT 'e29a5e1c695352b8', 3900, 'I', 'JYN8CYRVzaKWL-l_K',   50002 FROM DUAL UNION ALL
SELECT '741fb28bc72183e3', 3899, 'I', 'RU3ReKEw0yin9LxZWCO', 50002 FROM DUAL UNION ALL
SELECT '741fb28bc72183e4', 3901, 'A', 'RU3ReKEw0yin9LxZWCO', 50002 FROM DUAL;

兩個輸出：

電話號碼	序列號	積極的	代幣
741fb28bc72183e3	3899	一世	RU3ReKEw0yin9LxZWCO
e29a5e1c695352b8	3900	一世	JYN8CYRVzaKWL-l_K

db<>在這里擺弄

uj5u.com熱心網友回復：

一個簡單的子查詢怎么樣

SELECT UD.PHONECODE, UD.SeqID, UD.ACTIVE, UD.Token 
FROM device ud
WHERE (UD.PHONECODE, UD.SeqID) IN (
  SELECT PHONECODE, MAX(SeqID) 
  FROM device
  WHERE CUSID = '50002' AND ACTIVE = 'I'
  GROUP BY PHONECODE 
)

uj5u.com熱心網友回復：

您可以使用MAX()并加入：

SELECT UD.PHONECODE, UD.ACTIVE, UD.Token, UD.SeqID
FROM   DEVICE UD
INNER JOIN (
    SELECT PHONECODE, MAX(SeqID) as SEQID
    FROM DEVICE   
    WHERE CUSID = '50002' AND ACTIVE = 'I'
    GROUP BY PHONECODE
 ) T2 ON UD.PHONECODE = T2.PHONECODE AND UD.SEQID = T2.SEQID     

uj5u.com熱心網友回復：

以下查詢將起作用：

select phonecode,seqid,active,token,cusid
from
(
    select * , row_number() over(partition by phonecode order by seqid desc) as rownum
    from device
    where cusid = 50002 and active = 'I'
) as t1
where rownum = 1

標籤：sql 甲骨文 每组最大 n

上一篇：我可以使用ActivityResultContracts.GetContent()中的URI多長時間？

下一篇：如何從Oracle的SQL中截取資料