【DM版本】: DM Database Server 64 V8; DB Version: 0x7000c
【操作系统】:Kylin V10
【CPU】: 海光x86
【问题描述】*:
下面这条sql语句在MySQL下执行正常,请问是having不支持别名操作吗?
SELECT
count(ww.id) AS num,
sum(if(ww.status IN(12, 13, 9), 1, 0)) AS snum
FROM
wo_workorder ww
WHERE ww.deleted = 0
group by ww.de_demand_id
HAVING num = snum;
不支持,要把COUNT和SUM都放到having 里
HAVING 不能使用别名,改成使用完整表达式:
SELECT
COUNT(ww.id) AS num,
SUM(CASE WHEN ww.status IN (12, 13, 9) THEN 1 ELSE 0 END) AS snum
FROM
wo_workorder ww
WHERE
ww.deleted = 0
GROUP BY
ww.de_demand_id
HAVING
COUNT(ww.id) = SUM(CASE WHEN ww.status IN (12, 13, 9) THEN 1 ELSE 0 END);