表:TBL_Info
字段:
infoId int
title varchar(20)
Content text1、如何删除表中所有数据相同的值的数据呢?
2、如何删除数据表中某个字段数据相同的数据呢(比如title:删除所有title相同的数据)?
3、如何统计表中title相同数据的数目?
字段:
infoId int
title varchar(20)
Content text1、如何删除表中所有数据相同的值的数据呢?
2、如何删除数据表中某个字段数据相同的数据呢(比如title:删除所有title相同的数据)?
3、如何统计表中title相同数据的数目?
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
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) 3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 比方说在A表中存在一个字段“name”,而且不同记录之间的“name”值有可能会相同,
现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
Select Name,Count(*) From A Group By Name Having Count(*) > 1 如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-15 23:18:57
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int)
insert [tb]
select 1,2 union all
select 2,2 union all
select 3,3 union all
select 4,4
--------------开始查询--------------------------
select
count(1)
from
tb t
where
exists(select 1 from tb where b=t.b and a<>t.a)----------------结果----------------------------
/*-----------
2(1 行受影响)
*/
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/
无解!所有数据都相同,你根本没办法删除其中一条。
如果是全部删除倒容易
delete from TBL_Info
where (infoId,title,Content) in (select infoId,title,Content from TBL_Info group by infoId,title,Content having count(*)>1)2、如何删除数据表中某个字段数据相同的数据呢(比如title:删除所有title相同的数据)?
delete from TBL_Info a
where exists (select 1 from TBL_Info where title=a.title and infoId!=a.infoId)3、如何统计表中title相同数据的数目?
select title,count(*) from TBL_Info group by title
表:TBL_Info
字段:
infoId int
title varchar(20)
Content text
byUser varchar(20)
createTime datetime1、如何删除表中数据相同的数据呢?(主键除外)
2、如何删除数据表中某个字段数据相同的数据呢(比如title:删除所有title相同的数据)?
3、如何统计表中title相同数据的数目?
--1.1相同时保留最小的infoId
delete TBL_Info from TBL_Info t where infoId not in (select min(id) from infoId where title = t.title and Content = t.Content and byUser = t.byUser and createTime = t.createTime)
--1.1相同时保留最大的infoId
delete TBL_Info from TBL_Info t where infoId not in (select max(id) from infoId where title = t.title and Content = t.Content and byUser = t.byUser and createTime = t.createTime)--2
delete from TBL_Info where title in (select title from TBL_Info group by title having count(1) > 1)--3
select title , count(1) from TBL_Info group by title
select title , count(*) from TBL_Info group by title
select title , count(title) from TBL_Info group by title