用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，就换成'不允许'。