汇总统计的sql

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

谢谢刚刚两位大哥，基本满足要求，但是还要解决：
1。中间用year的话，就出现0了，是不是字段类型为数字型了
2。某一个行业的明细的要根据年排序。此外，我开始忘了说明了，我的行业名称是从另外一张表（行业编号、行业名称）得来的，
而开始的那张表应当是表（行业编号、年、产值）。两个表要做个连接，不知如何做？

Answer 5

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
这种方法是符合要求的！

Answer 6

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

Answer 7

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

Answer 8

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表中有记录的部门的报表

Answer 9

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

Answer 10

--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 ,100select 行业名称=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