sql - 如何使用 group by 检索不同的数据

Question

我有下表及其示例数据，

+------------------+-------+--------+---------------------+-------+
| 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 |
+------------------+-------+--------+---------------------+-------+

我需要使用最新的 SeqID 来区分 PhoneCode。所以我尝试了以下查询。

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 |
+------------------+-------+--------+---------------------+-------+

Answer 1

你可以在没有 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;

两个输出:

PHONECODE	SEQID	ACTIVE	TOKEN
741fb28bc72183e3	3899	I	RU3ReKEw0yin9LxZWCO
e29a5e1c695352b8	3900	I	JYN8CYRVzaKWL-l_K

db<> fiddle here