其中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.577
D字段是时间字段,我要保留时间最前面的一条数据,其余B字段重复的都删除···
问下sql改怎么实现·
select B from TEST group by B having count(*) >1
这条是查询出来重复的字段的数据·
14 个解决方案
#1
select B from TEST group by B having count(*) =1
#2
比如B字段中,ID为1,2的相同,我就保留 D字段的时间为2010-08-18 20:01:19.810
时间最前面的数据··
求大侠帮帮忙
时间最前面的数据··
求大侠帮帮忙
#3
但是我需要的是,B字段重复的数据保留一条啊··时间排在最前面的
#4
我要删除数据啊··重复的数据,保留时间最小的一条·
大侠们来帮忙啊·
大侠们来帮忙啊·
#5
select * from Test t where D=(select min(D) from Test where B=t.B)
这个是要的。
#6
我不是查询啊·我是要的删除其中的数据啊·
#7
用delete from不就得了
#8
不如新建一张表得了?
create table a
insert a
select...
create table a
insert a
select...
#9
delete from Test
select * from Test t where D>(select min(D) from Test where B=t.B)
#10
搞错 了,请无视...
#11
嗯,谢谢楼上的兄弟姐妹,根据时间大小来删除的数据,一条sql真的好难写·
#12
delete from Test where ID in
(select ID from Test t where D>(select min(D) from Test where B=t.B))
如果ID是唯一的,试试
#13
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
*/
#14
DELETE FROM member WHERE memberid not in(SELECT memberid
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语句·
我而测试通过了·谢谢大家了·
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语句·
我而测试通过了·谢谢大家了·
#1
select B from TEST group by B having count(*) =1
#2
比如B字段中,ID为1,2的相同,我就保留 D字段的时间为2010-08-18 20:01:19.810
时间最前面的数据··
求大侠帮帮忙
时间最前面的数据··
求大侠帮帮忙
#3
但是我需要的是,B字段重复的数据保留一条啊··时间排在最前面的
#4
我要删除数据啊··重复的数据,保留时间最小的一条·
大侠们来帮忙啊·
大侠们来帮忙啊·
#5
select * from Test t where D=(select min(D) from Test where B=t.B)
这个是要的。
#6
我不是查询啊·我是要的删除其中的数据啊·
#7
用delete from不就得了
#8
不如新建一张表得了?
create table a
insert a
select...
create table a
insert a
select...
#9
delete from Test
select * from Test t where D>(select min(D) from Test where B=t.B)
#10
搞错 了,请无视...
#11
嗯,谢谢楼上的兄弟姐妹,根据时间大小来删除的数据,一条sql真的好难写·
#12
delete from Test where ID in
(select ID from Test t where D>(select min(D) from Test where B=t.B))
如果ID是唯一的,试试
#13
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
*/
#14
DELETE FROM member WHERE memberid not in(SELECT memberid
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语句·
我而测试通过了·谢谢大家了·
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语句·
我而测试通过了·谢谢大家了·