mysql GROUP BY删除查询重复记录

作者：袖梨 2022-06-29

我们先来看查询重复记录

 代码如下 复制代码

SELECT *  
FROM (  
SELECT *  
FROM dede_archives  
ORDER BY id DESC  
)arc, dede_soft b  
WHERE arc.id = b.aid  
GROUP BY `shorttitle`  
ORDER BY `id` DESC  
LIMIT 0 , 30  

删除的话也很简单的了

 代码如下 复制代码

delete from people where peopleid in (select peopleid from people group by peopleid having count

(peopleid) > 1) and rowid not in (select min(rowid) from people group by peopleid having count

(peopleid )>1)


例

id name
1 a
2 b
3 c
4 c
5 b

完整语句放出:

 代码如下 复制代码

select *, count(distinct name) from table group by name

结果:

id name count(distinct name)
1 a 1
2 b 1
3 c 1

原来还有更简单的办法啊。

相关文章

精彩推荐