我有一個帶有子選擇語句的查詢，我正在尋找一種在 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）以某種方式過濾結果。
2 回答
萬千封印
TA貢獻1891條經驗 獲得超3個贊
您可以通過將查詢包含為“表表達式”來生成此命名列。例如：
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'
慕萊塢森
TA貢獻1810條經驗 獲得超4個贊
存儲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'
- 2 回答
- 0 關注
- 164 瀏覽
添加回答
舉報
0/150
提交
取消