需求描述:
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 :).