SJYH_SCORE字段库表计算有问题，不能直接用这个字段，我该怎么改

正确的SJYH_SCORE算法应该是 SJYH_NUM>0时，SJYH_SCORE=SJYH_NUM*5，，，SJYH_NUM<0时 SJYH_SCORE=0，我像图中那样写报错了，我该怎么写

FineReport SQL yzmFXNAH1526936 发布于 2023-2-15 11:22

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共1回答

最佳回答

Z4u3z1Lv6专家互助
发布于2023-2-15 11:23（编辑于 2023-2-15 11:44）

嵌套一个SELECT

比如

SELECT * FROM (

SELECT a*b as 'C'

) T where t.c>50

----------

这种情况用CSAE WHEN SJYH_NUM<0 THEN 0 ELSE SJYH_NUM*5 END

------------

举报收起评论(7)

yzmFXNAH1526936(提问者) (CSAE SJYH_NUM WHEN \'SJYH_NUM<0\' THEN \'0\' ELSE \'SJYH_NUM*5\' END) AS SJYH_SCORE 我这样改报错了

2023-02-15 11:33

Z4u3z1 回复 yzmFXNAH1526936(提问者) 我没加引号啊加引号就字符串了，字符串没法进行四则运算啊

2023-02-15 11:35

yzmFXNAH1526936(提问者) 回复 Z4u3z1 (case when SJYH_NUM<0 THEN 0 else SJYH_NUM*5 end) AS SJYH_SCORE 这样也不行

2023-02-15 11:37

Z4u3z1 回复 yzmFXNAH1526936(提问者) 全部SQL 贴出来

2023-02-15 11:38

yzmFXNAH1526936(提问者) 回复 Z4u3z1 select CITY_CD, CITY_NM, sum(YLZH_SCORE + JK_SCORE + SDSBK_SCORE + KJZF_SCORE + CQQJBD_SCORE + PGJJDT_SCORE+ (case when SJYH_NUM<0 THEN 0 else SJYH_NUM*5 end) AS SJYH_SCORE + YZFSH_SCORE) zfs, rank() over ( order by sum(YLZH_SCORE + JK_SCORE + SDSBK_SCORE + KJZF_SCORE + CQQJBD_SCORE + PGJJDT_SCORE+ (case when SJYH_NUM<0 THEN 0 else SJYH_NUM*5 end) AS SJYH_SCORE + YZFSH_SCORE) desc) qspm from CTRL_RP_ICC_INFO where STAT_DT = \'${para1}\' and STAT_PER = 1 group by CITY_CD,CITY_NM order by CITY_CD

2023-02-15 11:42