本来写成是SQL语句的优化,其实是将一个SQL语句的某些写法简化了,比如我之前的写法
select t.*,
(case when t.version like 'new' then g1.name else g.name end) as pname
from hd t
left join (
select distinct uid,did,(case when t.col15 like '%version%' then ‘new’ else ‘old’ end) as version
from hd t11
where t11.vt>=current_date-1 and t11.vt<current_date and t11.position like 'tmd%' and uid!=''
) t1 on t.did=t1.did
left join m_o o on o.bid=t1.uid
left join m_u u on u.oid=t1.uid
--old
left join m_i i on i.oid=t.icode
left join m_i_g g on g.oid=i.g_id
--new
left join m_i_g g1 on g1.oid=t.icode
left join m_c_c c on c.oid=t.cid
where t.vt>=current_date-1 and t.vt<current_date and t.position like 'tmd%'
order by o.bid, t.vt
这个是简化之后的SQL代码
select t.*,
(case when t.col15 like '%version%' then g1.name else g.name end) as pname
from hd t
left join (
select distinct uid,did,(case when t.)
from hd t
where t.vt>=current_date-1 and t.vt<current_date and t.position like 'tmd%' and uid!=''
) t1 on t.did=t1.did
left join m_o o on o.bid=t1.uid
left join m_u u on u.oid=t1.uid
--old
left join m_i i on i.oid=t.icode
left join m_i_g g on g.oid=i.g_id
--new
left join m_i_g g1 on g1.oid=t.icode
left join m_c_c c on c.oid=t.cid
where t.vt>=current_date-1 and t.vt<current_date and t.position like 'tmd%'
order by o.bid, t.vt
主要针对子查询进行简化,把可以放父查询里面的语句放到父查询中,没有放到子查询中,使得代码更加简洁,明朗