试试这样
select T1.年,T1.月,T1.地区,T1.物品码,T2.月合计发货数,T3.年度累计发货数
from 表 T1
left join (select 月,sum(发货数) as 月合计发货数 from 表 group by 月) T2
on T1.年=T2.年 and T1.月=T2.月 and T1.地区=T2.地区 and T1.物品码=T2.物品码
left join (select 年,sum(发货数) as 年度累计发货数 from 表 group by 年) T3
on T1.年=T3.年 and T1.地区=T3.地区 and T1.物品码=T3.物品码
select T1.年,T1.月,T1.地区,T1.物品码,T2.月合计发货数,T3.年度累计发货数
from 表 T1
left join (select 月,sum(发货数) as 月合计发货数 from 表 group by 月) T2
on T1.年=T2.年 and T1.月=T2.月 and T1.地区=T2.地区 and T1.物品码=T2.物品码
left join (select 年,sum(发货数) as 年度累计发货数 from 表 group by 年) T3
on T1.年=T3.年 and T1.地区=T3.地区 and T1.物品码=T3.物品码
试试这样,修改一下declare @M int
set @M=3
select T1.年,T1.月,T1.地区,T1.物品码,T2.月合计发货数,T3.年度累计发货数
from 表 T1
left join (select 月,sum(发货数) as 月合计发货数 from 表 group by 月) T2
on T1.年=T2.年 and T1.月=T2.月 and T1.地区=T2.地区 and T1.物品码=T2.物品码
left join (select 年,sum(发货数) as 年度累计发货数 from 表 where 月<=@M group by 年) T3
on T1.年=T3.年 and T1.地区=T3.地区 and T1.物品码=T3.物品码
我写的是这样:
SELECT 年, 月,地区,物品号, SUM(发货数) AS 月合计发货数,
(SELECT SUM(发货数) FROM 表 WHERE 地区=a.地区 AND 物品号= a.物品号 AND 年 = a.年 AND 月 <= a.月)
AS 月年度累计发货数
FROM 表 a
GROUP 年, 月,地区,物品号但是对“有的物品以前月有发货,本月也许没有”这种情况就不对了
begin
create table tb
(
[年] int,
[月] int check([月]<32 and [月]>0),
[物品码] varchar(20),
[发货数] int default 0,
[地区] varchar(20)
)
end
truncate table tbinsert tb([年],[月],[物品码],[发货数],[地区])
select 2004,1,'AAAA',12,'上海'
union all
select 2004,2,'BBBB',49,'北京'
union all
select 2004,2,'BBBB',49,'北京'
union all
select 2005,2,'CCCC',49,'北京'
union all
select 2004,3,'BBBB',467,'北京'select * from tbdeclare @month int
set @month=2
select distinct
A.[年],A.[月],A.[物品码],A.[地区],
(select sum([发货数]) from tb where [月]=A.[月] and [地区]=A.[地区] and [年]=A.[年] and [物品码]=A.[物品码]) as '月发货数',
(select sum([发货数]) from tb where [月]<=@month and [地区]=A.[地区] and [年]=A.[年] and [物品码]=A.[物品码]) as '月年度累计发货数'
from tb as A drop table tb