求大神光顾---sql 查询任意时间段内的数据条数
hospitalid, userid, cardno, collectdetails
……
1 001 0E256742 2013-9-22 10:14:15
1 001 0E256742 2013-9-22 10:14:25
1 002 0E256744 2013-9-22 13:14:15
1 002 0E256744 2013-9-22 13:14:25
1 001 0E256742 2013-9-22 13:14:35
1 003 0E256743 2013-9-22 13:15:15
1 003 0E256743 2013-9-22 13:15:45
1 003 0E256743 2013-9-22 13:16:55
1 003 0E256743 2013-9-22 15:20:55
……
举个例子 要查出1分钟内cardno数据条数超过1条的数据
如
0E256742 2013-9-22 10:14
0E256743 2013-9-22 13:15
0E256744 2013-9-22 13:14 数据 sql
[解决办法]
select a.*
from tb a
inner join
(
select userid,cardno, convert(varchar(16),collectdetails,120) as collectdetails,COUNT(1) as num
from tb
group by userid,cardno, convert(varchar(16),collectdetails,120)
having COUNT(1)>1
)b on a.userid=b.userid and a.cardno=b.cardno and convert(varchar(16),a.collectdetails,120)=b.collectdetails
if object_id('test',N'U')>0
drop table test
create table test(hospitalid int,userid varchar(5),cardno varchar(10),collectdetails datetime)
insert into test(hospitalid, userid, cardno, collectdetails)
select 1,'001','0E256742','2013-9-22 10:14:15' union all
select 1,'001','0E256742','2013-9-22 10:14:25' union all
select 1,'002','0E256744','2013-9-22 13:14:15' union all
select 1,'002','0E256744','2013-9-22 13:14:25' union all
select 1,'001','0E256742','2013-9-22 13:14:35' union all
select 1,'003','0E256743','2013-9-22 13:15:15' union all
select 1,'003','0E256743','2013-9-22 13:15:45' union all
select 1,'003','0E256743','2013-9-22 13:16:55' union all
select 1,'003','0E256743','2013-9-22 15:20:55'
--查询
select cardno,convert(varchar(16),collectdetails,120) as collectdetails
from test
group by cardno,convert(varchar(16),collectdetails,120)
having COUNT(*)>1
order by cardno
drop table test
--结果
cardno collectdetails
---------- ----------------
0E256742 2013-09-22 10:14
0E256743 2013-09-22 13:15
0E256744 2013-09-22 13:14
才发现昨天的代码是错的 不好意思
更正后的：
select A.cardno,convert(varchar(15),min(A.collectdetails),120) as collectdetails
from test as A
where exists (select 1 from test as B where A.cardno=B.cardno and B.collectdetails>A.collectdetails and (case when datediff(yy,A.collectdetails,B.collectdetails)>=1 then 1
when datediff(s,A.collectdetails,B.collectdetails)<60 then 0 end)=0)
group by A.cardno
order by A.cardno;
