单位 类型 名称 所属
北京 新 茶馆 首都
北京 重 红楼 首都
天津 租 西游 地方
北京 新 西游 首都
河北 重 林家 地方
河南 新 雷雨 地方
天津 租 红楼 地方
天津 新 水浒 地方以上的表的数据还有很多,我想得到一个汇总的结果是:
合计 新 重 租
总计(首都+地方) 8 4 2 2
1、首都 3 2 1 0
北京 3 2 1 0
2、地方 5 2 1 2
天津 3 1 0 2
河北 1 0 1 0
河南 1 1 0 0这样的SQL怎么写 就一条语句出来 谢谢 各位高手!!!
北京 新 茶馆 首都
北京 重 红楼 首都
天津 租 西游 地方
北京 新 西游 首都
河北 重 林家 地方
河南 新 雷雨 地方
天津 租 红楼 地方
天津 新 水浒 地方以上的表的数据还有很多,我想得到一个汇总的结果是:
合计 新 重 租
总计(首都+地方) 8 4 2 2
1、首都 3 2 1 0
北京 3 2 1 0
2、地方 5 2 1 2
天津 3 1 0 2
河北 1 0 1 0
河南 1 1 0 0这样的SQL怎么写 就一条语句出来 谢谢 各位高手!!!
isnull(单位,所属) as 单位,
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from
表
group by
所属,单位 with rollup
having
grouping(所属)=0
insert into @t select '北京','新','茶馆','首都'
insert into @t select '北京','重','红楼','首都'
insert into @t select '天津','租','西游','地方'
insert into @t select '北京','新','西游','首都'
insert into @t select '河北','重','林家','地方'
insert into @t select '河南','新','雷雨','地方'
insert into @t select '天津','租','红楼','地方'
insert into @t select '天津','新','水浒','地方'
select
isnull(单位,所属) as 单位,
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from
@t
group by
所属,单位 with rollup
having
grouping(所属)=0/*
单位 合计 新 重 租
---- ----------- ----------- ----------- -----------
河北 1 0 1 0
河南 1 1 0 0
天津 3 1 0 2
地方 5 2 1 2
北京 3 2 1 0
首都 3 2 1 0
*/
insert into @t select '北京','新','茶馆','首都'
insert into @t select '北京','重','红楼','首都'
insert into @t select '天津','租','西游','地方'
insert into @t select '北京','新','西游','首都'
insert into @t select '河北','重','林家','地方'
insert into @t select '河南','新','雷雨','地方'
insert into @t select '天津','租','红楼','地方'
insert into @t select '天津','新','水浒','地方'
select
(case when 所属 is null then '总计' else isnull(单位,所属) end) as 单位,
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from
@t
group by
所属,单位 with rollup /*
单位 合计 新 重 租
---- ----------- ----------- ----------- -----------
河北 1 0 1 0
河南 1 1 0 0
天津 3 1 0 2
地方 5 2 1 2
北京 3 2 1 0
首都 3 2 1 0
总计 8 4 2 2
*/
isnull(单位) as 单位,
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from
表
group by
所属,单位 with rollup
having
grouping(所属)=0
)
union all
(
(select
isnull(所属) as 单位,
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from
表
group by
所属,单位 with rollup )
)
from (
select '总计' as 项目,
sum(1) as 合计,
sum(case when '类型'='新' then 1 else 0 end) as 新,
sum(case when '类型'='重' then 1 else 0 end) as 重,
sum(case when '类型'='租' then 1 else 0 end) as 租,
min(单位) as 单位,
1 as ordertype
from tablename
union all
select 所属 as 项目,
sum(1) as 合计,
sum(case when '类型'='新' then 1 else 0 end) as 新,
sum(case when '类型'='重' then 1 else 0 end) as 重,
sum(case when '类型'='租' then 1 else 0 end) as 租,
min(单位) as 单位,
2 as ordertype
from tablename
group by 所属
union all
select 单位 as 项目,
sum(1) as 合计,
sum(case when '类型'='新' then 1 else 0 end) as 新,
sum(case when '类型'='重' then 1 else 0 end) as 重,
sum(case when '类型'='租' then 1 else 0 end) as 租,
单位,
3 as ordertype
from tablename
group by 单位
) as t
order by 单位,ordertype
insert into @t select N'北京',N'新',N'茶馆',N'首都'
insert into @t select N'北京',N'重',N'红楼',N'首都'
insert into @t select N'天津',N'租',N'西游',N'地方'
insert into @t select N'北京',N'新',N'西游',N'首都'
insert into @t select N'河北',N'重',N'林家',N'地方'
insert into @t select N'河南',N'新',N'雷雨',N'地方'
insert into @t select N'天津',N'租',N'红楼',N'地方'
insert into @t select N'天津',N'新',N'水浒',N'地方'select 项目,合计,新,重,租
from (
select N'总计' as 项目,
sum(1) as 合计,
sum(case when 类型=N'新' then 1 else 0 end) as 新,
sum(case when 类型=N'重' then 1 else 0 end) as 重,
sum(case when 类型=N'租' then 1 else 0 end) as 租,
min(所属) as 所属,
1 as ordertype
from @T
union all
select 所属 as 项目,
sum(1) as 合计,
sum(case when 类型=N'新' then 1 else 0 end) as 新,
sum(case when 类型=N'重' then 1 else 0 end) as 重,
sum(case when 类型=N'租' then 1 else 0 end) as 租,
所属,
2 as ordertype
from @T
group by 所属
union all
select 单位 as 项目,
sum(1) as 合计,
sum(case when 类型=N'新' then 1 else 0 end) as 新,
sum(case when 类型=N'重' then 1 else 0 end) as 重,
sum(case when 类型=N'租' then 1 else 0 end) as 租,
所属,
3 as ordertype
from @T
group by 单位,所属
) as t
order by 所属,ordertype,项目
--结果
项目 合计 新 重 租
---- ----------- ----------- ----------- -----------
总计 8 4 2 2
地方 5 2 1 2
天津 3 1 0 2
河北 1 0 1 0
河南 1 1 0 0
首都 3 2 1 0
北京 3 2 1 0(所影响的行数为 7 行)
insert into @t select '北京','新','茶馆','首都'
insert into @t select '北京','重','红楼','首都'
insert into @t select '天津','租','西游','地方'
insert into @t select '北京','新','西游','首都'
insert into @t select '河北','重','林家','地方'
insert into @t select '河南','新','雷雨','地方'
insert into @t select '天津','租','红楼','地方'
insert into @t select '天津','新','水浒','地方'
select 单位 = '总计' , sum(合计) as 合计 , sum(新) as 新 , sum(重) as 重 , sum(租) as 租 from
(
select
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t group by 所属
) t1union allselect 单位 = '首都' , sum(合计) as 合计 , sum(新) as 新 , sum(重) as 重 , sum(租) as 租 from
(
select
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t where 所属 = '首都' group by 所属
) t2union allselect 单位 = '北京' , sum(合计) as 合计 , sum(新) as 新 , sum(重) as 重 , sum(租) as 租 from
(
select
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t where 所属 = '首都' group by 所属
) t3union allselect 单位 = '地方' , sum(合计) as 合计 , sum(新) as 新 , sum(重) as 重 , sum(租) as 租 from
(
select
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t where 所属 = '地方' group by 所属
) t2union allselect
单位,
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t where 所属 <> '首都' group by 所属,单位
单位 合计 新 重 租
---- ----------- ----------- ----------- -----------
总计 8 4 2 2
首都 3 2 1 0
北京 3 2 1 0
地方 5 2 1 2
河北 1 0 1 0
河南 1 1 0 0
天津 3 1 0 2(所影响的行数为 7 行)
insert into @t select '北京','新','茶馆','首都'
insert into @t select '北京','重','红楼','首都'
insert into @t select '天津','租','西游','地方'
insert into @t select '北京','新','西游','首都'
insert into @t select '河北','重','林家','地方'
insert into @t select '河南','新','雷雨','地方'
insert into @t select '天津','租','红楼','地方'
insert into @t select '天津','新','水浒','地方'
select 单位 = '总计' , sum(合计) as 合计 , sum(新) as 新 , sum(重) as 重 , sum(租) as 租 from
(
select
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t group by 所属
) t1union allselect 单位 = '1、首都' , sum(合计) as 合计 , sum(新) as 新 , sum(重) as 重 , sum(租) as 租 from
(
select
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t where 所属 = '首都' group by 所属
) t2union allselect 单位 = '北京' , sum(合计) as 合计 , sum(新) as 新 , sum(重) as 重 , sum(租) as 租 from
(
select
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t where 所属 = '首都' group by 所属
) t3union allselect 单位 = '2、地方' , sum(合计) as 合计 , sum(新) as 新 , sum(重) as 重 , sum(租) as 租 from
(
select
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t where 所属 = '地方' group by 所属
) t2union allselect
单位,
count(*) as 合计,
sum(case 类型 when '新' then 1 else 0 end) as 新,
sum(case 类型 when '重' then 1 else 0 end) as 重,
sum(case 类型 when '租' then 1 else 0 end) as 租
from @t where 所属 <> '首都' group by 所属,单位 单位 合计 新 重 租
------- ----------- ----------- ----------- -----------
总计 8 4 2 2
1、首都 3 2 1 0
北京 3 2 1 0
2、地方 5 2 1 2
河北 1 0 1 0
河南 1 1 0 0
天津 3 1 0 2(所影响的行数为 7 行)
还有需要一个首都和地方的分组
-------------------------------
?
看不到吗
[单位/内容] = ISNULL(单位, '总计'),
[合计] = SUM(1),
[新出] = SUM(CASE 类型 WHEN '新出' THEN 1 ELSE 0 END),
[重印] = SUM(CASE 类型 WHEN '重印' THEN 1 ELSE 0 END),
[租型] = sum(case 类型 when '租型' then 1 else 0 end)
FROM BOOK
GROUP BY 单位 WITH ROLLUP
[单位/内容] = ISNULL(单位, '总计'),
[合计] = SUM(1),
[新出] = SUM(CASE 类型 WHEN '新出' THEN 1 ELSE 0 END),
[重印] = SUM(CASE 类型 WHEN '重印' THEN 1 ELSE 0 END),
[租型] = sum(case 类型 when '租型' then 1 else 0 end)
FROM 表名
GROUP BY 单位 WITH ROLLUP
[单位/内容] = ISNULL(单位, '总计'),
[合计] = SUM(1),
[新出] = SUM(CASE 类型 WHEN '新出' THEN 1 ELSE 0 END),
[重印] = SUM(CASE 类型 WHEN '重印' THEN 1 ELSE 0 END),
[租型] = sum(case 类型 when '租型' then 1 else 0 end)
FROM 表名
GROUP BY 单位 WITH ROLLUP在这个基础上我不知道怎么写了, 怎么效率高怎么实现,我想让总计显示在第一列,并且有一个首都和地方的分组。
除了用union(all),没其他办法.
[单位/内容] = ISNULL(单位, '总计'),
[合计] = SUM(1),
[新出] = SUM(CASE 类型 WHEN '新出' THEN 1 ELSE 0 END),
[重印] = SUM(CASE 类型 WHEN '重印' THEN 1 ELSE 0 END),
[租型] = sum(case 类型 when '租型' then 1 else 0 end)
FROM 表名
GROUP BY 单位 WITH ROLLUP在这个基础上我不知道怎么写了, 怎么效率高怎么实现,我想让总计显示在第一列,并且有一个首都和地方的分组。
-------------------------------------------------------------------------------------修改一下程序中对结果输出的顺序处理,从结果集的尾部向结果集首记录反序输出即可。
insert into @t select '北京','新','茶馆','首都'
insert into @t select '北京','重','红楼','首都'
insert into @t select '天津','租','西游','地方'
insert into @t select '北京','新','西游','首都'
insert into @t select '河北','重','林家','地方'
insert into @t select '河南','新','雷雨','地方'
insert into @t select '天津','租','红楼','地方'
insert into @t select '天津','新','水浒','地方'select 单位=case when (grouping(单位)=1 and grouping(所属) =0) then 所属 when (grouping(单位)=1 and grouping(所属) =1) then '总计(首都+地方) 'else 单位 end,合计=count(名称),
新=sum(case 类型 when '新' then 1 else 0 end),
重=sum(case 类型 when '重' then 1 else 0 end),
租=sum(case 类型 when '租' then 1 else 0 end)
from @t
group by 所属,单位 with rollup
order by grouping(所属)desc,所属 desc,grouping(单位)desc单位 合计 新 重 租
---------------- ----------- ----------- ----------- -----------
总计(首都+地方) 8 4 2 2
首都 3 2 1 0
北京 3 2 1 0
地方 5 2 1 2
河北 1 0 1 0
河南 1 1 0 0
天津 3 1 0 2(所影响的行数为 7 行)