有如下表
year month EMPID TIME
2009 5 001 2009-5-10 09:00:24
2009 5 001 2009-5-10 09:00:34我想删掉在同一分钟打卡记录,要怎么做.结果
year month EMPID TIME
2009 5 001 2009-5-10 09:00:24
year month EMPID TIME
2009 5 001 2009-5-10 09:00:24
2009 5 001 2009-5-10 09:00:34我想删掉在同一分钟打卡记录,要怎么做.结果
year month EMPID TIME
2009 5 001 2009-5-10 09:00:24
a b c d
1 2 3 4
1 5 3 5
1 2 7 9
以a、b为唯一字段,第一条和第三条的a、b完全相同,所以,需要删除第一条记录1 2 3 4 或者第三条记录1 2 7 9
即如下结果:
a b c d
1 2 3 4
1 5 3 5
或
a b c d
1 5 3 5
1 2 7 9 请问各位大侠这种sql语句怎么写
CREATE TABLE Tb1(id int, [a] varchar(255), [b] varchar(255), [c] varchar(255), [d] varchar(255))
INSERT Tb1(id, [a], [b], [c], [d])
SELECT 1, '1','2','3','4'
UNION ALL SELECT 2, '1','5','3','5'
UNION ALL SELECT 3, '1','2','7','9'
UNION ALL SELECT 4, '1','4','7','6' delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b )
select * from tb1 drop table tb1 如果要同时删除第一和第三行
即如下结果:
a b c d
1 5 3 5 语句如下: delete m from tb t
inner join
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
on m.a = n.a and m.b = n.b
或
delete * from tb as m,
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
where m.a = n.a and m.b = n.b
------------------------------------------------------------------------------------
在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢!
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
------------------------------------------------------------------------------------------------
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 方法二
有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复,比较容易解决,使用
select distinct * from tableName
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。 2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下
假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1)2)删除两条数据库中一模一样的记录
在建表时应设置一个唯一标识符(一般用IDENTITY用系统产生整型的自增长序列号,格式为IDENTITY ( seed , increment )),即便是有重复记录也能通过唯一标识符找到并处理掉。若没有该字段能够破坏原先表结构的可以用插入字段的方式来处理。
如:
alter table tb add idx int IDENTITY (1,1)
如果表的数据量不大且允许删除重建那么可以使用
select distinct * into #temptable from yourtable
delete table yourtable
insert yourtable select * from #temptable
如果不允许表删除重建则只能用变量,删除重复记录,可用Having语句可以找出两条相同的记录
create proc delchongfu
as
select * into #temptable from item group by userindex,avatarindex,ItemUUID having count(*)>1
delete from item where userindex in (select userindex from #temptable)
insert into item select * from #temptable
上诉语句也有问题,因为他要注意要匹配的字段必须具有一定的唯一性,否则会存在问题,可能会多删除部分记录。因此要被所有字段都进行匹配。 create proc newdelchongfu
as
select * into #temptable from item group by userindex,avatarindex,ItemUUID having count(*)>1
delete item from item as i,#temptable as t where i.userindex=t.userindex and i.avatarindex=t.avatarindex and i.ItemUUID=t.ItemUUID
insert into item select * from #temptable 在SQL server 2005也可以通过row_number()只是生成了新的查询结果,并不会对表中的原有数据造成影响因此无法通过这一函数确切的删除表中的重复列
year month EMPID TIME
2009 5 001 2009-5-10 09:00:24
2009 5 001 2009-5-10 09:00:34 我想删掉在同一分钟打卡记录,要怎么做. 结果
year month EMPID TIME
2009 5 001 2009-5-10 09:00:24
*/declare @t table([year] int,[month] int,EMPID varchar(3),[TIME] datetime)
insert @t select 2009,5,'001','2009-5-10 09:00:24'
insert @t select 2009,5,'001','2009-5-10 09:00:34'
insert @t select 2009,5,'001','2009-5-10 09:12:34'
insert @t select 2009,5,'001','2009-5-10 09:00:40'
insert @t select 2009,5,'001','2009-6-10 09:00:34'
insert @t select 2009,5,'001','2009-5-11 09:00:34'
insert @t select 2009,5,'001','2009-5-11 09:00:34'select * from @t
order by 4;with temptb
AS
(
select *,
neworder=row_number() over(partition by convert(char(16),[time],120) order by [time])
from @t
)
select [year],[month],EMPID,[TIME]
from temptb
where neworder=1/*
year month EMPID TIME
----------- ----------- ----- -----------------------
2009 5 001 2009-05-10 09:00:24.000
2009 5 001 2009-05-10 09:00:34.000
2009 5 001 2009-05-10 09:00:40.000
2009 5 001 2009-05-10 09:12:34.000
2009 5 001 2009-05-11 09:00:34.000
2009 5 001 2009-05-11 09:00:34.000
2009 5 001 2009-06-10 09:00:34.000(7 行受影响)year month EMPID TIME
----------- ----------- ----- -----------------------
2009 5 001 2009-05-10 09:00:24.000
2009 5 001 2009-05-10 09:12:34.000
2009 5 001 2009-05-11 09:00:34.000
2009 5 001 2009-06-10 09:00:34.000(4 行受影响)*/
drop table tb
go create table tb ([year] int, [month] int, EMPID varchar(10), [TIME] smalldatetime)
go insert into tb([year],[month],empid,[time])
select 2009, 5, '001', '2009-5-10 09:02:24' union all
select 2009, 5, '001', '2009-5-11 09:12:24' union all
select 2009, 5, '001', '2009-5-12 09:05:24' union all
select 2009, 5, '001', '2009-5-12 09:02:24' union all
select 2009, 5, '002', '2009-5-13 09:12:24' union all
select 2009, 5, '002', '2009-5-13 09:22:24' union all
select 2009, 5, '002', '2009-5-13 09:10:24' union all
select 2009, 5, '002', '2009-5-13 09:40:24'
go
SELECT CONVERT(VARCHAR(8),GETDATE(),114)--查询结果
SELECT [YEAR],[MONTH],EMPID,CONVERT(VARCHAR,[TIME],112) AS TDATE,
DATEDIFF(n,'09:05:00',convert(varchar(8),MIN([TIME]),114)) as
FROM TB
GROUP BY [YEAR],[MONTH],EMPID,CONVERT(VARCHAR,[TIME],112) /* 结果
YEAR MONTH EMPID TDATE
----------- ----------- ---------- ------------------------------ -----------
2009 5 001 20090510 -3
2009 5 001 20090511 7
2009 5 001 20090512 -3
2009 5 002 20090513 5(4 行受影响)
*/
INSERT @TB
SELECT 2009, 5, '001', '2009-5-10 09:00:24' UNION ALL
SELECT 2009, 5, '001', '2009-5-10 09:00:34'DELETE @TB
FROM @TB AS T
WHERE EXISTS(SELECT 1 FROM @TB WHERE EMPID=T.EMPID AND [TIME] BETWEEN DATEADD(SS,-59,T.TIME) AND T.TIME HAVING COUNT(*)>1)SELECT * FROM @TB
/*
year month EMPID TIME
----------- ----------- ----- -----------------------
2009 5 001 2009-05-10 09:00:24.000
*/
create table time1(year varchar(20),month varchar(20),EMPID varchar(20),TIME datetime )
insert into time1(year,month,empid,time)
select '2009','5','001','2009-5-10 09:00:24' union all
select '2009','5','001','2009-5-10 09:00:34'
select * from time1 where convert(varchar(10),time,120) in
(select convert(varchar(10),time,120) from time1
group by convert(varchar(10),time,120)
having count(convert(varchar(10),time,120))>1)
declare @tb table (year int,month int,empid int,time datetime)
insert into @tb select 2009,5,1,'2009-5-10 09:00:24'
union all select 2009,5,1,'2009-5-10 09:00:34'
select * from @tb a where not exists
(select 1 from @tb where a.year=year and a.month=month and time<a.time)(2 行受影响)
year month empid time
----------- ----------- ----------- -----------------------
2009 5 1 2009-05-10 09:00:24.000(1 行受影响)
drop table tbcreate table tb ([year] int,[month] int , EMPID INT ,[time] datetime)
insert tb
select 2009,5,001,'2009-5-10 09:00:24' union all
select 2009,5,001,'2009-5-10 09:00:34' union all
select 2009,5,001,'2009-5-11 09:00:34' union all
select 2009,5,001,'2009-5-10 09:00:59' union all
select 2009,5,002,'2009-5-10 09:00:34' union all
select 2009,5,002,'2009-5-10 09:00:04'select * from tb where [time] in( select min( [time])as[TIME] from tb where [time] between dateadd(ss,-59,[time]) and [time] group by EMPID)
/*
2009 5 1 2009-05-10 09:00:24.000
2009 5 2 2009-05-10 09:00:04.000
*/
drop table tbcreate table tb ([year] int,[month] int , EMPID INT ,[time] datetime)
insert tb
select 2009,5,001,'2009-5-10 09:01:24' union all
select 2009,5,001,'2009-5-10 09:00:34' union all
select 2009,5,001,'2009-5-11 09:00:44' union all
select 2009,5,001,'2009-5-10 09:00:59' union all
select 2009,5,002,'2009-5-10 09:00:34' union all
select 2009,5,002,'2009-5-10 09:00:04'delete t from tb t where exists(select 1 from tb where Convert(varchar(16),time,120)=Convert(varchar(16),t.time,120) and time <t.time)
select * from tb
/*-------------
2009 5 1 2009-05-10 09:01:24.000
2009 5 1 2009-05-11 09:00:44.000
2009 5 2 2009-05-10 09:00:04.000
------------*/
drop table tb
create table tb ([year] int,[month] int , EMPID INT ,[time] datetime)
insert tb
select 2009,5,001,'2009-5-10 09:01:24' union all
select 2009,5,001,'2009-5-10 09:00:34' union all
select 2009,5,001,'2009-5-11 09:00:44' union all
select 2009,5,001,'2009-5-10 09:00:59' union all
select 2009,5,002,'2009-5-10 09:00:34' union all
select 2009,5,002,'2009-5-10 09:00:04'delete a from tb a where exists(select 1 from tb where Convert(varchar(16),time,120)=Convert(varchar(16),a.time,120) and time <a.time)
select * from tb
/*
2009 5 1 2009-05-10 09:01:24.000
2009 5 1 2009-05-11 09:00:44.000
2009 5 2 2009-05-10 09:00:04.000*/
if object_id('tb') is not null
drop table tbcreate table tb ([year] int,[month] int , EMPID INT ,[time] datetime)
insert tb
select 2009,5,001,'2009-5-10 09:01:24' union all
select 2009,5,001,'2009-5-10 09:00:34' union all
select 2009,5,001,'2009-5-11 09:00:44' union all
select 2009,5,001,'2009-5-10 09:00:59' union all
select 2009,5,002,'2009-5-10 09:00:34' union all
select 2009,5,002,'2009-5-10 09:00:04'delete a from tb a where exists(select 1 from tb where Convert(varchar(16),time,120)=Convert(varchar(16),a.time,120) and time <a.time)select * from tb
declare @tb table (year int,month int,empid int,time datetime)
insert into @tb
select 2009,5,001,'2009-6-10 09:01:24' union all
select 2009,5,001,'2009-6-10 09:00:34' union all
select 2009,5,001,'2009-6-11 09:00:44' union all
select 2009,5,001,'2009-6-10 09:00:59' union all
select 2009,5,002,'2009-6-10 09:00:34' union all
select 2009,5,002,'2009-6-10 09:00:04' delete @tb from @tb a where exists(select 1 from @tb where Convert(varchar(16),time,120)=Convert(varchar(16),a.time,120) and time <a.time)