当前位置: 澳门新濠3559 > 数据库 > 正文

title)字段来判断,//显示重复的所有条

时间:2019-11-08 05:44来源:数据库
//显示重复的所有条 mysql语句删除重复数据,保留一条;查询所有重复数据;查询重复数据的一条,,mysql语句 //显示重复的所有条 SELECT * FROM 表名 WHERE (字段1,字段2,...) IN (SELECT 字段1

//显示重复的所有条

mysql语句删除重复数据,保留一条;查询所有重复数据;查询重复数据的一条,,mysql语句

//显示重复的所有条

  SELECT * FROM 表名
  WHERE (字段1,字段2,...)
  IN (SELECT 字段1,字段2,...FROM 表名 GROUP BY 字段1,字段2,... HAVING COUNT(*)>1)

 

//只显示重复中的一条

  SELECT * FROM 表名 GROUP BY CONCAT(字段1,字段2,...) HAVING COUNT(*)>1

 

//删除重复数据(保留ID最小的一条)
  DELETE FROM 表名
  WHERE (字段1,字段2,...)
  IN (SELECT 字段1,字段2,... FROM
    (SELECT 字段1,字段2,... FROM 表名 GROUP BY 字段1,字段2,... HAVING COUNT(*)>1) AS b

    )
  AND Id NOT IN (SELECT id AS id FROM
    (SELECT MIN(Id) AS id FROM 表名 GROUP BY 字段1,字段2,... HAVING COUNT(*)>1) AS c

    )

注意:在删除重复数据语句中加深的部分,from后面需要重新套一层查询语句,负责报错。

//显示重复的所有条 SELECT * FROM 表名 WHERE (字段...

(一)单个字段

  SELECT * FROM 表名
  WHERE (字段1,字段2,...)
  IN (SELECT 字段1,字段2,...FROM 表名 GROUP BY 字段1,字段2,... HAVING COUNT(*)>1)

1、查找表中多余的重复记录,根据(question_title)字段来判断

 

 代码如下 复制代码
select * from questions where question_title in (select question_title from people group by question_title having count(question_title) > 1)  

//只显示重复中的一条

2、删除表中多余的重复记录,根据(question_title)字段来判断,只留有一个记录

  SELECT * FROM 表名 GROUP BY CONCAT(字段1,字段2,...) HAVING COUNT(*)>1

 代码如下 复制代码
delete from questions
where peopleId in (select peopleId from people group by peopleId having count(question_title) > 1)
and min(id) not in (select question_id from questions group by question_title having count(question_title)>1)

 

(二)多个字段

//删除重复数据(保留ID最小的一条)
  DELETE FROM 表名
  WHERE (字段1,字段2,...)
  IN (SELECT 字段1,字段2,... FROM
    (SELECT 字段1,字段2,... FROM 表名 GROUP BY 字段1,字段2,... HAVING COUNT(*)>1) AS b

删除表中多余的重复记录(多个字段),只留有rowid最小的记录

    )
  AND Id NOT IN (SELECT id AS id FROM
    (SELECT MIN(Id) AS id FROM 表名 GROUP BY 字段1,字段2,... HAVING COUNT(*)>1) AS c

 代码如下 复制代码
DELETE FROM questions WHERE (questions_title,questions_scope) IN (SELECT questions_title,questions_scope FROM questions GROUP BY questions_title,questions_scope HAVING COUNT(*) > 1) AND question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY questions_scope,questions_title HAVING COUNT(*)>1)  

    )

用上述语句无法删除,创建了临时表才删的,求各位达人解释一下。

注意:在删除重复数据语句中加深的部分,from后面需要重新套一层查询语句,负责报错。

 代码如下 复制代码
CREATE TABLE tmp AS SELECT question_id FROM questions WHERE (questions_title,questions_scope) IN (SELECT questions_title,questions_scope FROM questions GROUP BY questions_title)字段来判断,//显示重复的所有条。title,questions_scope HAVING COUNT(*) > 1) AND question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY questions_scope,questions_title HAVING COUNT(*)>1);

DELETE FROM questions WHERE question_id IN (SELECT question_id FROM tmp);

DROP TABLE tmp;  

(三) 存储过程

 代码如下 复制代码
declare @max integer,@id integer

declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

delete from 表名 where 主字段 = @id

fetch cur_rows into @id,@max

end

close cur_rows

set rowcount 0  

例,

数据库版本 Server version: 5.1.41-community-log MySQL Community Server (GPL)

例1,表中有主键(可唯一标识的字段),且该字段为数字类型

例1测试数据

 代码如下 复制代码
/* 表结构 */
DROP TABLE IF EXISTS `t1`;
CREATE TABLE IF NOT EXISTS `t1`(
  `id` INT(1) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `add` VARCHAR(20) NOT NULL,
  PRIMARY KEY(`id`)
)Engine=InnoDB;

/* 插入测试数据 */
INSERT INTO `t1`(`name`,`add`) VALUES
('abc',"123"),
('abc',"123"),
('abc',"321"),
('abc',"123"),
('xzy',"123"),
('xzy',"456"),
('xzy',"456"),
澳门新濠3559,('xzy',"456"),
('xzy',"789"),
('xzy',"987"),
('xzy',"789"),
('ijk',"147"),
('ijk',"147"),
('ijk',"852"),
('opq',"852"),
('opq',"963"),
('opq',"741"),
('tpk',"741"),
('tpk',"963"),
('tpk',"963"),
('wer',"546"),
('wer',"546"),
('once',"546");

SELECT * FROM `t1`;
+----+------+-----+
| id | name | add |
+----+------+-----+
|  1 | abc  | 123 |
|  2 | abc  | 123 |
|  3 | abc  | 321 |
|  4 | abc  | 123 |
|  5 | xzy  | 123 |
|  6 | xzy  | 456 |
|  7 | xzy  | 456 |
|  8 | xzy  | 456 |
|  9 | xzy  | 789 |
| 10 | xzy  | 987 |
| 11 | xzy  | 789 |
| 12 | ijk  | 147 |
| 13 | ijk  | 147 |
| 14 | ijk  | 852 |
| 15 | opq  | 852 |
| 16 | opq  | 963 |
| 17 | opq  | 741 |
| 18 | tpk  | 741 |
| 19 | tpk  | 963 |
| 20 | tpk  | 963 |
| 21 | wer  | 546 |
| 22 | wer  | 546 |
| 23 | once | 546 |
+----+------+-----+
rows in set (0.00 sec)  

查找id最小的重复数据(只查找id字段)

 代码如下 复制代码
/* 查找id最小的重复数据(只查找id字段) */
SELECT DISTINCT MIN(`id`) AS `id`
FROM `t1`
GROUP BY `name`,`add`
HAVING COUNT(1) > 1;
+------+
| id   |
+------+
|    1 |
|   12 |
|   19 |
|   21 |
|    6 |
|    9 |
+------+
rows in set (0.00 sec)  

查找所有重复数据

 代码如下 复制代码
/* 查找所有重复数据 */
SELECT `t1`.*
FROM `t1`,(
  SELECT `name`,`add`
  FROM `t1`
  GROUP BY `name`,`add`
  HAVING COUNT(1) > 1
) AS `t2`
WHERE `t1`.`name` = `t2`.`name`
  AND `t1`.`add` = `t2`.`add`;
+----+------+-----+  

| id | name | add |
+----+------+-----+
|  1 | abc  | 123 |
|  2 | abc  | 123 |
|  4 | abc  | www.111cn.net|
|  6 | xzy  | 456 |
|  7 | xzy  | 456 |
|  8 | xzy  | 456 |
|  9 | xzy  | 789 |
| 11 | xzy  | 789 |
| 12 | ijk  | 147 |
| 13 | ijk  | 147 |
| 19 | tpk  | 963 |
| 20 | tpk  | 963 |
| 21 | wer  | 546 |
| 22 | wer  | 546 |
+----+------+-----+
rows in set (0.00 sec)

更多详细内容请查看:

编辑:数据库 本文来源:title)字段来判断,//显示重复的所有条

关键词: