一个多列汇总查询问题，求大神
本帖最后由 pctonc 于 2012-12-14 16:48:53 编辑 表usserinf
userid user_Identity user_MemberOrParty user_Organize
1000 0 1 1
1001 1 2 0
1002 2 1 2
1003 1 0 0
1004 1 1 2
1005 2 2 1
备注
user_Identity=0 表示默认
user_Identity=1 表示一般
user_Identity=2 表示普通
user_MemberOrParty=0 表示简单
user_MemberOrParty=1 表示复杂
user_MemberOrParty=2 表示超难
user_Organize=0 表示 甲
user_Organize=1 表示 乙
user_Organize=2 表示 丙
如何查询实现结果如下
结果一：
默认 一般 普通 简单 复杂 超难 甲 乙 丙
1 3 2 1 3 2 2 2 2
结果二：
默认 1
一般 3
普通 2
简单 1
复杂 3
超难 2
甲 2
乙 2
丙 2
[解决办法]
--usserinf
if object_id('usserinf','u') is not null
drop table usserinf
go
create table usserinf
(
userid char(4),
user_Identity smallint,
user_MemberOrParty smallint,
user_Organize smallint
)
go
insert into usserinf values
('1000',0,1,1),
('1001',1,2,0),
('1002',2,1,2),
('1003',1,0,0),
('1004',1,1,2),
('1005',2,2,1)
go
--SQL
--结果一
select *from (
select '默认' 中文, count(默认) Tag from(
select
case when user_Identity=0 then user_Identity else null end 默认
from usserinf) A
where 默认 is not null union all
select '一般', count(一般) Tag from(
select
case when user_Identity=1 then user_Identity else null end 一般
from usserinf) A
where 一般 is not null union all
select '普通', count(普通) Tag from(
select
case when user_Identity=2 then user_Identity else null end 普通
from usserinf) A
where 普通 is not null union all
select '简单', count(简单) Tag from(
select
case when user_MemberOrParty=0 then user_Identity else null end 简单
from usserinf) A
where 简单 is not null union all
select '复杂', count(复杂) Tag from(
select
case when user_MemberOrParty=1 then user_Identity else null end 复杂
from usserinf) A
where 复杂 is not null union all
select '超难', count(超难) Tag from(
select
case when user_MemberOrParty=2 then user_Identity else null end 超难
from usserinf) A
where 超难 is not null union all
select '甲', count(甲) Tag from(
select
case when user_Organize=0 then user_Identity else null end 甲
from usserinf) A
where 甲 is not null union all
select '乙', count(乙) Tag from(
select
case when user_Organize=1 then user_Identity else null end 乙
from usserinf) A
where 乙 is not null union all
select '丙', count(丙) Tag from(
select
case when user_Organize=2 then user_Identity else null end 丙
from usserinf) A
where 丙 is not null ) B
pivot(max(Tag) for 中文 in (默认,一般,普通,简单,复杂,超难,甲,乙,丙)) a
--结果二
select '默认' 中文, count(默认) Tag from(
select
case when user_Identity=0 then user_Identity else null end 默认
from usserinf) A
where 默认 is not null union all
select '一般', count(一般) Tag from(
select
case when user_Identity=1 then user_Identity else null end 一般
from usserinf) A
where 一般 is not null union all
select '普通', count(普通) Tag from(
select
case when user_Identity=2 then user_Identity else null end 普通
from usserinf) A
where 普通 is not null union all
select '简单', count(简单) Tag from(
select
case when user_MemberOrParty=0 then user_Identity else null end 简单
from usserinf) A
where 简单 is not null union all
select '复杂', count(复杂) Tag from(
select
case when user_MemberOrParty=1 then user_Identity else null end 复杂
from usserinf) A
where 复杂 is not null union all
select '超难', count(超难) Tag from(
select
case when user_MemberOrParty=2 then user_Identity else null end 超难
from usserinf) A
where 超难 is not null union all
select '甲', count(甲) Tag from(
select
case when user_Organize=0 then user_Identity else null end 甲
from usserinf) A
where 甲 is not null union all
select '乙', count(乙) Tag from(
select
case when user_Organize=1 then user_Identity else null end 乙
from usserinf) A
where 乙 is not null union all
select '丙', count(丙) Tag from(
select
case when user_Organize=2 then user_Identity else null end 丙
from usserinf) A
where 丙 is not null
if exists(select * from sysobjects where name='stu')
drop table stu
go
create table stu
( userid int,
user_Identity int,
user_MemberOrParty int,
user_Organize int
)
insert into stu(userid,user_Identity,user_MemberOrParty,user_Organize)
values(1000,0,1,1)
insert into stu(userid,user_Identity,user_MemberOrParty,user_Organize)
values(1001,1,2,0)
insert into stu(userid,user_Identity,user_MemberOrParty,user_Organize)
values(1002,2,1,2)
insert into stu(userid,user_Identity,user_MemberOrParty,user_Organize)
values(1003,1,0,0)
insert into stu(userid,user_Identity,user_MemberOrParty,user_Organize)
values(1004,1,1,2)insert into stu(userid,user_Identity,user_MemberOrParty,user_Organize)
values(1005,2,2,1)
select * from stu
select count(case user_Identity when 0 then 1 else null end)as 默认,
count(case user_Identity when 1 then 1 else null end) as 一般,
count(case user_Identity when 2 then 1 else null end)as 普通 ,
count(case user_MemberOrParty when 0 then 1 else null end) as 简单,
count(case user_MemberOrParty when 1 then 1 else null end) as 复杂 ,
count(case user_MemberOrParty when 2 then 1 else null end)as 超难,
count(case user_Organize when 0 then 1 else null end) as 甲,
count(case user_Organize when 1 then 1 else null end) as 乙,
count(case user_Organize when 2 then 1 else null end)as 丙
from stu
--------------------------------
---------------------------
----------------------------
select * from stu
----------------------------
select count(case user_Identity when 0 then 1 else null end)as 默认,'默认'as 表列
from stu union all
select count(case user_Identity when 1 then 1 else null end) as 一般,'一般'as 表列
from stu union all
select count(case user_Identity when 2 then 1 else null end)as 普通 ,'普通' as 表列
from stu union all
select count(case user_MemberOrParty when 0 then 1 else null end) as 简单,'简单'as 表列
from stu union all
select count(case user_MemberOrParty when 0 then 1 else null end) as 简单,'简单'as 表列
from stu union all
select count(case user_MemberOrParty when 1 then 1 else null end) as 复杂 ,'复杂'as 表列
from stu union all
select count(case user_MemberOrParty when 2 then 1 else null end)as 超难,'超难'as 表列
from stu union all
select count(case user_Organize when 0 then 1 else null end) as 甲,'甲'as 表列
from stu union all
select count(case user_Organize when 1 then 1 else null end) as 乙,'乙'as 表列
from stu union all
select count(case user_Organize when 2 then 1 else null end)as 丙,'丙'as 表列
from stu
