百分求分类汇总统计SQL语句。研究了半天，搞不定啊

[denghan]

数据库MSSQL2000
明细表
----------------------------------------
userID 用户ID c(15)
classID 栏目代码 c(3)
startDT 定阅时间 (时间格式:yyyymmdd 的字符串形式)
cancelDT 取消时间 (时间格式:yyyymmdd 的字符串形式)

类别表
---------------------------------------
classID 类别编号
className 类别名称

用户表
-----------------------------
userID 用户ID
userName 用户名

要实现的效果:
日期 栏目代码 定阅人数 取消人数 今天用户总数
------------------------------------------------------
20041230 101 100 25 75
20041230 102 99 31 68
20041230 103 85 0 0
..............
如果明细表中某栏目今天没有发生定制或取消记录，则应显示今天该栏目定制取消数为0
今天用户数 = 今天(相应栏目的的定阅用户数-取消用户数) + 昨天(相应栏目的'今天用户总数')

请各位大侠帮忙看看，百分感谢~~~！

[Wanter1]

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

[denghan]

先试试

[denghan]

这个问题以前发过贴问了，但解决不是很完美。如果明细表中某栏目今天没有发生定制或取消记录，就不没有该栏目今天的记录，应该显示今天该栏目定制取消数为0

请高手们帮忙看看。thx~!

[子陌红尘]

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

[zjcxc]

统计的时间段怎么确定,是做为参数来处理吗?

[子陌红尘]

修正：

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

[imhere_l]

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;

[子陌红尘]

哦，理解错了楼主的意思

[long0104]

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.栏目代码

[denghan]

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.栏目代码

[zjcxc]

--创建查询处理的存储过程(查询全部数据)

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

[passionke]

up

[constantine]

up

[Fibona]

up

[prcgolf]

up