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
……
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 cardno
from tb
where datediff(SS,collectdetails,GETDATE())<=60
group by cardno
having count(*)>1
select A.cardno,min(A.collectdetails) as collectdetails
from table_name as A
where exists (select 1 from table_name as B where A.cardno=B.cardno and datediff(s,A.collectdetails,B.collectdetails)<60)
group by A.cardno
order by A.cardno;
select userid,left(collectdetails,15) as collectdetails
from tb
group by userid,left(collectdetails,15)
having COUNT(*)>1你的collectdetails格式是不是varchar形式的?
if object_id('test',N'U')>0
drop table test
create table test(hospitalid int, userid varchar(5), cardno varchar(10), collectdetails varchar(20))
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,left(collectdetails,15) as collectdetails
from test
group by cardno,left(collectdetails,15)
having COUNT(*)>1
order by cardnodrop table test--结果
cardno collectdetails
---------- --------------------
0E256742 2013-9-22 10:14
0E256743 2013-9-22 13:15
0E256744 2013-9-22 13:14
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
datetime类型的用,不过看你的数据不是datetime类型的
--查询
select cardno,convert(varchar(16),collectdetails,120) as collectdetails
from test
group by cardno,convert(varchar(16),collectdetails,120)
having COUNT(*)>1
order by cardnodrop table test--结果
cardno collectdetails
---------- ----------------
0E256742 2013-09-22 10:14
0E256743 2013-09-22 13:15
0E256744 2013-09-22 13:14
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 cardnodrop 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(16,),min(A.collectdetails),120) as collectdetails
from table_name as A
where exists (select 1 from table_name as B where A.cardno=B.cardno and datediff(s,A.collectdetails,B.collectdetails)<60)
group by A.cardno
order by A.cardno;
datediff 函数导致溢出。用于分隔两个日期/时间实例的日期部分的数字太大。请尝试使用日期部分精度较低的 datediff。
日期时间可能是跨年的,超出最大值了
datediff 函数导致溢出。用于分隔两个日期/时间实例的日期部分的数字太大。请尝试使用日期部分精度较低的 datediff。
日期时间可能是跨年的,超出最大值了如果返回值超出 int 的范围(-2,147,483,648 到 +2,147,483,647),则会返回一个错误。对于 second,最大差值为 68 年修改为 试试-- 如果大于1年则 忽略
where exists (select 1 from table_name as B where A.cardno=B.cardno and case when datediff(yy,A.collectdetails,B.collectdetails)>=1 then 1=2
else datediff(s,A.collectdetails,B.collectdetails)<60)
少了 end-- 如果大于1年则 忽略
where exists (select 1 from table_name as B where A.cardno=B.cardno and case when datediff(yy,A.collectdetails,B.collectdetails)>=1 then 1=2
else datediff(s,A.collectdetails,B.collectdetails)<60) end
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;