SQL查询字段赋值的case用法

NC群小米

需求：select name from bd_corp where name='ab'
显示结果为“ab”，需求是name='ab'条件下显示为“cd”
我的sql是
select substr(name||'cd',-2,2 )name from bd_corp where name='ab'
怎解感觉都很麻烦，请大家讨论一下，是不是有更好的办法

itpub dingjun123

直接写'cd'
select 'cd'  name from  bd_corp where name='ab'

小米的真正sql是：

(SELECT 
substr( stdname||'天津市检察院二分院',-9,9) AS "单位",

A.LZANH AS "立案号",

A.XYR AS "嫌疑人",

A.RZRQ AS "入帐日期",

B.STDNAME AS "案由",

SUM(CASE WHEN A.KZT='2BA5B68A700000016B2132F465E1A6DD' THEN A.KYKWJE ELSE 0 END) AS "款待入账",
SUM(CASE WHEN A.KZT='2BA5B6DCE00000018F22CF8435E689DB' THEN A.KYKWJE ELSE 0 END) AS "款已入账",
SUM(CASE WHEN A.KZT='2BAEFB0AC00000017360C2C5E8A3A2DC' THEN A.KYKWJE ELSE 0 END) AS "款待出帐",
SUM(CASE WHEN A.KZT='2BA5B70190000001DC6E219CAE87CA19' THEN A.KYKWJE ELSE 0 END) AS "款已出帐",
A.BAR_H AS "承办人"
FROM TJ_KY_AJXX AS A 
LEFT JOIN TJ_JC_AY AS B ON B.RECID=A.XZAY
LEFT JOIN MD_ORG AS C ON C.RECID=A.ORGUNIT 
WHERE A.ZZGS='38741B1B00000001C70C1CCC3A93856F' AND C.PARENTS contains @dw  and A.RZRQ>=@kssq and A.RZRQ<@jssj and A.AJLY = @ajlx 
and C.stdname='二分院民行处'

GROUP BY C.STDNAME,A.LZANH,A.XYR,A.RZRQ,B.STDNAME,A.BAR_H
ORDER BY substr(C.STDNAME,1,3),A.LZANH)

union
(SELECT 
C.STDNAME AS "单位",

A.LZANH AS "立案号",

A.XYR AS "嫌疑人",

A.RZRQ AS "入帐日期",

B.STDNAME AS "案由",

SUM(CASE WHEN A.KZT='2BA5B68A700000016B2132F465E1A6DD' THEN A.KYKWJE ELSE 0 END) AS "款待入账",
SUM(CASE WHEN A.KZT='2BA5B6DCE00000018F22CF8435E689DB' THEN A.KYKWJE ELSE 0 END) AS "款已入账",
SUM(CASE WHEN A.KZT='2BAEFB0AC00000017360C2C5E8A3A2DC' THEN A.KYKWJE ELSE 0 END) AS "款待出帐",
SUM(CASE WHEN A.KZT='2BA5B70190000001DC6E219CAE87CA19' THEN A.KYKWJE ELSE 0 END) AS "款已出帐",
A.BAR_H AS "承办人"
FROM TJ_KY_AJXX AS A 
LEFT JOIN TJ_JC_AY AS B ON B.RECID=A.XZAY
LEFT JOIN MD_ORG AS C ON C.RECID=A.ORGUNIT 
WHERE A.ZZGS='38741B1B00000001C70C1CCC3A93856F' AND C.PARENTS contains @dw  and A.RZRQ>=@kssq and A.RZRQ<@jssj and A.AJLY = @ajlx 
and C.stdname<>'二分院民行处'

GROUP BY C.STDNAME,A.LZANH,A.XYR,A.RZRQ,B.STDNAME,A.BAR_H
ORDER BY substr(C.STDNAME,1,3),A.LZANH)

我本要求她union一下，总感觉复杂，中午的时候折腾了一下，使用case的话不存在case when 1 then else很多顾虑，可以直接else name

如下

select unitcode,
(case when unitcode='010201' then 'sumsen' else unitname end)as unitname
from bd_corp

 case提醒：case的字段必须是同一类，否则可以to_char等统一