未知列在where子句

问题描述：

SELECT 
    ps.id, 
    MAX(ps.dueDate) as due_date, 
    u.firstName as first_name, 
    u.lastName as last_name, 
    u.email, 
    IDENTITY(ps.loanApplication) as loan_application_id, 
    DATE_DIFF(MAX(ps.dueDate), CURRENT_DATE()) as diff 
FROM 
    Loan\Entity\PaymentSchedule ps 
LEFT JOIN 
    ps.paymentType pt 
LEFT JOIN 
    ps.loanApplication la 
LEFT JOIN 
    la.status s 
LEFT JOIN 
    la.user u 
WHERE 
    pt.slug != :paymentSlug AND s.keyIdentifier = :status AND diff = 14 
GROUP BY 
    ps.loanApplication

它转换为以下SQL查询

SELECT 
    p0_.id AS id_0, 
    MAX(p0_.due_date) AS sclr_1, 
    u1_.first_name AS first_name_2, 
    u1_.last_name AS last_name_3, 
    u1_.email AS email_4, 
    p0_.loan_application_id AS sclr_5, 
    DATEDIFF(MAX(p0_.due_date), CURRENT_DATE) AS sclr_6 
FROM 
    payment_schedule p0_ 
LEFT JOIN 
    payment_type p2_ ON p0_.payment_type_id = p2_.id 
LEFT JOIN 
    loan_application l3_ ON p0_.loan_application_id = l3_.id 
LEFT JOIN 
    loan_application_status l4_ ON l3_.loan_application_status_id = l4_.id 
LEFT JOIN 
    user u1_ ON l3_.user_id = u1_.id 
WHERE 
    p2_.slug <> ? AND l4_.key_identifier = ? AND sclr_6 = 14 
GROUP BY 
    p0_.loan_application_id

这给了我下面的错误

====================================================================== 
PDOException 
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sclr_6' in 'where clause' 
----------------------------------------------------------------------

当我更换WHERE条件

WHERE pt.slug != :paymentSlug AND s.keyIdentifier = :status AND diff = 14

随着

WHERE pt.slug != :paymentSlug AND s.keyIdentifier = :status

它完美，并显示了我正确的记录，我也尝试以下WHERE条件

WHERE pt.slug != :paymentSlug AND s.keyIdentifier = :status AND DATE_DIFF(MAX(ps.dueDate), CURRENT_DATE()) = :days_diff 

WHERE pt.slug != :paymentSlug AND s.keyIdentifier = :status HAVING (DATE_DIFF(MAX(ps.dueDate), CURRENT_DATE())) = :days_diff

WHERE以上不工作，以及，我失去了什么吗？

谢谢。

将DATE_DIFF（）向下移动到where子句。 – Cerad

答

如果你想在你的WHERE子句中使用别名，你需要一个sub-select。

select * 
from 
(SELECT 
    p0_.id AS id_0, 
    MAX(p0_.due_date) AS sclr_1, 
    u1_.first_name AS first_name_2, 
    u1_.last_name AS last_name_3, 
    u1_.email AS email_4, 
    p0_.loan_application_id AS sclr_5, 
    DATEDIFF(MAX(p0_.due_date), CURRENT_DATE) AS sclr_6 
FROM 
    payment_schedule p0_ 
LEFT JOIN 
    payment_type p2_ ON p0_.payment_type_id = p2_.id 
LEFT JOIN 
    loan_application l3_ ON p0_.loan_application_id = l3_.id 
LEFT JOIN 
    loan_application_status l4_ ON l3_.loan_application_status_id = l4_.id 
LEFT JOIN 
    user u1_ ON l3_.user_id = u1_.id 
) A 
WHERE 
    slug <> ? AND key_identifier = ? AND sclr_6 = 14

这是怎么查询逻辑处理

FROM clause 
WHERE clause 
SELECT clause 
GROUP BY clause 
HAVING clause 
ORDER BY clause

由于Where来Select之前，你不能在Where子句中使用alias name

按照你的建议尝试，它不起作用。 –

@IbrahimAzharArmar - 已更新检查 –

答

不能使用别名（对最终结果的字段）在条款WHERE;但是，至少在MySQL中，您可以使用HAVING子句，而不需要GROUP BY。

它不起作用... –

答

您正在使用的表达式是聚合的结果。替换为having子句，使查询看起来像;

SELECT . . . 
WHERE p2_.slug <> ? AND l4_.key_identifier = ? 
GROUP BY p0_.loan_application_id 
HAVING sclr_6 = 14

请注意，date_diff()不是MySQL中的函数。你打算datediff()。

未知列在where子句

相关推荐