oracle 请问一下,这一条拼接的sql怎么写成一条SQL

毕加索5481 发布于 2016/08/16 11:44
阅读 327
收藏 0
select *
          from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2
         where t1.accbaseid = t2.accbaseid
           and t1.state = '02'
           and t2.state = '03'
           and t1.relievetime is not null
           and t1.punishtimelimit is null
           and t2.happenorgcode = '02'
           and t1.relievetime <= to_char(sysdate, 'yyyy-mm-dd')
        union all
        select *
          from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2
         where t1.accbaseid = t2.accbaseid
           and t1.state = '02'
           and t2.state = '03'
           and t1.relievetime is null
           and t1.punishtimelimit is not null
           and t2.happenorgcode = '02'
           and to_char(add_months(sysdate,
                                  decode(t1.punishtimelimit,
                                         '半年',
                                         -6,
                                         '一年',
                                         -12,
                                         '二年',
                                         -24,
                                         99)),
                       'yyyy-mm-dd') >= t1.punishtime
        union all
        select *
          from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2
         where t1.accbaseid = t2.accbaseid
           and t1.state = '02'
           and t2.state = '03'
           and t1.relievetime is null
           and t1.punishtimelimit is null
           and t1.insidepunishtype = '01'
           and t2.happenorgcode ='02'
           and to_char(add_months(sysdate,
                                  case
                                    when t1.punishway = '01' then
                                     -6
                                    when t1.punishway in ('02', '03', '04') then
                                     -12
                                    when t1.punishway = '05' then
                                     -24
                                    when t1.punishway = '06' then
                                     -24
                                    else
                                     99
                                  end),

                       'yyyy-mm-dd') >= t1.punishtime


各位大神,请问这条拼接SQL咋样才能写成一条SQL

加载中
0
la_lala
la_lala
用case when 应该可以
0
IdleMan
IdleMan
你这本来就是一条SQL
毕加索5481
这条sql是拼接成的一条SQL,我的意思是不需要拼接,就是一条完整的sql
0
skhuhu
skhuhu
select *
          from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2
         where t1.accbaseid = t2.accbaseid
           and t1.state = '02'
           and t2.state = '03'
           and t1.relievetime is null
           and t1.punishtimelimit is null
           and t1.insidepunishtype = '01'
           and t2.happenorgcode ='02'
           and to_char(add_months(sysdate,
                                  case
                                    when t1.punishway = '01' then
                                     -6
                                    when t1.punishway in ('02', '03', '04') then
                                     -12
                                    when t1.punishway = '05' then
                                     -24
                                    when t1.punishway = '06' then
                                     -24
                                    else
                                     99
                                  end),
                       'yyyy-mm-dd') >= t1.punishtime
					and to_char(add_months(sysdate,
                                  decode(t1.punishtimelimit,
                                         '半年',
                                         -6,
                                         '一年',
                                         -12,
                                         '二年',
                                         -24,
                                         99)),
                       'yyyy-mm-dd') >= t1.punishtime
          and t1.relievetime <= to_char(sysdate, 'yyyy-mm-dd')



毕加索5481
这个语句求出来没有数据!本来有3条数据的!
0
skhuhu
skhuhu
select *
          from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2
         where t1.accbaseid = t2.accbaseid
           and t1.state = '02'
           and t2.state = '03'
           and t1.relievetime is null
           and t1.punishtimelimit is null
           and t1.insidepunishtype = '01'
           and t2.happenorgcode ='02'
           and (to_char(add_months(sysdate,
                                  case
                                    when t1.punishway = '01' then
                                     -6
                                    when t1.punishway in ('02', '03', '04') then
                                     -12
                                    when t1.punishway = '05' then
                                     -24
                                    when t1.punishway = '06' then
                                     -24
                                    else
                                     99
                                  end),
                       'yyyy-mm-dd') >= t1.punishtime

                    or to_char(add_months(sysdate,
                                  decode(t1.punishtimelimit,
                                         '半年',
                                         -6,
                                         '一年',
                                         -12,
                                         '二年',
                                         -24,
                                         99)),
                       'yyyy-mm-dd') >= t1.punishtime
          or t1.relievetime <= to_char(sysdate, 'yyyy-mm-dd') )



0
IdleMan
IdleMan
select *
  from T21_EAP_ACC_PUNISH_INFO t1, t21_eap_acc_base_info t2
 where t1.accbaseid = t2.accbaseid
   and t1.state = '02'
   and t2.state = '03'
   and ((t1.punishtimelimit is null and t2.happenorgcode = '02' and
       t1.relievetime <= to_char(sysdate, 'yyyy-mm-dd')) or
       (t1.relievetime is null and t1.punishtimelimit is not null and
       t2.happenorgcode = '02' and
       to_char(add_months(sysdate,
                            decode(t1.punishtimelimit,
                                   '半年',
                                   -6,
                                   '一年',
                                   -12,
                                   '二年',
                                   -24,
                                   99)),
                 'yyyy-mm-dd') >= t1.punishtime) or
       (t1.relievetime is null and t1.punishtimelimit is null and
       t1.insidepunishtype = '01' and t2.happenorgcode = '02' and
       to_char(add_months(sysdate,
                            case
                              when t1.punishway = '01' then
                               -6
                              when t1.punishway in ('02', '03', '04') then
                               -12
                              when t1.punishway = '05' then
                               -24
                              when t1.punishway = '06' then
                               -24
                              else
                               99
                            end),
                 'yyyy-mm-dd') >= t1.punishtime))



0
IdleMan
IdleMan
union all相比or性能更低,每一个子sql会访问一次相关表/索引,而or只需要访问一次
返回顶部
顶部