mysql 结果集去重_mysql去重, 把url重复且区为空的中去掉、统计重复数据、、结果集去重合并成一行...

delete from 表名 where id not in (select d.id from (SELECT id FROM 表名 GROUP BY c1,c2,c3,c4)as d)

#去重复，把url重复，且区为空的中去掉。

select * from TABLE where

url in

(select u.url from (select * from TABLE where id not in (select d.id from (SELECT id FROM TABLE GROUP BY url)as d)) as u)

and qu like "";

二、删除/去除重复记录

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

SELECT shortname,COUNT(*) FROM student GROUP BY shortname HAVING COUNT(*) > 1;

select user_name,count(*) as count from user group by user_name having count>1;

创建临时表：

create table tb2 select c1,c2 from tb1 group by c1,c2;

drop table tb1;

alter table tb2 rename to tb1;

三、统计重复数据：

select c1,c2

from tb1

group by c1,c2

having count(*)>1;

除了GROUP BY 来读取数据表中不重复的数据，使用DISTINCT关键字来过滤。

用distinct:

select distinct c1,c2 from table;

四、防止表中出现重复数据：

在MySQL数据表中设置指定的字段为PRIMARY KEY索引/UNIQUE索引)来保证数据的唯一性。

create table tb1 (

c1 char(16) not null,

c2 char(16) not null,

c3 char(10),

primary key(c1,c2)

)

或者：

create table tb1

(

c1 char(20) not null,

c2 char(20) not null,

c3 char(10),

unique(c1,c2)

)

mysql查询表里的重复数据方法

更新时间&#x