按周分組統計問題

[SassyBoy]

若表有三條記錄如下
Q1=1000(int) Q2=500(int)
delivery (datetime) Qty1 (int) Qty2(int)
2004-01-01 100 200
2004-01-02 200 300
...
2004-08-23 500 400

現想根據delivery 的日期按周分組統計
delivery Qty1 (int) Qty2(int)
2004-01-01 100 200
2004-01-02 200 300
小計: 300 500　(周統計結果)
剩余: 700(Q1-300) 0(Q2-500)
...

小計: xxx xxx (周統計結果)
剩余: Q1-xxx Q2-xxx

...
2004-08-23 500 400
小計: xxx xxx (周統計結果)
剩余: Q1-xxx Q2-xxx

請各位大蝦們幫幫忙...

[viptiger]

select delivery,sum(Qty1) from Youtable
group by delivery with rollup

[zjcxc]

--查询
select delivery,Qty1,Qty2
from(
select delivery=convert(varchar(10),delivery,120),Qty1,Qty2
,s1=delivery,s2=0
from tb
union all
select '第'+right(' '+datename(week,delivery),3)+' 周小计'
,Qty1=sum(Qty1),Qty2=sum(Qty1)
,s1=max(delivery),s2=1
from tb
group by '第'+right(' '+datename(week,delivery),3)+' 周小计'
union all
select '第'+right(' '+datename(week,b.delivery),3)+' 周小计'
,Qty1=isnull(sum(a.Q1),0)-isnull(
(select sum(Qty1) from tb where delivery<=max(b.delivery)),0)
,Qty2=isnull(sum(a.Q2),0)-isnull(
(select sum(Qty2) from tb where delivery<=max(b.delivery)),0)
,s1=max(delivery),s2=2
from ta a,tb b
group by '第'+right(' '+datename(week,delivery),3)+' 周小计'
)a order by s1,s2

[zjcxc]

--字段名写错了一点,改改;

select delivery,Qty1,Qty2
from(
select delivery=convert(varchar(10),delivery,120),Qty1,Qty2
,s1=delivery,s2=0
from tb
union all
select '第'+right(' '+datename(week,delivery),3)+' 周小计:'
,Qty1=sum(Qty1),Qty2=sum(Qty1)
,s1=max(delivery),s2=1
from tb
group by '第'+right(' '+datename(week,delivery),3)+' 周小计:'
union all
select '剩 余:'
,Qty1=isnull(sum(a.Q1),0)-isnull(
(select sum(Qty1) from tb where delivery<=max(b.delivery)),0)
,Qty2=isnull(sum(a.Q2),0)-isnull(
(select sum(Qty2) from tb where delivery<=max(b.delivery)),0)
,s1=max(delivery),s2=2
from ta a,tb b
group by datename(week,delivery)
)a order by s1,s2

[zjcxc]

--示例

--示例数据
create table ta(Q1 int,Q2 int)
insert ta select 1000,500

create table tb(delivery datetime,Qty1 int,Qty2 int)
insert tb select '2004-01-01',100,200
union all select '2004-01-02',200,300
union all select '2004-08-23',500,400
go

--查询
select delivery,Qty1,Qty2
from(
select delivery=convert(varchar(10),delivery,120),Qty1,Qty2
,s1=delivery,s2=0
from tb
union all
select '第'+right(' '+datename(week,delivery),3)+' 周小计:'
,Qty1=sum(Qty1),Qty2=sum(Qty1)
,s1=max(delivery),s2=1
from tb
group by '第'+right(' '+datename(week,delivery),3)+' 周小计:'
union all
select '剩 余:'
,Qty1=isnull(sum(a.Q1),0)-isnull(
(select sum(Qty1) from tb where delivery<=max(b.delivery)),0)
,Qty2=isnull(sum(a.Q2),0)-isnull(
(select sum(Qty2) from tb where delivery<=max(b.delivery)),0)
,s1=max(delivery),s2=2
from ta a,tb b
group by datename(week,delivery)
)a order by s1,s2
go

--删除测试
drop table ta,tb

/*--测试结果

delivery Qty1 Qty2
------------- ----------- -----------
2004-01-01 100 200
2004-01-02 200 300
第 1 周小计: 300 300
剩 余: 1700 500
2004-08-23 500 400
第 35 周小计: 500 500
剩 余: 200 -400

（所影响的行数为 7 行）
--*/

[了缘]

有老大在，up接分

[SassyBoy]

等我測試一下...

[SassyBoy]

老大誤會了我的意思
每周的剩余都是Q-當周的小計即(小計+剩余=Q)
delivery Qty1 Qty2
------------- ----------- -----------
2004-01-01 100 200
2004-01-02 200 300
第 1 周小计: 300 500
剩 余: 700 0
2004-08-23 500 400
第 35 周小计: 500 400
剩 余: 500 100

[zjcxc]

--那就更简单啦.

select delivery,Qty1,Qty2
from(
select delivery=convert(varchar(10),delivery,120),Qty1,Qty2
,s1=delivery,s2=0
from tb
union all
select '第'+right(' '+datename(week,delivery),3)+' 周小计:'
,Qty1=sum(Qty1),Qty2=sum(Qty2)
,s1=max(delivery),s2=1
from tb
group by '第'+right(' '+datename(week,delivery),3)+' 周小计:'
union all
select '剩 余:'
,Qty1=isnull(max(a.Q1),0)-isnull(sum(Qty1),0)
,Qty2=isnull(max(a.Q2),0)-isnull(sum(Qty2),0)
,s1=max(delivery),s2=2
from ta a,tb b
group by datename(week,delivery)
)a order by s1,s2

[zjcxc]

--示例

--示例数据
create table ta(Q1 int,Q2 int)
insert ta select 1000,500

create table tb(delivery datetime,Qty1 int,Qty2 int)
insert tb select '2004-01-01',100,200
union all select '2004-01-02',200,300
union all select '2004-08-23',500,400
go

--查询
select delivery,Qty1,Qty2
from(
select delivery=convert(varchar(10),delivery,120),Qty1,Qty2
,s1=delivery,s2=0
from tb
union all
select '第'+right(' '+datename(week,delivery),3)+' 周小计:'
,Qty1=sum(Qty1),Qty2=sum(Qty2)
,s1=max(delivery),s2=1
from tb
group by '第'+right(' '+datename(week,delivery),3)+' 周小计:'
union all
select '剩 余:'
,Qty1=isnull(max(a.Q1),0)-isnull(sum(Qty1),0)
,Qty2=isnull(max(a.Q2),0)-isnull(sum(Qty2),0)
,s1=max(delivery),s2=2
from ta a,tb b
group by datename(week,delivery)
)a order by s1,s2
go

--删除测试
drop table ta,tb

/*--测试结果

delivery Qty1 Qty2
----------------- ----------- -----------
2004-01-01 100 200
2004-01-02 200 300
第 1 周小计: 300 500
剩 余: 700 0
2004-08-23 500 400
第 35 周小计: 500 400
剩 余: 500 100

（所影响的行数为 7 行）
--*/

[SassyBoy]

如果用rollup那又怎麼做呢?

[zjcxc]

这里的处理不适合于用rollup

[SassyBoy]

為什麼?