1、Order By 时通过case when 进行判断后排序
需求:当F_EnvirState=0时排第二;F_EnvirState=1时排第一。
SELECT
F_ENVIRGRANTDATE
F_ENVIRSTATE
FROM T_Envir_EnvironmentalLogo
ORDER BY
CASE
WHEN F_EnvirState = 0 THEN --未发放
2
WHEN F_EnvirState = 1 THEN --已发放
1
WHEN F_EnvirState = 2 THEN --已注销
3
WHEN F_EnvirState IS NULL THEN
4
END ,
F_ENVIRGRANTDATE desc
如果日期也需要判断状态时,排序sql如下:
ORDER BY
CASE
WHEN F_EnvirState = 0 THEN --未发放
2
WHEN F_EnvirState = 1 THEN --已发放
1
WHEN F_EnvirState = 2 THEN --已注销
3
WHEN F_EnvirState IS NULL THEN
4
END ,
CASE
WHEN F_EnvirState = 0 THEN
F_ENVIRGRANTDATE
END desc,
CASE
WHEN F_EnvirState = 1 THEN
F_ENVIRGRANTDATE
END desc,
CASE
WHEN F_EnvirState = 2 THEN
F_ENVIRGRANTDATE
END desc