mysql - 在选择列规范中使用CASE通过别名引用聚合结果

这是一组更大的表的一部分，但最根本的故障是我有两个表employees和profits。每次在公司获利时，都会将其添加到获利中，并引用获利的员工ID，然后执行案例检查以在自定义列中显示任意值。

架构：
http://sqlfiddle.com/#!2/aad19

当我拉出一个雇员列表时，我通过子查询生成属于他们的所有利润的总数：
SELECT firstname, lastname, (SELECT SUM(amount) FROM利润 WHERE profits.employee_id=employees.id) AS利润 FROM employees

产生以下列：

firstnamelastnameprofits

现在，我需要根据利润值将自定义列设置为一个值。如果利润是雇员表中的常规列而不是子查询的结果，我将简单地执行以下操作：

SELECT firstname, lastname, (SELECT SUM(amount) FROM利润 WHERE profits.employee_id=employees.id) AS利润 CASE WHEN (利润IS NOT NULL) THEN 'Met Requirements' WHEN (利润>100) THEN 'Exceeded Requirements' ELSE 'No Profits' END as rating FROM employees

如您所见，这会在结果集中创建一个“等级”别名列，我可以根据自己的意愿（有多少或是否为用户带来收益）有条件地进行设置。我将这些数据的结果发送到我们无法控制的第三方应用程序，仅按原样显示“评分”列，因此我需要使用“没有利润”或“达到/超出要求”预先填充该列。

但是，我很沮丧地发现您不能在这种情况下使用CASE，因为它抱怨评级列不存在，因为它是根据推断得出的（或mysql用于makeupup列的任何单词）

有什么办法可以使我工作？

最佳答案

您需要的查询只是一个简单的LEFT JOIN，因为您必须显示每个employee及其profits。同样，您在case语句IS NOT NULL上有一个小错误，将永远不会使'No Profits'条件成立。所以我解决了这个问题，请尝试以下查询：

select firstname,
       lastname,
       CASE WHEN (profits>0 and profits <=100)
               THEN 'Met Requirements'
            WHEN (profits>100)
               THEN 'Exceeded Requirements'
            ELSE    'No Profits'
       END as rating
from
(
SELECT
    e.firstname,
    e.lastname,
    SUM(p.amount) profits
FROM employees e left join
     profits p on (e.`id` = p.`employee_id`)
GROUP BY
   e.firstname,
   e.lastname
) tbl

在SQL Fiddle Demo上查看

您也可以使用SUM语句中的聚合函数CASE来仅使用一个查询，但是我认为作为子查询的第一个示例它并不美观。

SELECT
    e.firstname,
    e.lastname,
    CASE WHEN (SUM(p.amount)>0 and SUM(p.amount) <=100)
               THEN 'Met Requirements'
            WHEN (SUM(p.amount)>100)
               THEN 'Exceeded Requirements'
            ELSE    'No Profits'
       END as rating
FROM employees e left join
     profits p on (e.id = p.employee_id)
GROUP BY
   e.firstname,
   e.lastname

在SQL Fiddle Demo 2上查看