SQL 行转列 group by union 用法

摘要：

SQL 行转列

group by 用法
union 用法

行转列一般是和group by结合使用，在select 中使用case aa when 'value1' then bb else cc end来进行行转列操作。
group by 一般用于针对某个字段进行分类。特别是需要针对某字段进行分列和汇总统计的时候，group by是必用的。

union 使用时，必须保证2个要union的表必须所有的字段都一致，并且在子句中不能使用group by.

代码：

select * from (
select sessions.build_id as build_id, sessions.build_cset_hypervisor as build_cset_hypervisor, sessions.build_commit_userspace as build_commit_userspace, 
sessions.build_rpm_url as build_rpm_url, sessions.ses_id as ses_id, sessions.ses_date as ses_date, sessions.ses_xvs_log as ses_xvs_log, 
sessions.plf_hostname as plf_hostname, sessions.plf_name as plf_name, 
sum(case function.res_benchmark_id when '5010' then function.res_perf_data else 0 end) as total,
sum(case function.res_benchmark_id when '5011' then function.res_perf_data else 0 end) as pass,
sum(case function.res_benchmark_id when '5012' then function.res_perf_data else 0 end) as fail
from 
(select build_id, build_cset_hypervisor, build_commit_userspace, 
build_rpm_url, ses_id, ses_date, ses_xvs_log, plf_hostname, plf_name 
from session_tab join build_tab join platform_tab 
where ses_plat_id=plf_id 
and build_project='xen' 
and build_arch='ia32e' 
and ses_vm_type='xen_hvm' 
and ses_suite='function' 
and ses_scenario='nightly' 
and build_id=ses_build_id order by build_cset_hypervisor desc, ses_date desc) as sessions join function_tab as function
where function.res_session_num=sessions.ses_id group by function.res_session_num 
union
select build_id, build_cset_hypervisor, build_commit_userspace, 
build_rpm_url, ses_id, ses_date, ses_xvs_log, plf_hostname, plf_name, total,
pass,
fail from (select build_id, build_cset_hypervisor, build_commit_userspace, 
build_rpm_url, ses_id, ses_date, ses_xvs_log, plf_hostname, plf_name, 0 as total,
0 as pass,
0 as fail
from session_tab join build_tab join platform_tab 
where ses_plat_id=plf_id 
and build_project='xen' 
and build_arch='ia32e' 
and ses_vm_type='xen_hvm' 
and ses_suite='function' 
and ses_scenario='nightly' 
and build_id=ses_build_id
order by build_cset_hypervisor desc, ses_date desc) as session 
where plf_hostname ='xen-build' or plf_hostname ='hp-build' 
or plf_hostname ='losvmm-build' or plf_hostname ='losvmm-builde') as test
order by build_cset_hypervisor desc, ses_date desc