[code=SQL]select name from tb a where exists(select 1 from tb where datediff(mm,a.time,time)<5 and datediff(mm,a.time,time)<>0 and name = a.name)[code]
select name from tb a where exists(select 1 from tb where datediff(mm,a.time,time)<5 and datediff(mm,a.time,time)<>0 and name = a.name)
to :wer123q 我们的时间(时分秒)是字符型的,按照你的这种方式提示从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。
提示从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。 可以提前转换下格式:select name from table1 as a where exists ( select 1 from table1 b where datediff(mm, Convert(datetime,a.time), Convert(datetime,time))<5 and datediff(mm, Convert(datetime,a.time),Convert(datetime,time))<>0 and name=a.name) )
DECLARE @t TABLE(id int,plattime varchar(10),amount int) INSERT INTO @t SELECT 1, '000100',100 UNION ALL SELECT 2,'000101',200 UNION ALL SELECT 1,'000103',10 SELECT * FROM @t a WHERE EXISTS( SELECT * FROM @t WHERE id=a.id AND DATEDIFF(second,'2008-01-01 '+SUBSTRING(a.plattime,1,2)+':'+SUBSTRING(a.plattime,3,2)+':'+SUBSTRING(a.plattime,5,2), '2008-01-01 '+SUBSTRING(plattime,1,2)+':'+SUBSTRING(plattime,3,2)+':'+SUBSTRING(plattime,5,2)) Between 1 AND 300 )
是这样么select name from tb a where exists(select 1 from tb where datediff(ss,cast(a.time as datetime),cast(time as datetime))<5 and datediff(ss,cast(a.time as datetime),cast(time as datetime))<>0 and name = a.name)
--一个按五分钟分段统计的例
create table tb(时间 datetime , 金额 int)
insert into tb values('2007-1-1 10:00:23' , 8 )
insert into tb values('2007-1-1 10:01:24' , 4 )
insert into tb values('2007-1-1 10:05:00' , 2 )
insert into tb values('2007-1-1 10:06:12' , 3 )
insert into tb values('2007-1-1 10:08:00' , 1 )
insert into tb values('2007-1-1 10:12:11' , 5 )
go--时间段>=10:00:00 and 时间段<10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:00:00.000 3 14
2007-01-01 10:05:00.000 2 4
2007-01-01 10:10:00.000 1 5
(所影响的行数为 3 行)
*/--时间段>10:00:00 and 时间段<=10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:00:00.000 2 12
2007-01-01 10:05:00.000 3 6
2007-01-01 10:10:00.000 1 5(所影响的行数为 3 行)
*/drop table tb
数据结构 id,plattime,amount
1, 000100,100元
2, 000101,200元
1,000103,10元
数据就像这样由于5分钟是动态的5分钟,也就是说24个小时任意的5分钟,用户交易笔数达到2笔都行。就是想要这样的结果。
只要有记录存在的,用户就一定有一笔交易了 ;
我们的时间(时分秒)是字符型的,按照你的这种方式提示从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。
可以提前转换下格式:select name
from table1 as a
where exists
(
select 1
from table1 b
where datediff(mm, Convert(datetime,a.time), Convert(datetime,time))<5 and datediff(mm, Convert(datetime,a.time),Convert(datetime,time))<>0 and name=a.name)
)
DECLARE @t TABLE(id int,plattime varchar(10),amount int)
INSERT INTO @t
SELECT
1, '000100',100 UNION ALL SELECT
2,'000101',200 UNION ALL SELECT
1,'000103',10 SELECT
*
FROM @t a
WHERE EXISTS(
SELECT *
FROM @t
WHERE id=a.id AND DATEDIFF(second,'2008-01-01 '+SUBSTRING(a.plattime,1,2)+':'+SUBSTRING(a.plattime,3,2)+':'+SUBSTRING(a.plattime,5,2),
'2008-01-01 '+SUBSTRING(plattime,1,2)+':'+SUBSTRING(plattime,3,2)+':'+SUBSTRING(plattime,5,2)) Between 1 AND 300
)
我把这两个字段转换为datetime时总出错 谁有成功的sql呀
convert(datetime,platdate+' '+
substring(plattime,1,2)+':'+
substring(plattime,3,2)+':'+
substring(plattime,5,2))
from henan1month就这个语句都报错
from table1 as a
inner join table1 as b
on a.name = b.name and a.time<>b.time and(你整理好的时间类型条件)
group by a.name
having count(a.name)>2
感谢 bugfoximc
按照你的sql,n分钟内交易两笔以上或者3笔以上都可以搞定。谢谢。但是如果交易4笔以上就难搞定了 ,希望能继续帮忙 呵呵
这个和bugfoximc的结果一样 是正确的 可以解决交易3次以下的问题。