引用聚合列的MySQL别名

发布时间：2020-12-14 04:51:55 所属栏目：百科来源：网络整理

导读：继续我的问题 summarizing-two-conditions-on-the-same-sql-table,我添加了一个RATIO列,它只是一个SUM(…)列除以第二个SUM(…)列： 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) THE

继续我的问题 summarizing-two-conditions-on-the-same-sql-table,我添加了一个RATIO列,它只是一个SUM(…)列除以第二个SUM(…)列：

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,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,3) THEN 1 ELSE 0 END) AS BILLABLE
         FROM TRANSACTIONS
     GROUP BY t.company_id) x

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!