- 论坛徽章:
- 0
|
求大神支招,下面的多个union all语句如何简化?
里面查询的是从201205到201607的日志表数据
with tb as(
select * from AOP_STAT_INVOKED_LOG_201205 t1 where t1.app_id=-1 and t1.ability_id<>-1 union all
select * from AOP_STAT_INVOKED_LOG_201206 t2 where t2.app_id=-1 and t2.ability_id<>-1 union all
select * from AOP_STAT_INVOKED_LOG_201207 t3 where t3.app_id=-1 and t3.ability_id<>-1 union all
select * from AOP_STAT_INVOKED_LOG_201208 t1 where t1.app_id=-1 and t1.ability_id<>-1 union all
select * from AOP_STAT_INVOKED_LOG_201209 t2 where t2.app_id=-1 and t2.ability_id<>-1 union all
select * from AOP_STAT_INVOKED_LOG_201210 t3 where t3.app_id=-1 and t3.ability_id<>-1 union all
select * from AOP_STAT_INVOKED_LOG_201211 t1 where t1.app_id=-1 and t1.ability_id<>-1 union all
select * from AOP_STAT_INVOKED_LOG_201212 t2 where t2.app_id=-1 and t2.ability_id<>-1 union all
...
select * from AOP_STAT_INVOKED_LOG_201607 t3 where t3.app_id=-1 and t3.ability_id<>-1 )
select tb.ability_id,tc.ability_name,sum(tb.invoked_nums) from tb,aop_ability_baseinfo tc where tb.ability_id=tc.ability_id group by tb.ability_id,tc.ability_name order by 3 desc; |
|