mysql - sql不允许在where子句比较中使用case变量

Question

这个问题已经有答案了:

Referring to a Column Alias in a WHERE Clause (9 个回答)

已关闭 3 年前。

我正在尝试在 sql_server 中运行查询

我想加入 2 col 并比较两者的数量以及 print_col 的数量，其中第一个表(sid)的数量少于第二个表(idt)的数量

由于我需要第一个表的所有数据，无论是否没有连接，这就是为什么我使用 LEFT JOIN 并将 null 字段设置为 0

SELECT sid.id, sid.total_quantity, idt.id,
CASE 
    WHEN idt.total_quantity IS NULL THEN 0
   ELSE idt.total_quantity
END as total_quantity2
FROM abc_details as sid
LEFT JOIN (SELECT * FROM def_details WHERE location_name = 'XYZ') as idt
ON sid.item_no = idt.item_no 
WHERE sid.stock_invoice_id = 37
ORDER BY sid.item_code, sid.lot_number

这与 case 条件的 col_name 'total_quantity2' 配合得很好

但是，当我尝试比较时

WHERE sid.stock_invoice_id = 37 AND sid.total_quantity < total_quantity2

但是，我收到错误

 Unknown column 'total_quantity2' in 'where clause'

为什么会发生这种情况以及如何解决这个问题

Answer 1

您可以将查询简化为:

SELECT sid.id, sid.total_quantity, idt.id,
       COALESCE(idt.total_quantity, 0) as total_quantity2
FROM abc_details sid LEFT JOIN
     def_details idt
     ON sid.item_no = idt.item_no AND
        idt.location_name = 'XYZ'
WHERE sid.stock_invoice_id = 37
ORDER BY sid.item_code, sid.lot_number;

这会将 CASE 表达式更改为 COALESCE() 并删除子查询。

对于您的 WHERE 子句，您不妨重复表达式:

WHERE sid.stock_invoice_id = 37 AND
      sid.total_quantity < COALESCE(idt.total_quantity, 0)

但是，考虑到数量通常为非负数并假设 NULL 值来自 LEFT JOIN，您可以将查询编写为:

SELECT sid.id, sid.total_quantity, idt.id,
       idt.total_quantity as total_quantity2
FROM abc_details sid JOIN
     def_details idt
     ON sid.item_no = idt.item_no AND
        idt.location_name = 'XYZ'
WHERE sid.stock_invoice_id = 37 AND
      sid.total_quantity < idt.total_quantity
ORDER BY sid.item_code, sid.lot_number;

也就是说，如果您希望在 WHERE 子句中出现这种不等式，则需要匹配行。