sql case 嵌套示例

SELECT
b.requestCarType,
b.fhtype,
b.kfName,
b.cpName,
b.shengName,
b.shiName,
sum( ljwjjcount ) AS ljwjjcount
FROM
(select
c.kfName,
c.cpName,
c.shengName,
c.shiName,
c.khName,
c.RECADDR,
c.ywname,
c.fhType,
c.requestCarType,
sum( c.ljwjjcount ) AS ljwjjcount
from(
SELECT
a.kfName,
a.cpName,
a.shengName,
a.shiName,
a.khName,
a.RECADDR,
a.ywname,
CASE
( a.fhtype )
WHEN ( ‘A’ ) THEN
‘A’ ELSE ‘B’
END AS fhType,
CASE
WHEN
a.kuandu > ‘2200’ THEN
CASE
( a.vehtype )
WHEN ‘0’ THEN
‘Wide’
WHEN ‘1’ THEN
‘HighWide’
WHEN ‘2’ THEN
‘LowWide’
END ELSE
CASE
( a.vehtype )
WHEN ‘0’ THEN
‘NoRequirement’
WHEN ‘1’ THEN
‘High’
WHEN ‘2’ THEN
‘Low’
END
END AS requestCarType,
a.ljwjjcount
FROM
DB.VIEWDISPLISTNODATA a
WHERE
a.kfName = ‘中厚板成品库’
AND a.planType = ‘B’
AND a.billstate = ‘2’
AND a.state = ‘2’
AND a.fbizdate >= ‘20230214’
AND ( a.dispSource = ‘F’ OR ( a.dispSource = ‘Y’ AND a.XIANNAME != ‘***’ ) )
)c
GROUP BY
c.kfName,
c.cpName,
c.shengName,
c.shiName,
c.khName,
c.RECADDR,
c.ywname,
c.fhtype,
c.requestCarType
) b
WHERE
b.ljwjjcount > 30
GROUP BY
b.fhtype,
b.kfName,
b.cpName,
b.shengName,
b.shiName,
b.requestCarType