kevin171710885 发表于 2016-07-26 16:59

union all语句简化问题

求大神支招，下面的多个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;

seesea2517 发表于 2016-07-27 09:55

多个不同表的查询没什么想法，我也是这样做，坐看高手怎么处理的学一下。

lyhabc 发表于 2016-07-29 15:52

肯定不是mysql，mysql没有with tb as语法的
页: [1]
查看完整版本: union all语句简化问题