查询在wos1库中,多字段重复的数据:
select p1.* from tabTimeData p1,tabTimeData p2
where p1.id<>p2.id and p1.区站号 = p2.区站号 and p1.日期时间 = p2.日期时间
ORDER BY 区站号 DESC, 日期时间 DESC
我想保留id值较大的数据,不知道删除语句怎么写?
select p1.* from tabTimeData p1,tabTimeData p2
where p1.id<p2.id and p1.区站号 = p2.区站号 and p1.日期时间 = p2.日期时间
ORDER BY 区站号 DESC, 日期时间 DESC
就是把这个查询结果在wos1的tabTimeData表中删去。
select p1.* from tabTimeData p1,tabTimeData p2
where p1.id<>p2.id and p1.区站号 = p2.区站号 and p1.日期时间 = p2.日期时间
ORDER BY 区站号 DESC, 日期时间 DESC
我想保留id值较大的数据,不知道删除语句怎么写?
select p1.* from tabTimeData p1,tabTimeData p2
where p1.id<p2.id and p1.区站号 = p2.区站号 and p1.日期时间 = p2.日期时间
ORDER BY 区站号 DESC, 日期时间 DESC
就是把这个查询结果在wos1的tabTimeData表中删去。
--功能概述:删除重复记录在几千条记录里,存在着些相同的记录,如何能用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
现部分记录如下:
ID 区站号 日期时间 风向 风速 空气温度
256215 L6509 2009-6-23 2:00:00 227 14 153
256216 L6547 2009-6-23 3:00:00 249 11 160
256217 L6547 2009-6-23 3:00:00 236 12 160
256218 L6509 2009-6-23 2:00:00 212 18 152
------------------------------------------------------------------
我的表中同一区站号下,同一时间只应该有一条数据,现在不知道为什么出现了多条数据,我现在想做的是查询和删除多余的数据,每一个区站号每一个日期时间只保留一条数据即可,例如ID号较大的数据。因为数据库较为重要,因此先尝试查询,确定后再做删除。好久没上CSDN了,谢谢各位!
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-31 16:38:27
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([ID] int,[区站号] nvarchar(5),[日期时间] datetime,[风向] int,[风速] int,[空气温度] int)
Insert tb
Select 256215,'L6509','2009-6-23 2:00:00',227,14,153 union all
Select 256216,'L6547','2009-6-23 3:00:00',249,11,160 union all
Select 256217,'L6547','2009-6-23 3:00:00',236,12,160 union all
Select 256218,'L6509','2009-6-23 2:00:00',212,18,152
Go
--Select * from tb-->SQL查询如下:
delete tb where exists(select 1 from tb t where t.[区站号]=tb.[区站号] and t.id>tb.id)select * from tb
/*
ID 区站号 日期时间 风向 风速 空气温度
----------- ----- ----------------------- ----------- ----------- -----------
256217 L6547 2009-06-23 03:00:00.000 236 12 160
256218 L6509 2009-06-23 02:00:00.000 212 18 152(2 行受影响)
*/
Create table #a(a int,b int)
insert into #a
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,1delete #a
where a not in(
select min(a) from #a
group by b)select * from #a
----------结果-------------
a b
1 1
Create table #a(a int,b int)
insert into #a
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,1delete #a
where a not in(
select max(a) from #a
group by b)select * from #a
----------结果-------------
a b
1 1
Create table #a(a int,b int)
insert into #a
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,1delete #a
where a not in(
select max(a) from #a
group by b)select * from #a
----------结果-------------
a b
4 1