mysql 引用列_引用聚合列的MySQL别名

SELECT

COMPANY_ID,

SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,

SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE

SUM(NON_BILLABLE)/SUM(BILLABLE) AS RATIO

FROM TRANSACTIONS

GROUP BY COMPANY_ID

像这样定义RATIO看起来很漂亮和干净,但显然也是forbidden by SQL.

为了使查询正常工作,我只复制了NON_BILLABLE和BILLABLE的CASE语句.

SELECT

COMPANY_ID,

SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,

SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE

SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END)/SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS RATIO

FROM TRANSACTIONS

GROUP BY COMPANY_ID

是否有更好,更清晰(非冗余)的方式来编写此查询？

解决方法:

使用：

SELECT x.company_id,

x.non_billable,

x.billable,

x.non_billable/x.billable AS RATIO

FROM (SELECT t.company_id

SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,

SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE

FROM TRANSACTIONS

GROUP BY t.company_id) x

标签：mysql,sql,aggregation,alias

来源： https://codeday.me/bug/20190627/1301271.html