php - 在条件 Mysql 中使用时别名变量不起作用

Question

在条件client != id中使用时，别名变量不起作用

select *,str_to_date(invoice_date,'%d/%m/%Y') as date,invoice_to as id, 
    (select mob_no from client_info where mob_no = id ) as client
from client_invoice
where client != id
having str_to_date(invoice_date,'%d/%m/%Y') >= '$fdate'
   and str_to_date(invoice_date,'%d/%m/%Y') <= '$tdate'  
ORDER BY date DESC

请帮忙解决这个问题。提前致谢。

Answer 1

您不能在 WHERE 子句中引用别名，必须使用 HAVING 来引用它。

SELECT *, str_to_date(invoice_date, '%d/%m/%Y') AS date,
       invoice_to AS id,
       (SELECT mob_no FROM client_info WHERE mob_no = invoice_to) AS client
FROM client_invoice
WHERE STR_TO_DATE(invoice_date, '%d/%m/%Y') BETWEEN '$fdate' AND '$tdate'
HAVING client != id