得到所有重复记录select * from tables where fields in( select fields from tables group by fields having sum(1)> 1)
select * into #tem from tables where fields in( select fields from tables group by fields having sum(1)> 1) go delete from tables go select * into tables from #tem
delete * from tables where fields not in( select fields from tables where 条件保留记录的条件 )
--实现如下:CREATE TABLE T1(column1 INT,column2 INT,column3 INT)GOINSERT INTO T1 SELECT 1,1,1 UNION ALL SELECT 1,1,1 UNION ALL SELECT 1,2,3 UNION ALL SELECT 1,1,1 UNION ALL SELECT 1,2,3 UNION ALL SELECT 7,8,9GO--T1表中现有数据SELECT * FROM T1column1 column2 column3 ----------- ----------- ----------- 1 1 1 1 1 1 1 2 3 1 1 1 1 2 3 7 8 9(所影响的行数为 6 行)--所要求实现的查询语句:BEGIN IF (select count(*)from t1)<>(select count(*)from (select column1,column2,column3 from t1 group by column1,column2,column3 )as 不重复的行数) BEGIN SELECT DISTINCT'该表中有重复行'FROM T1 END ELSE BEGIN SELECT DISTINCT'该表中没有重复行'FROM T1 END END --另外几个有用的辅助语句:--查询原表的行数: SELECT COUNT(*)FROM T1----------- 6(所影响的行数为 1 行)--查询原表中不重复的行数: SELECT COUNT(*)FROM (SELECT column1,column2,column3 FROM T1 GROUP BY column1,column2,column3)AS 不重复的行数
----------- 3(所影响的行数为 1 行)--查询原表中不重复的数据: SELECT * FROM (SELECT column1,column2,column3 FROM T1 GROUP BY column1,column2,column3)AS 不重复的记录column1 column2 column3 ----------- ----------- ----------- 1 1 1 1 2 3 7 8 9(所影响的行数为 3 行) --查询原表中重复的数据 SELECT * FROM T1 GROUP BY column1,column2,column3 HAVING SUM(1)>1column1 column2 column3 ----------- ----------- ----------- 1 1 1 1 2 3(所影响的行数为 2 行)--删除测试环境DROP TABLE T1
Select fields from tables group by fields having count(*) > 1
select identity(int,1,1) Ice,* into iceriver from tables godelete form tables godelete from iceriver where ice not in (select min(ice) from iceriver group by fields) goalter table iceriver drop ice go --如果原表有自增长字段,此处先设置自增长字段允许插入 insert into tables select * from iceriver go --如果原表有自增长字段,此处设置自增长字段不允许插入drop table iceriver go --这样原表中的数据就是不重复的,此处的条件只是fields不重复!
重复的记录 Select fields from tables group by fields having count(*) > 1重复的所有记录. select * from tables where fields in( select fields from tables group by fields having sum(1)> 1)
select * from (select a = count(*),from tables group by fields ) b where b.a >= 2
Select * from table group by all_fields having count(*) > 1
select fields from tables group by fields having sum(1)> 1)
select fields from tables group by fields having sum(1)> 1)
go
delete from tables
go
select * into tables from #tem
select fields from tables where 条件保留记录的条件 )
UNION ALL SELECT 1,1,1
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,1,1
UNION ALL SELECT 1,2,3
UNION ALL SELECT 7,8,9GO--T1表中现有数据SELECT * FROM T1column1 column2 column3
----------- ----------- -----------
1 1 1
1 1 1
1 2 3
1 1 1
1 2 3
7 8 9(所影响的行数为 6 行)--所要求实现的查询语句:BEGIN
IF (select count(*)from t1)<>(select count(*)from (select column1,column2,column3 from t1 group by column1,column2,column3 )as 不重复的行数)
BEGIN
SELECT DISTINCT'该表中有重复行'FROM T1
END
ELSE
BEGIN
SELECT DISTINCT'该表中没有重复行'FROM T1
END
END --另外几个有用的辅助语句:--查询原表的行数:
SELECT COUNT(*)FROM T1-----------
6(所影响的行数为 1 行)--查询原表中不重复的行数:
SELECT COUNT(*)FROM (SELECT column1,column2,column3 FROM T1 GROUP BY column1,column2,column3)AS 不重复的行数
-----------
3(所影响的行数为 1 行)--查询原表中不重复的数据:
SELECT * FROM (SELECT column1,column2,column3 FROM T1
GROUP BY column1,column2,column3)AS 不重复的记录column1 column2 column3
----------- ----------- -----------
1 1 1
1 2 3
7 8 9(所影响的行数为 3 行)
--查询原表中重复的数据
SELECT * FROM T1 GROUP BY column1,column2,column3
HAVING SUM(1)>1column1 column2 column3
----------- ----------- -----------
1 1 1
1 2 3(所影响的行数为 2 行)--删除测试环境DROP TABLE T1
into iceriver
from tables
godelete form tables
godelete from iceriver
where ice not in (select min(ice) from iceriver group by fields)
goalter table iceriver
drop ice
go
--如果原表有自增长字段,此处先设置自增长字段允许插入
insert into tables
select *
from iceriver
go
--如果原表有自增长字段,此处设置自增长字段不允许插入drop table iceriver
go
--这样原表中的数据就是不重复的,此处的条件只是fields不重复!
Select fields from tables group by fields having count(*) > 1重复的所有记录.
select * from tables where fields in(
select fields from tables group by fields having sum(1)> 1)
where b.a >= 2