查询同一天内的重复记录,如
NO. name time201010 aaaa 2010-05-01
201010 aaaa 2010-05-01
201010 aaaa 2010-05-01
201223 bbb 2010-05-01
201223 bbb 2010-05-01
201334 b32 2010-05-05
2233334 434b32 2010-05-06想找出
NO. name time201010 aaaa 2010-05-01
201010 aaaa 2010-05-01
201010 aaaa 2010-05-01
201223 bbb 2010-05-01
201223 bbb 2010-05-01求语句
NO. name time201010 aaaa 2010-05-01
201010 aaaa 2010-05-01
201010 aaaa 2010-05-01
201223 bbb 2010-05-01
201223 bbb 2010-05-01
201334 b32 2010-05-05
2233334 434b32 2010-05-06想找出
NO. name time201010 aaaa 2010-05-01
201010 aaaa 2010-05-01
201010 aaaa 2010-05-01
201223 bbb 2010-05-01
201223 bbb 2010-05-01求语句
from tb
group by NO,name, time
having count(1)>1
(select [NO.] ,name, time from tb group by [NO.] ,name, time having count(1) > 1) n
where [NO.] = m.[NO.] and name = m.name and time = m.time
)
insert into tb values('201010' , 'aaaa' , '2010-05-01')
insert into tb values('201010' , 'aaaa' , '2010-05-01')
insert into tb values('201010' , 'aaaa' , '2010-05-01')
insert into tb values('201223' , 'bbb' , '2010-05-01')
insert into tb values('201223' , 'bbb' , '2010-05-01')
insert into tb values('201334' , 'b32' , '2010-05-05')
insert into tb values('2233334', '434b32', '2010-05-06')
goselect m.* from tb m where exists (select 1 from
(select [NO.] ,name, time from tb group by [NO.] ,name, time having count(1) > 1) n
where [NO.] = m.[NO.] and name = m.name and time = m.time
)drop table tb/*
NO. name time
-------------------- -------------------- ------------------------------------------------------
201010 aaaa 2010-05-01 00:00:00.000
201010 aaaa 2010-05-01 00:00:00.000
201010 aaaa 2010-05-01 00:00:00.000
201223 bbb 2010-05-01 00:00:00.000
201223 bbb 2010-05-01 00:00:00.000(所影响的行数为 5 行)*/
up 有答案了, 就jf啦