汇总统计的sql

[hnahzx]

表（行业名称、年、产值），现要查询某一时间段的值，并按名称分类汇总，如2001-2003年的数据：
A 2001 200
A 2002 300
A 2003 400
合计 900
B 2001 300
B 2002 400
B 2003 100
合计 800
总合计 1700

请问如何写sql？我用的C#开发asp.net程序，sql server数据库。谢谢大家

[hnahzx]

大家帮帮忙，谢谢啦！

[$扫地僧$]

DECLARE @t TABLE(Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'A','2001',200
INSERT @t SELECT 'A','2002',300
INSERT @t SELECT 'A','2003',400
INSERT @t SELECT 'B','2001',300
INSERT @t SELECT 'B','2002',400
INSERT @t SELECT 'B','2003',100

--统计
SELECT Item,Color,Quantity
FROM(
--明细
SELECT Item,Color,Quantity=SUM(Quantity)
,s1=0,s2=Item,s3=0
FROM @t
GROUP BY Item,Color
UNION ALL
--各Item合计
SELECT '','合计',Quantity=SUM(Quantity)
,s1=0,s2=Item,s3=1
FROM @t
GROUP BY Item
UNION ALL
--总计
SELECT '总合计','',Quantity=SUM(Quantity)
,s1=1,s2='',s3=1
FROM @t
)a ORDER BY s1,s2,s3

[qiqingshizhe]

create table tc(item varchar(10),year datetime,quantity int)
insert tc select 'a','2001-1-1',200
union all select 'a','2001-1-3',300
union all select 'a','2002-2-3',300
union all select 'a','2003-3-3',400
union all select 'b','2001-2-3',400
union all select 'b','2003-3-6',300
union all select 'b','2002-4-3',300
union all select 'b','2004-11-3',100
union all select 'b','2001-2-4',399
declare @dt_begin varchar(10),@dt_end varchar(10)
select @dt_begin='2001',@dt_end='2003'
select item,year,quantity
from(
select item,[year]=year(year),quantity=sum(quantity),s1=0,s2=item,s3=0
from tc
where year(year) between @dt_begin and @dt_end
group by item,year(year)
union all
select '合计','',quantity=sum(quantity),s1=0,s2=item,s3=1
from tc
where year(year) between @dt_begin and @dt_end
group by item
union all
select '总计','',quantity=sum(quantity),s1=1,s2='',s3=1
from tc
where year(year) between @dt_begin and @dt_end
)a order by s1,s2,s3
可以满足要求！就是合计，总计那里中间不知道怎么会出现0

[yongwin]

DECLARE @t TABLE(Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'A','2001',200
INSERT @t SELECT 'A','2002',300
INSERT @t SELECT 'A','2003',400
INSERT @t SELECT 'B','2001',300
INSERT @t SELECT 'B','2002',400
INSERT @t SELECT 'B','2003',100
select Item=(case when Item is null then '总合计'
when color is null then'合计' else Item end)
,color,sum(Quantity)
from @t
group by Item,color with rollup

[Jpp]

这样需要计算小计、总计的查询不应该在SQL中实现，应该充分利用客户端工具实现。

[hnahzx]

谢谢刚刚两位大哥，基本满足要求，但是还要解决：
1。中间用year的话，就出现0了，是不是字段类型为数字型了
2。某一个行业的明细的要根据年排序。

此外，我开始忘了说明了，我的行业名称是从另外一张表（行业编号、行业名称）得来的，
而开始的那张表应当是表（行业编号、年、产值）。两个表要做个连接，不知如何做？

[qiqingshizhe]

drop table tc
create table tc(item varchar(10),year datetime,quantity int)
insert tc select 'a','2001-1-1',200
union all select 'a','2001-1-3',300
union all select 'a','2002-2-3',300
union all select 'a','2003-3-3',400
union all select 'b','2001-2-3',400
union all select 'b','2003-3-6',300
union all select 'b','2002-4-3',300
union all select 'b','2004-11-3',100
union all select 'b','2001-2-4',399
declare @dt_begin varchar(10),@dt_end varchar(10)
select @dt_begin='2001',@dt_end='2003'
select
item=case
when grouping([year])=0 then item
when grouping([year])=1 and grouping(a.item)<>1 then '合计'
when grouping(a.item)=1 then '总合计'
end,
[year]=case
when grouping([year])=1 then ''
else cast([year] as varchar)
end,
quantity=sum(a.quantity)
from
(select item=item,[year]=year(year),quantity=quantity from tc)a
where [year] between @dt_begin and @dt_end
group by a.item,a.[year] with rollup
这种方法是符合要求的！

[qiqingshizhe]

用with rollup运算符产生的结果会自动按升序排列！

[vovo2000]

在scmail81(freedom) 的基础上：
inner join 表（行业编号、行业名称）

[qiqingshizhe]

create table ta(id int,item varchar(10))
insert ta select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'
create table tc(id int ,year datetime,quantity int)
insert tc select 1,'2001-1-1',200
union all select 1,'2001-1-3',300
union all select 1,'2002-2-3',300
union all select 1,'2003-3-3',400
union all select 1,'2001-2-3',400
union all select 2,'2003-3-6',300
union all select 2,'2002-4-3',300
union all select 3,'2004-11-3',100
union all select 3,'2001-2-4',399
union all select 3,'2003-2-2',222
declare @dt_begin varchar(10),@dt_end varchar(10)
select @dt_begin='2001',@dt_end='2003'
select item=case
when grouping([year])=0 then item
when grouping([year])=1 and grouping(item)<>1 then '合计'
when grouping(item)=1 then '总合计'
end,
[year]=case
when grouping([year])=1 then ''
else cast([year] as varchar)
end,
quantity=sum(quantity)
from ta a right join
(select [id]=id,[year]=year(year),quantity=quantity from tc)b
on a.id=b.[id] and [year] between @dt_begin and @dt_end
group by item,[year] with rollup
如果你要显示全部部门的总计，就应该把right join 改为left Join
这里只显示在tc表中有记录的部门的报表

[qiqingshizhe]

上面出了点错！在子查询select [id]=id,[year]=year(year),quantity=quantity from tc
后面加上过滤条件 where year(year) between @dt_begin and @dt_end

[ReViSion]

--try


create table #1 (name varchar(10),year varchar(10),value int)
insert into #1 select
'A' , 2001, 200
union all select
'A' , 2002, 300
union all select
'B' , 2001, 300
union all select
'A' , 2003, 400
union all select
'B' , 2002, 400
union all select
'B' , 2003 ,100

select 行业名称=case when grouping(year)=1 and grouping(name)=0 then '合计'
else case when grouping(year)=1 and grouping(name)=1 then '总计'
else name end
end,
年=year,产值=sum(value)
from #1
where year between '2001' and '2003'
group by name,year with rollup