杰奇cms删除筛选数据库中的重复数值
说明:所谓的重复数据,都是基于就一个数据表中某个字段中“值”的重复。
下面以杰奇cms为例,筛选或者删除重复书籍
PS:如果要把筛选结果输出到文档则可在命令后面机上
INTO OUTFILE 'c:/txt1.txt';
1、查找表中多余的重复记录,重复记录是根据单个字段(articlename)来判断
select * from jieqi_article_article where articlename in (select articlename from jieqi_article_article group by articlename having count(articlename) > 1);
2、删除表中多余的重复记录,重复记录是根据单个字段(articlename)来判断,只留有一个记录
delete from jieqi_article_article where articlename in (select articlename from jieqi_article_article group by articlename having count(articlename) > 1 and min(id) not in (select id from jieqi_article_article group by articlename having count(articlename)>1);
3、查找表中多余的重复记录(多个字段)【结果是:输出的数据包含了此表中的所有字段数据】
select * from jieqi_article_article where (articlename,author) in (select articlename,author from jieqi_article_article group by articlename,author having count(*) > 1);
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.articlename,a.seq) in (select articlename,seq from vitae group by articlename,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by articlename,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.articlename,a.seq) in (select articlename,seq from vitae group by articlename,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by articlename,seq having count(*)>1)
下面2个示例注意对比
6.筛选出某个属性中唯一的值【结果是:按照articlename,author字段输出这两个字段唯一书籍,排除掉重复了,结果中而不包含其他字段信息】
SELECT DISTINCT articlename,author FROM jieqi_article_article; //按照书籍名和作者筛选出唯一的书籍,已经过滤掉重复
7.把重复的筛选出来【结果是重复的实际】
select articlename,author,count(*) as count from jieqi_article_article group by articlename,author having count>1 into outfile "c:\3.txt"; Query OK, 3486 rows affected
发表评论