php - 'expiry_date' 我的 sql 查询中的未知列 'where clause'

Question

我有多个表，我正在使用以下查询按 expiry_date 过滤尚未过期的记录。

我的查询:

SELECT

    MT.id,
    CONCAT(MEM.name,' ',MEM.last_name) AS name,
    IFNULL((SELECT MAX(MFT.membership_end_date) 
        FROM membership_future_transaction AS MFT 
        WHERE MFT.membership_transaction_id = MT.id),MM.end_date) AS expiry_date,
    MEM.phone,
    MEM.email,
    MMST.name AS membership_name,
    MMST.price,
    MMST.session
FROM
    membership_transaction AS MT
    LEFT JOIN member_master AS MEM ON MEM.id = MT.member_id
    LEFT JOIN members_membership AS MM ON MM.membership_transaction_id = MT.id
    LEFT JOIN membership_master AS MMST ON MMST.id = MT.membership_id
WHERE
    MT.is_casual = 'No'
    AND MT.is_deleted = 'No'
    AND MM.is_cancelled = 'No'
    AND expiry_date >= '2016-01-01'"

但是我收到“where 子句”中的“未知列”expiry_date”错误，请帮助我，我在这里缺少什么。

Answer 1

MySQL 有一个也许很漂亮的功能，您可以将 having 与别名一起使用。因此，如果将 where 子句更改为:

WHERE MT.is_casual = 'No' AND
      MT.is_deleted = 'No' AND
      MM.is_cancelled = 'No'
HAVING expiry_date >= '2016-01-01'"

然后它应该可以工作。当然，对于使用 group by 的查询，这不会像您期望的那样工作。