数据库是sql server 2005表结构是:
col11 col12 col13 col21 col22 col23
A A B C C D
B B D C A A
C C D A B D
C D B B C D
D B B A A C
A C D B C C
B D A A D C
C C B B D D说明这个表各列的取值只能是A ,B ,C 或者 D要求查询统计的结果为: col11 col12 col13 col21 col22 col23
2 2 1 3 2 1 // 统计上表中各列 A 出现的次数
2 2 4 3 1 0 // 统计上表中各列 B 出现的次数
3 3 0 2 3 3 // 统计上表中各列 C 出现的次数
1 2 3 0 2 4 // 统计上表中各列 D 出现的次数
col11 col12 col13 col21 col22 col23
A A B C C D
B B D C A A
C C D A B D
C D B B C D
D B B A A C
A C D B C C
B D A A D C
C C B B D D说明这个表各列的取值只能是A ,B ,C 或者 D要求查询统计的结果为: col11 col12 col13 col21 col22 col23
2 2 1 3 2 1 // 统计上表中各列 A 出现的次数
2 2 4 3 1 0 // 统计上表中各列 B 出现的次数
3 3 0 2 3 3 // 统计上表中各列 C 出现的次数
1 2 3 0 2 4 // 统计上表中各列 D 出现的次数
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col11] varchar(1),[col12] varchar(1),[col13] varchar(1),[col21] varchar(1),[col22] varchar(1),[col23] varchar(1))
insert [tb]
select 'A','A','B','C','C','D' union all
select 'B','B','D','C','A','A' union all
select 'C','C','D','A','B','D' union all
select 'C','D','B','B','C','D' union all
select 'D','B','B','A','A','C' union all
select 'A','C','D','B','C','C' union all
select 'B','D','A','A','D','C' union all
select 'C','C','B','B','D','D'
---查询---
select
col11=sum(case when col11='A' then 1 else 0 end),
col12=sum(case when col12='A' then 1 else 0 end),
col13=sum(case when col13='A' then 1 else 0 end),
col21=sum(case when col21='A' then 1 else 0 end),
col22=sum(case when col22='A' then 1 else 0 end),
col23=sum(case when col23='A' then 1 else 0 end)
from tb
union all
select
col11=sum(case when col11='B' then 1 else 0 end),
col12=sum(case when col12='B' then 1 else 0 end),
col13=sum(case when col13='B' then 1 else 0 end),
col21=sum(case when col21='B' then 1 else 0 end),
col22=sum(case when col22='B' then 1 else 0 end),
col23=sum(case when col23='B' then 1 else 0 end)
from tb
union all
select
col11=sum(case when col11='C' then 1 else 0 end),
col12=sum(case when col12='C' then 1 else 0 end),
col13=sum(case when col13='C' then 1 else 0 end),
col21=sum(case when col21='C' then 1 else 0 end),
col22=sum(case when col22='C' then 1 else 0 end),
col23=sum(case when col23='C' then 1 else 0 end)
from tb
union all
select
col11=sum(case when col11='D' then 1 else 0 end),
col12=sum(case when col12='D' then 1 else 0 end),
col13=sum(case when col13='D' then 1 else 0 end),
col21=sum(case when col21='D' then 1 else 0 end),
col22=sum(case when col22='D' then 1 else 0 end),
col23=sum(case when col23='D' then 1 else 0 end)
from tb
---结果---
col11 col12 col13 col21 col22 col23
----------- ----------- ----------- ----------- ----------- -----------
2 1 1 3 2 1
2 2 4 3 1 0
3 3 0 2 3 3
1 2 3 0 2 4(所影响的行数为 4 行)
sum(case [col12] when 'A' then 1 else 0 end) as [col12] ,
sum(case [col13] when 'A' then 1 else 0 end) as [col13],
sum(case [col21] when 'A' then 1 else 0 end) as [col21],
sum(case [col22] when 'A' then 1 else 0 end) as [col22],
sum(case [col23] when 'A' then 1 else 0 end) as [col23]
from [tb]
union all
select sum(case [col11] when 'B' then 1 else 0 end) as [col11] ,
sum(case [col12] when 'B' then 1 else 0 end) as [col12] ,
sum(case [col13] when 'B' then 1 else 0 end) as [col13],
sum(case [col21] when 'B' then 1 else 0 end) as [col21],
sum(case [col22] when 'B' then 1 else 0 end) as [col22],
sum(case [col23] when 'B' then 1 else 0 end) as [col23]
from [tb]
union all
select sum(case [col11] when 'C' then 1 else 0 end) as [col11] ,
sum(case [col12] when 'C' then 1 else 0 end) as [col12] ,
sum(case [col13] when 'C' then 1 else 0 end) as [col13],
sum(case [col21] when 'C' then 1 else 0 end) as [col21],
sum(case [col22] when 'C' then 1 else 0 end) as [col22],
sum(case [col23] when 'C' then 1 else 0 end) as [col23]
from [tb]
union all
select sum(case [col11] when 'D' then 1 else 0 end) as [col11] ,
sum(case [col12] when 'D' then 1 else 0 end) as [col12] ,
sum(case [col13] when 'D' then 1 else 0 end) as [col13],
sum(case [col21] when 'D' then 1 else 0 end) as [col21],
sum(case [col22] when 'D' then 1 else 0 end) as [col22],
sum(case [col23] when 'D' then 1 else 0 end) as [col23]
from [tb]
go
create table [tb]([col11] varchar(1),[col12] varchar(1),[col13] varchar(1),[col21] varchar(1),[col22] varchar(1),[col23] varchar(1))
insert [tb]
select 'A','A','B','C','C','D' union all
select 'B','B','D','C','A','A' union all
select 'C','C','D','A','B','D' union all
select 'C','D','B','B','C','D' union all
select 'D','B','B','A','A','C' union all
select 'A','C','D','B','C','C' union all
select 'B','D','A','A','D','C' union all
select 'C','C','B','B','D','D'
---查询---
select
col11=sum(case when col11='A' then 1 else 0 end),
col12=sum(case when col12='A' then 1 else 0 end),
col13=sum(case when col13='A' then 1 else 0 end),
col21=sum(case when col21='A' then 1 else 0 end),
col22=sum(case when col22='A' then 1 else 0 end),
col23=sum(case when col23='A' then 1 else 0 end)
from tb
union all
select
col11=sum(case when col11='B' then 1 else 0 end),
col12=sum(case when col12='B' then 1 else 0 end),
col13=sum(case when col13='B' then 1 else 0 end),
col21=sum(case when col21='B' then 1 else 0 end),
col22=sum(case when col22='B' then 1 else 0 end),
col23=sum(case when col23='B' then 1 else 0 end)
from tb
union all
select
col11=sum(case when col11='C' then 1 else 0 end),
col12=sum(case when col12='C' then 1 else 0 end),
col13=sum(case when col13='C' then 1 else 0 end),
col21=sum(case when col21='C' then 1 else 0 end),
col22=sum(case when col22='C' then 1 else 0 end),
col23=sum(case when col23='C' then 1 else 0 end)
from tb
union all
select
col11=sum(case when col11='D' then 1 else 0 end),
col12=sum(case when col12='D' then 1 else 0 end),
col13=sum(case when col13='D' then 1 else 0 end),
col21=sum(case when col21='D' then 1 else 0 end),
col22=sum(case when col22='D' then 1 else 0 end),
col23=sum(case when col23='D' then 1 else 0 end)
from tb
--> 测试时间:2009-12-11 13:34:02
--> 测试菜鸟:l8r
--> 我的淘宝:《戒色坊》http://shop36766744.taobao.com/if object_id('[TB]') is not null drop table [TB]
create table [TB]([col11] varchar(1),[col12] varchar(1),[col13] varchar(1),[col21] varchar(1),[col22] varchar(1),[col23] varchar(1))
insert [TB]
select 'A','A','B','C','C','D' union all
select 'B','B','D','C','A','A' union all
select 'C','C','D','A','B','D' union all
select 'C','D','B','B','C','D' union all
select 'D','B','B','A','A','C' union all
select 'A','C','D','B','C','C' union all
select 'B','D','A','A','D','C' union all
select 'C','C','B','B','D','D'select value,
col11=sum(case when flag=1 then 1 else 0 end ),
col12=sum(case when flag=2 then 1 else 0 end ),
col13=sum(case when flag=3 then 1 else 0 end ),
col21=sum(case when flag=4 then 1 else 0 end ),
col22=sum(case when flag=5 then 1 else 0 end ),
col23=sum(case when flag=6 then 1 else 0 end )
from (
select value=col11,flag=1 from [TB] union all
select value=col12,flag=2 from [TB] union all
select value=col13,flag=3 from [TB] union all
select value=col21,flag=4 from [TB] union all
select value=col22,flag=5 from [TB] union all
select value=col23,flag=6 from [TB] )g
group by value/*
value col11 col12 col13 col21 col22 col23
----- ----------- ----------- ----------- ----------- ----------- -----------
A 2 1 1 3 2 1
B 2 2 4 3 1 0
C 3 3 0 2 3 3
D 1 2 3 0 2 4(所影响的行数为 4 行)*/drop table [TB]
那关联(select 'a' as col union all select 'b' union all select 'c' union all select 'd') b