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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
行转列是将长表转为宽表的过程,可以使用SQL语句中的SUM IF函数来实现。[1] SUM IF函数可以将符合某个条件的值进行求和,并将结果作为新的列。行转列的思路是将原表中的行记录变为多行,将列字段变为单列。实现的方法是首先根据某个条件进行筛选,然后使用SUM IF函数对符合条件的值进行求和,将结果作为新的列添加到宽表中。 列转行是行转列的逆过程,即将宽表转为长表。 在SQL语句中,可以使用UNION操作符将多个查询结果合并为一个结果集。对于列转行的实现,可以针对每个列字段提取一张衍生表,然后使用UNION将所有衍生表合并到一起。需要注意的是字段的对齐,确保每个衍生表中的字段一一对应。 另外,还可以使用GROUP_CONCAT函数来实现列转行。该函数可以将同一组的行合并为一个字段,并返回合并后的结果。分组是根据GROUP BY指定的列进行的,通过指定要返回的列,可以将多个列字段合并为一个字段,实现列转行的效果。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* [SQL 行转列,列转行](https://blog.csdn.net/qq_52825422/article/details/125612013)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *3* [sql查询:行转列和 列转行](https://blog.csdn.net/weixin_40482816/article/details/109395069)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值