Mysql 选择别名列

Question

我需要过滤具有以下条件列的查询:

 SELECT
     `c`.`fullname` AS `fullname`,
     IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`,
     `bla`.`loan_no` AS `Loan_No`,
     `bla`.`total_loan_amt` AS `Total_Loan_Amt`,
     `bla`.`monthly_installment` AS `Month_Inst_Amt`,
     `bla`.`disb_date` AS `disb_date` FROM (`customers` `c`
 JOIN `basic_loan_application` `bla`
    ON ((`bla`.`customer_uid` = `c`.`customer_uid`))) 
 WHERE Ic LIKE '%800%' ORDER BY `bla`.`Basic_Loan_Application_Id` DESC LIMIT 0, 10

但我无法使用 Ic 字段并返回错误，如下所述:

 Unknown column 'Ic' in 'where clause' 

如果可能的话，我想尽量避免嵌套选择。

这个问题还有其他解决办法吗？

Answer 1

只需重复定义:

WHERE IFNULL(`c`.`new_ic`,`c`.`old_ic`) LIKE '%800%'
ORDER BY `bla`.`Basic_Loan_Application_Id` DESC
LIMIT 0, 10

或者，如果您愿意，可以使用 MySQL 扩展，该扩展允许您使用 having 子句进行过滤(在本例中):

HAVING Ic LIKE '%800%'
ORDER BY `bla`.`Basic_Loan_Application_Id` DESC
LIMIT 0, 10