Mysql CAST(CONCAT_WS 试图获得正确的语法

Question

这有效

SELECT EntryId FROM 2_1_journal 
WHERE CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) = ? 

但希望在 WHERE 之前为日期定义“虚拟”列名称，并使用列名称，例如 RecordDate。

已尝试

SELECT EntryId FROM 2_1_journal 
CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate 
WHERE RecordDate = ?


SELECT EntryId FROM 2_1_journal 
CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) AS RecordDate 
WHERE RecordDate = ?


SELECT EntryId FROM 2_1_journal 
CAST((CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) AS RecordDate) 
WHERE RecordDate = ?

在所有情况下都会出现错误 语法错误或访问冲突:1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在“CAST”附近使用的正确语法。

请告知正确的语法

Answer 1

http://dev.mysql.com/doc/refman/5.5/en/select.html :

“It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section C.5.5.4, Problems with Column Aliases.”

如果您还想选择 WHERE 子句中的操作结果作为列值，那么您必须将该操作写入两次 - 一次在 WHERE 子句中，没有别名，以及 SELECT 之后的列列表中。