sql - 基于case语句的SQL过滤器

Question

我有一些接近下面例子的逻辑：

SELECT
    employee.num,
    CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
    END AS attr_status

FROM employee

LEFT JOIN job ON employee.key = job.key

LEFT JOIN physical ON physical.key = job.key

LEFT JOIN chemical ON chemical.key = job.key

WHERE attr_status = 'A'

关于如何实现这一目标的任何建议？

谢谢你。

标签： sql

Answer 1

因为在大多数（或所有？）DBMS 系统中，您不能在 WHERE 子句中使用别名，因此您有 2 个选项：

1. attr_status用CASE 的副本替换WHERE 子句

SELECT
    employee.num,
    CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
    END AS attr_status

FROM employee

LEFT JOIN job ON employee.key = job.key

LEFT JOIN physical ON physical.key = job.key

LEFT JOIN chemical ON chemical.key = job.key

WHERE CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
      END = 'A'

2. 使您的查询成为子查询：

SELECT * 
FROM (
   SELECT
       employee.num,
       CASE job.type
           WHEN 'C' THEN chemical.chem_status
           ELSE physical.phys_status
       END AS attr_status

   FROM employee

   LEFT JOIN job ON employee.key = job.key

   LEFT JOIN physical ON physical.key = job.key

   LEFT JOIN chemical ON chemical.key = job.key 
) x

WHERE x.attr_status = 'A'