新年快乐。
我不确定如何做SQL,我需要保留最高级别的clientID的记录,同时删除其他记录?
我的表格如下所示

并希望获得以下内容

基本上,它删除了重复的clientID行,但保留了该clientID记录的最高级别。
感谢您的指导。
发布于 2021-01-11 15:50:43
此查询可以在大多数版本的SQL中使用(不包括MySQL < 8.0或SQL Server);它使用ROW_NUMBER()对Level值按ClientID排序,并删除除该ClientID值具有最大Level的行以外的所有行:
WITH CTE AS (
SELECT ClientID, Level,
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY Level DESC) AS rn
FROM data
)
DELETE FROM data
WHERE (ClientId, Level) IN (
SELECT ClientId, Level
FROM CTE
WHERE rn > 1
)在SQL Server中,您只需从CTE中删除:
WITH CTE AS (
SELECT ClientID, Level,
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY Level DESC) AS rn
FROM data
)
DELETE FROM CTE
WHERE rn > 1在MySQL < 8.0中,您可以使用以下查询:
DELETE FROM data
WHERE (ClientID, Level) NOT IN (
SELECT ClientID, MAX(Level)
FROM (SELECT * FROM data) d
GROUP BY ClientID
)发布于 2021-01-11 15:54:28
您可以按如下方式使用exists:
Delete from your_table t
Where exists
(Select 1 from your_table tt
Where tt.client_id = t.client_id and tt.level > t.level)发布于 2021-01-11 16:01:16
在Mysql中,您可以简单地访问所需的输出,而无需删除sql中的数据
SELECT ClientID,level FROM (SELECT* , ROW_NUMBER() OVER(PARTITION BY ClientID
ORDER BY level DESC ) AS RN FROM tbl_client)A
WHERE RN=1以下是所需的简单输出

https://stackoverflow.com/questions/65662669
复制相似问题