假如我有一个这样的表:
id 发生日期 发生次数
-------------------------
id fsrq fscs
01 20070901 1
02 20070901 1
03 20070901 1
01 20070902 1
02 20070902 3
01 20070903 1
01 20070904 1
02 20070904 1
03 20070904 1
------------------------
现在,我想将fscs>=3,或者每天发生持续3天以上的帐号挑出来,比如说,01和02,
该怎么写这个SQL语句??
id 发生日期 发生次数
-------------------------
id fsrq fscs
01 20070901 1
02 20070901 1
03 20070901 1
01 20070902 1
02 20070902 3
01 20070903 1
01 20070904 1
02 20070904 1
03 20070904 1
------------------------
现在,我想将fscs>=3,或者每天发生持续3天以上的帐号挑出来,比如说,01和02,
该怎么写这个SQL语句??
create table tb(id varchar(9),fsrq smalldatetime,fscs int)
insert tb(id,fsrq,fscs)
select '01','20070901','1' union all
select '02','20070901','1' union all
select '03','20070901','1' union all
select '01','20070902','1' union all
select '02','20070902','3' union all
select '01','20070903','1' union all
select '01','20070904','1' union all
select '02','20070904','1' union all
select '03','20070904','1'
go
--执行测试语句
select id,min(fsrq) as 开始时间,max(fsrq) as 结束时间,sum(fscs) as fscs
from(
select a.id,a.fsrq,a.fscs,count(1) as ii
from tb a
join tb b on b.id=a.id and b.fsrq<=a.fsrq
group by a.id,a.fsrq,a.fscs
)c
group by id, DATEADD(day,-c.ii,fsrq)
having sum(fscs) >= 3
go
--删除测试环境
drop table tb
go
/*--测试结果
id 开始时间 结束时间 fscs
--------- ---------------- --------------------- -----------
01 2007-09-01 00:00:00 2007-09-04 00:00:00 4
02 2007-09-01 00:00:00 2007-09-02 00:00:00 4*/
from tablename a
where fscs>=3
or (
exists (select 1 from tablename b
where b.id=a.id
and b.fsrq=dateadd(day,-1,a.fsrq)
)
and exists (select 1 from tablename b
where b.id=a.id
and b.fsrq=dateadd(day,1,a.fsrq)
)
)
declare @tablename table(id varchar(9),fsrq smalldatetime,fscs int)
insert @tablename(id,fsrq,fscs)
select '01','20070901','1' union all
select '02','20070901','1' union all
select '03','20070901','1' union all
select '01','20070902','1' union all
select '02','20070902','3' union all
select '01','20070903','1' union all
select '01','20070904','1' union all
select '02','20070904','1' union all
select '03','20070904','1'select distinct id
from @tablename a
where fscs>=3
or (
exists (select 1 from @tablename b
where b.id=a.id
and b.fsrq=dateadd(day,-1,a.fsrq)
)
and exists (select 1 from @tablename b
where b.id=a.id
and b.fsrq=dateadd(day,1,a.fsrq)
)
)--结果
id
---------
01
02(所影响的行数为 2 行)
goinsert tb select '01', '20070901', 1
union all select '02', '20070901', 1
union all select '03', '20070901', 1
union all select '01', '20070902', 1
union all select '02', '20070902', 3
union all select '01', '20070903', 1
union all select '01', '20070904', 1
union all select '02', '20070904', 1
union all select '03', '20070904', 1select distinct id from tb
where fscs>=3 or
(select count(1) from tb as t where id=tb.id and fscs>0 and abs(datediff(day,fsrq,tb.fsrq))=1)>1
drop table tb/**************
id
--------
01
02(2 row(s) affected
****************/
FROM TABLE
GROUP BY id, fsrq
HAVING SUM(fscs)>=3
from @tablename a
where fscs>=3
or (
exists (select 1 from @tablename b
where b.id=a.id
and b.fsrq=dateadd(day,-1,a.fsrq)
and datename(week, a.fsrq)=datename(week, b.fsrq)
)
and exists (select 1 from @tablename b
where b.id=a.id
and b.fsrq=dateadd(day,1,a.fsrq)
and datename(week, a.fsrq)=datename(week, b.fsrq)
)
)
create function fn_NextWeekDay(
@Today DateTime
)
returns DateTime
as
begin
return case datepart(weekday,@Today) when 6 then dateadd(day,3,@today)
when 7 then dateadd(day,2,@today)
else dateadd(day,1,@today)
end
end
gocreate function fn_PreWeekDay(
@Today DateTime
)
returns DateTime
as
begin
return case datepart(weekday,@Today) when 2 then dateadd(day,-3,@today)
when 1 then dateadd(day,-2,@today)
else dateadd(day,-1,@today)
end
end
go--
create table #temp
(
id char(2),
fsrq datetime,
fscs int
)
goinsert into #temp
select '01', '20070901', 1 union all
select '02', '20070901', 1 union all
select '03', '20070901', 1 union all
select '01', '20070902', 1 union all
select '02', '20070902', 3 union all
select '01', '20070903', 1 union all
select '01', '20070904', 1 union all
select '02', '20070904', 1 union all
select '03', '20070904', 1
goselect distinct a.id
from #temp a join #temp b on a.id=b.id and a.fsrq=dateadd(day,1,b.fsrq) or a.fscs>=3
join #temp c on b.id=c.id and b.fsrq=dateadd(day,1,c.fsrq) go
drop table #temp/*(9 row(s) affected)id
----
01
02(2 row(s) affected)
*/
insert into @t select '01','20070901',1
insert into @t select '02','20070901',1
insert into @t select '03','20070901',1
insert into @t select '01','20070902',1
insert into @t select '02','20070902',3
insert into @t select '01','20070903',1
insert into @t select '01','20070904',1
insert into @t select '02','20070904',1
insert into @t select '03','20070904',1select
distinct a.id
from
@t a
left join @t b on a.id=b.id and a.发生日期=b.发生日期+1
left join @t c on a.id=c.id and a.发生日期=c.发生日期-1
where
a.发生次数+isnull(b.发生次数,0)+isnull(c.发生次数,0)>=3/*
id
----------
01
02
*/
或者
每天发生持续3天以上的帐号挑出来2个不是一个意思啊?我是不是钻牛角尖了啊?
1.fscs>=3(给你加上连续的工作日):但是假如第一天是发生一次,第二天发生二次。这个符号条件
2.每天发生持续3天以上:(第一个就不满足了,因为要持续3天)应该是:找出一个账号,连续3个工作日发生交易次数超过3次的账号找出来(而且要说明一下包不包括周五-周一,算不算工作日)可能钻牛角尖了。呵呵