我有下表及其示例数据,
+------------------+-------+--------+---------------------+-------+
| 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 |
+------------------+-------+--------+---------------------+-------+
最佳答案
你可以在没有 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
https://stackoverflow.com/questions/71754206/