设有一个顾客表,存有如下数据:
顧客NO | 性別 | 婚姻 |
100000004 | 女性 | 不明 |
100000006 | 女性 | 不明 |
100000007 | 女性 | 不明 |
1000000008 | 女性 | 未婚 |
1000000045 | 女性 | 未婚 |
1000000047 | 女性 | 未婚 |
1000000052 | 男性 | 未婚 |
100000017 | 男性 | 不明 |
1000000013 | 男性 | 既婚 |
1000000014 | 女性 | 既婚 |
1000000015 | 女性 | 既婚 |
1000000017 | 男性 | 既婚 |
现在需要统计不同性别和婚姻下的顾客人数。使用group by 后得到如下结果:
性別 | 婚姻 | 顧客数 |
女性 | 既婚 | 2 |
女性 | 不明 | 3 |
女性 | 未婚 | 3 |
男性 | 既婚 | 2 |
男性 | 不明 | 1 |
男性 | 未婚 | 1 |
如果能得到交叉表格报表就更为理想,即如下形式:
性別 | 未婚 | 既婚 | 不明 |
女性 | 3 | 2 | 3 |
男性 | 1 | 2 | 1 |
方案1:sql server 2005新增了pivot运算符,其语法形式为
Select select_list
From table_source pivot
( aggregate_function (value_column)
for pivot_column
in (<column_list>)
) as table_alias
其中table_source是进行数据透视的来源数据,pivot_column是pivot运算符的数据透视字段,pivot_column字段中有哪些数据值要变成输出结果中的字段,必须在column_list中指定。上例中pivot_column是"婚姻"字段,column_list指定为未婚,既婚和不明。即
select 性別,[未婚],[既婚],[不明]
from 顧客表
pivot
(
count(顧客NO)
for 婚姻in ([未婚],[既婚],[不明])
) as a
注意:要使用pivot运算符,数据库的兼容级别必须设为90.
方案2.很简单,如下
select 性別,count(case 婚姻 when '未婚' then 顧客NO end) as 未婚,
count(case 婚姻 when '既婚' then 顧客NO end) as 既婚,
count(case 婚姻 when '不明' then 顧客NO end) as 不明
from 顧客表
group by 性別
即灵活使用case。