MySQL SELECT查询中如何将已获取列值用于IF/CASE子句比较

你在MySQL里用流程控制函数写查询时，碰到了一个问题：前面IF子句生成的lead_id、Cont_id别名，能作为后续子查询的WHERE参数，但没法直接放到IF/CASE语句里做值比较，导致想要的逻辑跑不起来。

你的现有查询是这样的：

SELECT *, 
        IF(tte.task_id>0, (SELECT mt.lead_id FROM mt WHERE mt.id=tte.task_id), 'NA') AS lead_id,
        IF(tte.task_id>0, (SELECT mt.assigned_to_staff_id FROM mt WHERE mt.id=tte.task_id), 'NA') AS staff_id,
        IF(tte.task_id>0, (SELECT leads.contact_id FROM leads WHERE leads.id=lead_id),'NA') AS Cont_id,
        IF(tte.task_id>0, (SELECT contacts.Name FROM contacts WHERE contacts.id=Cont_id),'NA') AS PC_Name,
        CASE WHEN tte.task_id>0 THEN (SELECT contacts.Name AS LC_Name FROM contacts WHERE contacts.id=PC_id) ELSE 'NA' END
FROM tte ORDER BY request_log_time DESC;

你想要实现的是类似这样的逻辑：

IF(Cont_id>0, (SELECT contacts.Name FROM contacts WHERE contacts.id=Cont_id),'NA') AS PC_Name

或者：

CASE WHEN Cont_id>0 THEN (SELECT contacts.Name AS LC_Name FROM contacts WHERE contacts.id=Cont_id) ELSE 'NA' END

下面给你两种可行的解决方法：

方法1：用子查询生成中间结果（兼容所有MySQL版本） #

MySQL里，SELECT子句里定义的别名不能在同层的其他表达式里直接用。你可以把生成lead_id、Cont_id的逻辑放到内层子查询，外层再基于这些中间值做判断：

SELECT 
    tte.*,
    sub.lead_id,
    sub.staff_id,
    sub.Cont_id,
    IF(sub.Cont_id > 0, (SELECT c.Name FROM contacts c WHERE c.id = sub.Cont_id), 'NA') AS PC_Name,
    CASE WHEN sub.Cont_id > 0 THEN (SELECT c.Name FROM contacts c WHERE c.id = sub.Cont_id) ELSE 'NA' END AS LC_Name
FROM (
    SELECT 
        tte.*,
        IF(tte.task_id>0, (SELECT mt.lead_id FROM mt WHERE mt.id=tte.task_id), 'NA') AS lead_id,
        IF(tte.task_id>0, (SELECT mt.assigned_to_staff_id FROM mt WHERE mt.id=tte.task_id), 'NA') AS staff_id,
        -- 内层子查询里不能直接引用同层的lead_id，所以要重复子查询逻辑
        IF(tte.task_id>0, (SELECT leads.contact_id FROM leads WHERE leads.id = (SELECT mt.lead_id FROM mt WHERE mt.id=tte.task_id)), 'NA') AS Cont_id
    FROM tte
) AS sub
ORDER BY sub.request_log_time DESC;

方法2：用JOIN替代嵌套子查询（更高效，推荐） #

嵌套子查询不仅看着乱，性能也容易打折扣。改用LEFT JOIN关联所有需要的表，逻辑会更清晰，还能直接用关联后的字段做判断：

SELECT 
    tte.*,
    IF(tte.task_id > 0, mt.lead_id, 'NA') AS lead_id,
    IF(tte.task_id > 0, mt.assigned_to_staff_id, 'NA') AS staff_id,
    IF(tte.task_id > 0 AND leads.contact_id IS NOT NULL, leads.contact_id, 'NA') AS Cont_id,
    IF(leads.contact_id > 0, contacts.Name, 'NA') AS PC_Name,
    CASE WHEN leads.contact_id > 0 THEN contacts.Name ELSE 'NA' END AS LC_Name
FROM tte
LEFT JOIN mt ON tte.task_id = mt.id AND tte.task_id > 0
LEFT JOIN leads ON mt.lead_id = leads.id AND tte.task_id > 0
LEFT JOIN contacts ON leads.contact_id = contacts.id
ORDER BY tte.request_log_time DESC;

这种方式把关联逻辑都放到JOIN里，既避免了重复计算，又完美解决了别名无法引用的问题。

内容的提问来源于stack exchange，提问作者Tariq