用case when实现decode()函数的功能

原来的oracle

select t.ttid,o.organname,
t.targettrain,
t.creator,
DateText(t.TTEnd) as DateRange, //这是自定义的函数
decode(tg.finishedMode, 1, '已达标', '未达标') as passmode,
decode(t.AllowView, 1, '允许', '不允许') as AllowView
from tbTargetTrain t inner join tborgan o on o.organid=t.organid
left join Tbttgrade tg on t.ttid = tg.ttid
and tg.userid =1
where (sysdate between t.TTBegin and t.TTEnd)
and t.TargetType = '0'
and exists (select 1 from vttuser01 where userid=1)
and tg.finishedMode ='0' ;

order by tg.finishedMode desc Nulls last,t.datecreated desc; // Nulls last 只支持oracle待修改

---------------------------------------------------
翻译成mysql后

select t.ttid,o.organname,
t.targettrain,
t.creator,
t.TTEnd as DateRange,
case tg.finishedMode when 1 then '已达标' else '未达标' end as passmode,

case t.AllowView when 1 then '允许' else '不允许' end as AllowView

from tbTargetTrain t inner join tborgan o on o.organid=t.organid
left join Tbttgrade tg on t.ttid = tg.ttid

and tg.userid =1
where (sysdate() between t.TTBegin and t.TTEnd)
and t.TargetType = '0'
# and exists (select 1 from vttuser01 where userid=1) 因为有视图先搁置再议
and tg.finishedMode ='0' ;

-------------------------------------------
decode(t.AllowView, 1, '允许', '不允许')
decode()的第一个参数是 别名是t表的一个字段，第二个是字段的值，如果是值是1，字段值换成'允许'，如果不是1，就换成'不允许'。