MySQL 通过子选择变量过滤 WHERE

Question

我有一个带有子选择语句的查询，我正在寻找一种在 where 子句中使用它的方法，如下所示：SELECT      p.id as project_id, (            SELECT      pfva.dateValue            FROM        ProjectFieldValue pfva            WHERE       (pfva.fieldKey = 'listing_date' AND p.id = project_id)            LIMIT 1                     ) as table_filter_dateFROM        Project pJOIN        ProjectFieldValue pfv ON         p.id = pfv.project_idWHERE       (fieldKey = 'contract_status' AND pfv.textValue IN ('Active'))AND         table_filter_date BETWEEN '2020-06-06' AND '2020-12-12'这似乎不起作用......AND table_filter_date BETWEEN '2020-06-06' AND '2020-12-12'我收到以下错误：Unknown column 'table_filter_date' in 'where clause'有什么方法可以在 HAVING 或 FIND_IN_SET 中或在 WHERE 中以某种方式使用此值“table_filter_date”。我基本上需要能够通过此变量以及开始日期和结束日期（使用 BETWEEN 和 AND）以某种方式过滤结果。

Answer 1

您可以通过将查询包含为“表表达式”来生成此命名列。例如：

select *

from ( -- table expression "x" starts

  SELECT    p.id as project_id, (

            SELECT      pfva.dateValue

            FROM        ProjectFieldValue pfva

            WHERE       (pfva.fieldKey = 'listing_date' AND p.id = project_id)

            LIMIT 1         

            ) as table_filter_date

  FROM      Project p

  JOIN      ProjectFieldValue pfv

   ON       p.id = pfv.project_id

  WHERE     (fieldKey = 'contract_status' AND pfv.textValue IN ('Active'))

) x -- table expression "x" ends

WHERE   table_filter_date BETWEEN '2020-06-06' AND '2020-12-12'

Answer 2

存储table_filter_date在变量中并在您的WHERE子句中使用它：

SELECT      p.id as project_id, ( @tfd := (

            SELECT      pfva.dateValue

            FROM        ProjectFieldValue pfva

            WHERE       (pfva.fieldKey = 'listing_date' AND p.id = project_id)

            LIMIT 1         

            ) ) as table_filter_date

FROM        Project p

JOIN        ProjectFieldValue pfv

 ON         p.id = pfv.project_id

WHERE       (fieldKey = 'contract_status' AND pfv.textValue IN ('Active'))

AND         @tfd BETWEEN '2020-06-06' AND '2020-12-12'