mysql 'T.subscription_package_id' 中的未知列 'on clause'

Question

请检查以下查询，它会给我错误 MySQL 说:文档 -“on 子句”中的未知列“T.subscription_package_id”

SELECT COALESCE(T.SUBSCRIPTION_STATUS, 'N') as SUBSCRIPTION_STATUS, c.assigned_executive as user_id,concat(ud.first_name,' ',ud.last_name) as username,c.id as customer_id, concat(c.first_name,' ',c.last_name) as customer_name ,c.mobile 
    FROM tbl_customer c 
    LEFT join tbl_user_details ud on c.assigned_executive=ud.user_id 
    LEFT JOIN (SELECT a1.customer_id, (CASE WHEN a1.available_credits > 0 THEN 'Y' ELSE 'N' END) AS SUBSCRIPTION_STATUS FROM tbl_customer_subscription a1 GROUP BY a1.customer_id)T ON c.id = T.customer_id 
    LEFT JOIN tbl_subscription_package_services z ON z.subscription_package_id = T.subscription_package_id 
    group by c.id

Answer 1

您的问题是因为您尝试从 SUBQUERY 获取字段，而该子查询没有所需的字段:

(SELECT a1.customer_id, 
       (CASE WHEN a1.available_credits > 0 THEN 'Y' ELSE 'N' END) AS SUBSCRIPTION_STATUS 
   FROM tbl_customer_subscription a1 
  GROUP BY a1.customer_id) T

此子查询没有您尝试获取的字段。当您使用子查询作为表时，只有选定的字段可供使用，而不是选定的字段加上您认为可以工作的表的并集，因此在子查询上添加您的字段

(SELECT a1.customer_id,
        a1.subscription_package_id, -- here the missing field
       (CASE WHEN a1.available_credits > 0 THEN 'Y' ELSE 'N' END) AS SUBSCRIPTION_STATUS 
   FROM tbl_customer_subscription a1 
  GROUP BY a1.customer_id) T   

因此，当您执行 ON z.subscription_package_id = T.subscription_package_id 时，它将起作用，因为它现在已存在。