大家帮忙思考一个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
*/
[解决办法]
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)
)
)
[解决办法]
Haiwer(海阔天空) 大哥的想法很巧妙啊，赞一下
[解决办法]
create table tb(id varchar(8), fsrq datetime, fscs int)
go
insert 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 ', 1
select 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
****************/
[解决办法]
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 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 table #temp
(
id char(2),
fsrq datetime,
fscs int
)
go
insert 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
go
select 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)
*/
