SQL关键语句

-- 查询重复name的数量，按重复数量倒序排列
select * from (SELECT name,count(1) as count from nexperia_cn group by name) AS A order by count DESC

-- 查询待删除的重复name的所有数据
select *
FROM
    test 
WHERE
    ID IN ( SELECT ID FROM test WHERE NAME IN ( SELECT NAME FROM test GROUP BY NAME HAVING count( NAME ) > 1 ) ) 
    AND ID NOT IN (
    SELECT
        B.minid 
    FROM
    ( SELECT min( ID ) AS minid FROM test GROUP BY NAME HAVING count( NAME ) > 1 ) B 
    )

 

-- 删除name字段重复的记录，只保留ID最小的name记录。
DELETE 
FROM
    test 
WHERE
    ID IN ( SELECT ID FROM test WHERE NAME IN ( SELECT NAME FROM test GROUP BY NAME HAVING count( NAME ) > 1 ) ) 
    AND ID NOT IN (
    SELECT
        B.minid 
    FROM
        ( SELECT min( ID ) AS minid FROM test GROUP BY NAME HAVING count( NAME ) > 1 ) B 
    ) 
    
-- 功能同上
-- 删除name字段重复的记录，只保留ID最小的name记录。(在mysql里面可用，）
-- 如果用上面的语句删除，在mysql里面会报错You can't specify target table 'test' for update in FROM clause，改成如下就可以
DELETE 
FROM
    test 
WHERE
    id IN (
    SELECT
        id 
    FROM
        (
        SELECT
            id 
        FROM
            test 
        WHERE
            ID IN ( SELECT ID FROM test WHERE NAME IN ( SELECT NAME FROM test GROUP BY NAME HAVING count( NAME ) > 1 ) ) 
            AND ID NOT IN (
            SELECT
                B.minid 
            FROM
                ( SELECT min( ID ) AS minid FROM test GROUP BY NAME HAVING count( NAME ) > 1 ) B 
            ) 
        ) AS C 
    )