mysql求两列差值。。。

Question

SELECT container_code,
COUNT(container_code) as num ,
count(CASE WHEN circulate_state IN(0,1) THEN container_code ELSE NULL END) as actualNum
FROM t_circulate_history
GROUP BY
container_code
要统计后的列actualNum 和 num的差异列differentNum，直接（actualNum - num）as differentNum不行。应该怎么写？

Answer 1

用as之前的相减,你试试:

SELECT container_code,
(COUNT(container_code)-count(CASE WHEN circulate_state IN(0,1) THEN container_code ELSE NULL END) )
FROM t_circulate_history
GROUP BY
container_code