嵌套查询

  select t1.active_id,
         t1.state_2,
         t1.state_1,
         t2.active_name,
       to_char(t2.start_time,'yyyy-mm-dd hh24:mi')
    from (select t.active_id,
                 sum(decode(t.ticket_state, '2', 1, 0)) state_2,
                 count(*) state_1
            from T_PROMOTION_TICKET_INFO t
           group by t.active_id) t1,
         t_cjh_active_info t2
   where t1.active_id = t2.active_id
     and t2.org_num like '%1%' order by t2.start_time desc; 

此条sql用到多表查询,嵌套查询,聚合函数sum,以及decode

sum:计算总数

decode:相当于条件赋值

例如decode(t.ticket_state, '2', 1, 0),意思为:当ticket_state为"2"时,该值置为1,否则置为0

谢谢