时间戳

首页 > 解决方案 > MySQL 通过子选择变量过滤 WHERE

php - MySQL 通过子选择变量过滤 WHERE

问题描述

我有一个带有子选择语句的查询，我正在寻找一种在 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_date
FROM        Project p
JOIN        ProjectFieldValue pfv
 ON         p.id = pfv.project_id
WHERE       (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）过滤结果。

标签： phpmysql

解决方案

Store table_filter_date in a variable and use that in your WHERE clause:

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'

推荐阅读