需求描述:
Table1: job1
job1_id | name | status | other_column |
1 | file1 | process | … |
2 | file2 | failed | … |
3 | file3 | success | … |
Table2: job2
job2_id | name | status | other_column |
1 | fileA | new | … |
2 | fileB | success | … |
3 | fileC | failed | … |
4 | fileD | new | ... |
统计这两张表总共的成功(success)数,失败(failed)数,正在解析(process,包含new)数,结果返回一张表,像这样:
status | amount |
process | 3 |
success | 2 |
failed | 2 |
Oracle SQL写法:
Step1:
分别统计两张表各自的process, success, failed的count,只需要group by一下再count即可:
select status, count(*) amount from job1 group by status; select status, count(*) amount from job2 group by status;
结果:
status | amount |
process | 1 |
success | 1 |
failed | 1 |
(上图表job1,下图表job2)
status | amount |
new | 2 |
success | 1 |
failed | 1 |
Step2:
将两张表融合起来,group by一下再对每个分组分别sum一下就可以计算出各自status的总数,但会遇到一个问题,new怎么add到process中?
> 用oracle的decode函数,即在查上面的count的时候,就把new的value给改成process(当然只是视图的改变,不会改变表原来的值)。这里只需要对表job2稍作修改:
select decode(status, 'new', 'process', 'success', 'success', 'failed', 'failed') status, count(*) amount from job2 group by status;
> decode的用法:decode(字段名,原来的值,改变后的值,原来的值,改变后的值……),请注意,不需要改变的也要写上去,如上面的'success', 'success',否则都会变成空。
正确结果:
status | amount |
process | 2 |
success | 1 |
failed | 1 |
如果只写了decode(status, 'new', 'process') status,就会变成:
status | amount |
process | 2 |
1 | |
1 |
Step3:
至此,可以融合了。用union all关键字将上面分别count后的表融合成一张表,再group by和sum。
> 这里不要用union,如果缺省all,则合并结果会去掉相同的项,因为我们两张表分别有success 1和success 1,重复了,若被去掉其中一项,就没有效果了,所以应该使用union all,而且注意union左右两边的表不要加括号,否则会报语法错误。另外这里用到with关键字将上面count后的表作为临时表方便总表调用。全部代码如下:
with table_sum as( select status, count(*) amount from job1 group by status union all select decode(status, 'new', 'process', 'success', 'success', 'failed', 'failed') status, count(*) amount from job2 group by status ) select status, sum(amount) amount from table_sum group by status;
结果:
status | amount |
process | 3 |
success | 2 |
failed | 2 |
Over :).