表名: TEST 字段: Id,A,B,C,D
其中B字段包含重复值;Id A B C D
1 11 a 34 2010-08-18 20:01:19.810
2 22 a 35 2010-08-18 20:02:01.153
3 33 d ht 2010-08-18 20:03:53.577D字段是时间字段,我要保留时间最前面的一条数据,其余B字段重复的都删除···
问下sql改怎么实现·
select B from TEST group by B having count(*) >1
这条是查询出来重复的字段的数据·
其中B字段包含重复值;Id A B C D
1 11 a 34 2010-08-18 20:01:19.810
2 22 a 35 2010-08-18 20:02:01.153
3 33 d ht 2010-08-18 20:03:53.577D字段是时间字段,我要保留时间最前面的一条数据,其余B字段重复的都删除···
问下sql改怎么实现·
select B from TEST group by B having count(*) >1
这条是查询出来重复的字段的数据·
时间最前面的数据··
求大侠帮帮忙
这个是要的。
create table a
insert a
select...
select * from Test t where D>(select min(D) from Test where B=t.B)
(select ID from Test t where D>(select min(D) from Test where B=t.B))如果ID是唯一的,试试
create table test
(
id int identity(1,1),
A int,
B varchar(20),
C varchar(20),
D nvarchar(20)
)
insert into test
select 11,'a','34','2010-08-18 20:01' union all
select 22,'a','35','2010-08-18 20:02' union all
select 33,'d','ht','2010-08-18 20:03'
select * from test
/*
id A B C D
----------- ----------- -------------------- -------------------- -------------------
11 11 a 34 2010-08-18 20:01:00.000
12 22 a 35 2010-08-18 20:02:00.000
13 33 d ht 2010-08-18 20:03:00.000
*/delete test where exists(select 1 from test t where t.B=test.B and t.D<test.D)
select * from test
/*
id A B C D
----------- ----------- -------------------- -------------------- -------------------
11 11 a 34 2010-08-18 20:01:00.000
13 33 d ht 2010-08-18 20:03:00.000
*/
FROM member as TS,(SELECT mobile, MIN(certdate) certdate FROM member group by mobile having count(*) >1 ) as A
WHERE TS.mobile = A.mobile
AND TS.certdate = A.certdate)
and mobile IN (select mobile from member group by mobile having count(*) >1 )
这个是最后的SQL语句·
我而测试通过了·谢谢大家了·