select b.DT as 日期, a.classID as 栏目代码, count(c.userID) as 定阅人数, count(d.userID) as 取消人数, count(c.userID) - count(d.userID) as 今天用户总数 from 类别表 a inner join (select distinct startDT as DT from 明细表 union select distinct cancelDT from 明细表) b on 1 = 1 left join 明细表 c on a.classid = c.classid and b.DT = c.startDT left join 明细表 d on a.classid = d.classid and b.DT = d.cancelDT group by b.DT a.classID
修正:select b.DT as 日期, a.classID as 栏目代码, count(c.userID) as 定阅人数, count(d.userID) as 取消人数, count(c.userID) - count(d.userID) as 今天用户总数 from t1 a inner join (select distinct startDT as DT from t2 where startDT is not null union select distinct cancelDT from t2 where cancelDT is not null) b on 1 = 1 left join t2 c on a.classid = c.classid and b.DT = c.startDT left join t2 d on a.classid = d.classid and b.DT = d.cancelDT group by b.DT, a.classID order by b.DT, a.classID
create proc p1 @startdate smalldatetime, @enddate smalldatetime as begin create table #t( fdate smalldate, fclass ... ...); create index ind_t on(fdate); declare @class ... declare class scroll cursor for select from open class; fetch next from class into @class declare @incount integer; declare @outcount integer; declare @date smalldatetime; set @date=@startdate; while @date<=@enddate begin while @@fetch_status=0 begin --统计 end --插入一条统计记录 fetch first from class into ...; set @date=dateadd(Day, 1, @date) end close class; deallocate class; select * from #t end;
select b.DT as 日期, a.classID as 栏目代码, count(c.userID) as 定阅人数, count(d.userID) as 取消人数, count(c.userID) - count(d.userID) as 今天用户总数 from 类别表 a inner join (select distinct startDT as DT from 明细表 union select distinct cancelDT from 明细表) b on 1 = 1 left join 明细表 c on a.classid = c.classid and b.DT = c.startDT left join 明细表 d on a.classid = d.classid and b.DT = d.cancelDT group by b.DT a.classID
分步实现:select b.DT as 日期, a.classID as 栏目代码, count(c.userID) as 定阅人数, count(d.userID) as 取消人数, count(c.userID) - count(d.userID) as 今天用户总数 into #t from t1 a inner join (select distinct startDT as DT from t2 where startDT is not null union select distinct cancelDT from t2 where cancelDT is not null) b on 1 = 1 left join t2 c on a.classid = c.classid and b.DT = c.startDT left join t2 d on a.classid = d.classid and b.DT = d.cancelDT group by b.DT, a.classID order by b.DT, a.classID select c.*, d.今天用户总数 from #t c, (select a.日期, a.栏目代码, sum(b.定阅人数) - sum(b.取消人数) as 今天用户总数 from #t a,#t b where a.日期>=b.日期 and a.栏目代码 = b.栏目代码 group by a.日期,a.栏目代码 order by a.日期,a.栏目代码) d where c.日期 = d.日期 and c.栏目代码 = d.栏目代码 order by c.日期,c.栏目代码
To:zjcxc(邹建)大侠:时间段不作参数,每天都有记录。做为分组的字段
后一句SQL要去掉子查询中的order by子句:select c.*, d.今天用户总数 from #t c, (select a.日期, a.栏目代码, sum(b.定阅人数) - sum(b.取消人数) as 今天用户总数 from #t a,#t b where a.日期>=b.日期 and a.栏目代码 = b.栏目代码 group by a.日期,a.栏目代码) d where c.日期 = d.日期 and c.栏目代码 = d.栏目代码 order by c.日期,c.栏目代码
--创建查询处理的存储过程(查询全部数据)create proc p_qry as set nocount on declare @date_begin char(8)--生成日期区间表 declare @i int select @date_begin=min(date_begin) ,@i=datediff(day,@date_begin,max(date_end))+1 from 明细表 set rowcount @i select id=identity(int,0,1),dat=cast(@date_begin as char(8)) into #t from syscolumns a,syscolumns b update #t set dat=convert(char(8),dateadd(day,id,@date_begin),112)--查询处理 select 日期=a.dat ,栏目代码=a.classID ,定阅人数=isnull(b.DZusers,0) ,取消人数=isnull(c.QXusers,0) ,今天用户总数=0 into #t1 from( select a.dat,b.classID from #t,类别表 )a left join( select classID,startDT,DZusers=count(*) from 明细表 group by classID,startDT )b on a.classID=b.classID and a.dat=b.startDT left join( select classID,cancelDT,QXusers=count(*) from 明细表 group by classID,cancelDT )c on a.classID=c.classID and a.dat=c.cancelDT and b.startDT=c.cancelDT--生成"今天用户总数" update a set Todayusers=( select sum(DZusers-QXusers) from 日志表 where classID=a.classID and log_day<=a.log_day) from #t1 a select * from #t1 go
select distinct aa.栏目,aa.代码,(select isnull(count(*),0) from 明细表 b where b.f_classID=aa.栏目 and b.startDT=aa.日期) as 订阅人数 , (select isnull(count(*),0) from 明细表 b where b.f_classID=aa.栏目 and b.cancelDT=aa.日期) as 取消人数 , (select isnull(count(*),0) from 明细表 b where b.f_classID=aa.栏目 and b.startDT>=aa.日期) - (select isnull(count(*),0) from 明细表 b where b.f_classID=aa.栏目 and b.cancelDT>=aa.日期) as 今天用户总数 from (select distinct startDT as 日期,classID as 栏目代码 from 明细表 union all (select distinct cancelDT as 日期,classID as 栏目代码 from 明细表) aa
b.DT as 日期,
a.classID as 栏目代码,
count(c.userID) as 定阅人数,
count(d.userID) as 取消人数,
count(c.userID) - count(d.userID) as 今天用户总数
from
类别表 a
inner join
(select distinct startDT as DT from 明细表
union
select distinct cancelDT from 明细表) b
on
1 = 1
left join
明细表 c
on
a.classid = c.classid and b.DT = c.startDT
left join
明细表 d
on
a.classid = d.classid and b.DT = d.cancelDT
group by
b.DT
a.classID
b.DT as 日期,
a.classID as 栏目代码,
count(c.userID) as 定阅人数,
count(d.userID) as 取消人数,
count(c.userID) - count(d.userID) as 今天用户总数
from
t1 a
inner join
(select distinct startDT as DT from t2 where startDT is not null
union
select distinct cancelDT from t2 where cancelDT is not null) b
on
1 = 1
left join
t2 c
on
a.classid = c.classid and b.DT = c.startDT
left join
t2 d
on
a.classid = d.classid and b.DT = d.cancelDT
group by
b.DT,
a.classID
order by
b.DT,
a.classID
@startdate smalldatetime,
@enddate smalldatetime
as
begin
create table #t(
fdate smalldate,
fclass ... ...);
create index ind_t on(fdate); declare @class ...
declare class scroll cursor for
select
from
open class;
fetch next from class into @class declare @incount integer;
declare @outcount integer;
declare @date smalldatetime;
set @date=@startdate;
while @date<=@enddate
begin
while @@fetch_status=0
begin
--统计
end
--插入一条统计记录
fetch first from class into ...;
set @date=dateadd(Day, 1, @date)
end close class;
deallocate class;
select * from #t
end;
b.DT as 日期,
a.classID as 栏目代码,
count(c.userID) as 定阅人数,
count(d.userID) as 取消人数,
count(c.userID) - count(d.userID) as 今天用户总数
from
类别表 a
inner join
(select distinct startDT as DT from 明细表
union
select distinct cancelDT from 明细表) b
on
1 = 1
left join
明细表 c
on
a.classid = c.classid and b.DT = c.startDT
left join
明细表 d
on
a.classid = d.classid and b.DT = d.cancelDT
group by
b.DT
a.classID
b.DT as 日期,
a.classID as 栏目代码,
count(c.userID) as 定阅人数,
count(d.userID) as 取消人数,
count(c.userID) - count(d.userID) as 今天用户总数
into
#t
from
t1 a
inner join
(select distinct startDT as DT from t2 where startDT is not null
union
select distinct cancelDT from t2 where cancelDT is not null) b
on
1 = 1
left join
t2 c
on
a.classid = c.classid and b.DT = c.startDT
left join
t2 d
on
a.classid = d.classid and b.DT = d.cancelDT
group by
b.DT,
a.classID
order by
b.DT,
a.classID
select
c.*,
d.今天用户总数
from
#t c,
(select
a.日期,
a.栏目代码,
sum(b.定阅人数) - sum(b.取消人数) as 今天用户总数
from
#t a,#t b
where
a.日期>=b.日期 and a.栏目代码 = b.栏目代码
group by
a.日期,a.栏目代码
order by
a.日期,a.栏目代码) d
where
c.日期 = d.日期 and c.栏目代码 = d.栏目代码
order by
c.日期,c.栏目代码
c.*,
d.今天用户总数
from
#t c,
(select
a.日期,
a.栏目代码,
sum(b.定阅人数) - sum(b.取消人数) as 今天用户总数
from
#t a,#t b
where
a.日期>=b.日期 and a.栏目代码 = b.栏目代码
group by
a.日期,a.栏目代码) d
where
c.日期 = d.日期 and c.栏目代码 = d.栏目代码
order by
c.日期,c.栏目代码
as
set nocount on
declare @date_begin char(8)--生成日期区间表
declare @i int
select @date_begin=min(date_begin)
,@i=datediff(day,@date_begin,max(date_end))+1
from 明细表
set rowcount @i
select id=identity(int,0,1),dat=cast(@date_begin as char(8))
into #t from syscolumns a,syscolumns b
update #t set dat=convert(char(8),dateadd(day,id,@date_begin),112)--查询处理
select 日期=a.dat
,栏目代码=a.classID
,定阅人数=isnull(b.DZusers,0)
,取消人数=isnull(c.QXusers,0)
,今天用户总数=0
into #t1 from(
select a.dat,b.classID
from #t,类别表
)a
left join(
select classID,startDT,DZusers=count(*)
from 明细表
group by classID,startDT
)b on a.classID=b.classID and a.dat=b.startDT
left join(
select classID,cancelDT,QXusers=count(*)
from 明细表
group by classID,cancelDT
)c on a.classID=c.classID
and a.dat=c.cancelDT
and b.startDT=c.cancelDT--生成"今天用户总数"
update a set Todayusers=(
select sum(DZusers-QXusers)
from 日志表
where classID=a.classID
and log_day<=a.log_day)
from #t1 a
select * from #t1
go
where b.f_classID=aa.栏目 and
b.startDT=aa.日期) as 订阅人数 ,
(select isnull(count(*),0) from 明细表 b
where b.f_classID=aa.栏目 and
b.cancelDT=aa.日期) as 取消人数 ,
(select isnull(count(*),0) from 明细表 b
where b.f_classID=aa.栏目 and
b.startDT>=aa.日期)
-
(select isnull(count(*),0) from 明细表 b
where b.f_classID=aa.栏目 and
b.cancelDT>=aa.日期) as 今天用户总数
from (select distinct startDT as 日期,classID as 栏目代码 from 明细表
union all
(select distinct cancelDT as 日期,classID as 栏目代码 from 明细表) aa