sql分类汇总

Question

如表1:类型     规格          代码       等级      数量       
a         50G          0A0001     特级        2
a         50G          0A0001     优等        4         
a         100G         0A0001     特级        3
a         100G         0A0001     特级        3
...希望得出以下结果:类型    规格       代码           特级     优等
a        50G        0A0001        2         4
         小计                     2         4   
a        100G       0A0001        6         0
         小计                     6         0
合计                              8         4

Answer 1

select 类型,规格,代码,
sum(case 等级 when '特级' then 数量 end) as 数量,
sum(case 等级 when '优等' then 数量 end) as 优等
from 表1
group by 类型,规格

Answer 2

select 类型,规格,
[特级] =sum(case 等级 when '特级' then 数量 else 0 end),
[优等] =sum(case 等级 when '优等' then 数量 else 0 end)
from tb
group by 类型,规格类型         规格         特级          优等          
---------- ---------- ----------- ----------- 
a          100G       6           0
a          50G        2           4（所影响的行数为 2 行）

Answer 3

create table tb(类型 varchar(10), 规格 varchar(10), 代码 varchar(10), 等级 varchar(10), 数量 int) 
insert into tb values('a' , '50G' , '0A0001' , '特级' , 2 )
insert into tb values('a' , '50G' , '0A0001' , '优等' , 4 ) 
insert into tb values('a' , '100G', '0A0001' , '特级' , 3 )
insert into tb values('a' , '100G', '0A0001' , '特级' , 3 )
goselect * from 
(
select 类型,规格,代码,
       sum(case 等级 when '特级' then 数量 else 0 end) 特级,
       sum(case 等级 when '优等' then 数量 else 0 end) 优等
from tb
group by 类型,规格,代码
union all
select 类型,规格,代码='小计',
       sum(case 等级 when '特级' then 数量 else 0 end) 特级,
       sum(case 等级 when '优等' then 数量 else 0 end) 优等
from tb
group by 类型 ,规格
union all
select '合计' 类型,规格='',代码='',
       sum(case 等级 when '特级' then 数量 else 0 end) 特级,
       sum(case 等级 when '优等' then 数量 else 0 end) 优等
from tb
) t
order by case when 类型 = '合计' then 2 else 1 end , 类型 , 规格 , case when 代码 = '小计' then 2 else 1 enddrop table tb/*类型         规格         代码         特级          优等          
---------- ---------- ---------- ----------- ----------- 
a          100G       0A0001     6           0
a          100G       小计         6           0
a          50G        0A0001     2           4
a          50G        小计         2           4
合计                               8           4（所影响的行数为 5 行）
*/

Answer 4

select 类型=isnull(类型,''),
规格=(case when grouping(类型)=1 then '合计' when grouping(代码)=1 then '小计'  else 规格 end),
代码=isnull(代码,''),
[特级] =sum(case 等级 when '特级' then 数量 else 0 end),
[优等] =sum(case 等级 when '优等' then 数量 else 0 end)
from tb
group by 类型,规格,代码
WITH ROLLUP
having grouping(规格)=0 or grouping(类型)=1类型         规格         代码         特级          优等          
---------- ---------- ---------- ----------- ----------- 
a          100G       0A0001     6           0
a          小计                    6           0
a          50G        0A0001     2           4
a          小计                    2           4
           合计                    8           4（所影响的行数为 5 行）

Answer 5

create table tb(类型 varchar(10),规格 varchar(10),代码 varchar(10),等级 varchar(10),数量 int) insert into tb select 
'a','50G','0A0001','特级',2  union all select
'a','50G','0A0001','优等',4  union all select
'a','100G','0A0001','特级',3  union all select
'a','100G','0A0001','特级',3 
select 类型=(case when grouping(类型)=1 then '合计' else 类型 end),
规格=(case when grouping(代码)=1 then '小计'  else 规格 end),
代码=isnull(代码,''),
[特级] =sum(case 等级 when '特级' then 数量 else 0 end),
[优等] =sum(case 等级 when '优等' then 数量 else 0 end)
from tb
group by 类型,规格,代码
with rollup
having grouping(规格)=0 or grouping(类型)=1/*类型         规格         代码         特级          优等          
---------- ---------- ---------- ----------- ----------- 
a          100G       0A0001     6           0
a          小计                    6           0
a          50G        0A0001     2           4
a          小计                    2           4
合计         小计                    8           4（所影响的行数为 5 行）
*/--drop table tb

Answer 6

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-12-27 19:47:20
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([类型] varchar(1),[规格] varchar(4),[代码] varchar(6),[等级] varchar(4),[数量] int)
insert [tb]
select 'a','50G','0A0001','特级',2 union all
select 'a','50G','0A0001','优等',4 union all
select 'a','100G','0A0001','特级',3 union all
select 'a','100G','0A0001','特级',3
--------------开始查询--------------------------
;with f as
(
select
 类型,规格,代码,
 sum(case 等级 when '特级' then 数量 else 0 end) as 特级,
 sum(case 等级 when '优等' then 数量 else 0 end) as 优等
from
 tb
group by
 类型,规格,代码
)
select 
  case when grouping(类型)=1 then '合计' else 类型 end as 类型,
  case when grouping(规格)=1 and grouping(类型)=0 then '小计' else 规格 end 规格, 
  isnull(代码,'') as 代码,sum(特级) as 特级,sum(优等) as 优等
from 
  f
group by 
  类型,规格,代码
with rollup
having
  grouping(规格)=1 or grouping(代码)=0 
----------------结果----------------------------
/* 类型   规格   代码     特级          优等
---- ---- ------ ----------- -----------
a    100G 0A0001 6           0
a    50G  0A0001 2           4
a    小计          8           4
合计   NULL        8           4(4 行受影响)*/

Answer 7

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-12-27 19:47:20
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([类型] varchar(1),[规格] varchar(4),[代码] varchar(6),[等级] varchar(4),[数量] int)
insert [tb]
select 'a','50G','0A0001','特级',2 union all
select 'a','50G','0A0001','优等',4 union all
select 'a','100G','0A0001','特级',3 union all
select 'a','100G','0A0001','特级',3
--------------开始查询--------------------------
;with f as
(
select
 类型,规格,代码,
 sum(case 等级 when '特级' then 数量 else 0 end) as 特级,
 sum(case 等级 when '优等' then 数量 else 0 end) as 优等
from
 tb
group by
 类型,规格,代码
)
select 
  case when grouping(类型)=1 then '合计' else 类型 end as 类型,
  case when grouping(代码)=1 and grouping(类型)=0 then '小计' else 规格 end 规格, 
  isnull(代码,'') as 代码,sum(特级) as 特级,sum(优等) as 优等
from 
  f
group by 
  类型,规格,代码
with rollup
having
  grouping(规格)=0 or grouping(类型)=1
----------------结果----------------------------
/* 类型   规格   代码     特级          优等
---- ---- ------ ----------- -----------
a    100G 0A0001 6           0
a    小计          6           0
a    50G  0A0001 2           4
a    小计          2           4
合计   NULL        8           4(5 行受影响)*/