mysql - 'on clause' 中具有多个连接的未知列

Question

我想知道是否有人可以提供帮助。

我收到错误:

Error Code: 1054. Unknown column 'sub_claims_volume.Claims_Volume' in 'on clause'

当我尝试运行下面的查询时。

我认为这与连接到 pi_loadings_claims_volume 表有关。

我已经从子查询生成了一个表，在这种情况下进行连接没有问题，并且返回了正确的结果。

SELECT bdx_pi_policy_accounts.policyid,
   bdx_pi_policy_accounts.renewal,
   sub_claims_volume.claims_volume,
   pi_loadings_claims_volume.loading
FROM   bdx_pi_policy_accounts
   LEFT JOIN pi_loadings_claims_volume
   ON sub_claims_volume.claims_volume =
   pi_loadings_claims_volume.claims_per_year

LEFT JOIN (SELECT pi_claims.policyid,
    RIGHT(pi_claims.policykey, 1) AS Renewal,
    Count(pi_claims.claimid)      AS Claims_Volume
    FROM   pi_claims
    GROUP  BY pi_claims.policyid,
    pi_claims.policykey) AS sub_claims_volume
    ON bdx_pi_policy_accounts.policyid = sub_claims_volume.policyid
    AND bdx_pi_policy_accounts.renewal = sub_claims_volume.renewal  

Answer 1

之所以显示未知列错误，是因为当与pi_loadings_claims_volume表执行join时，sub_claims_volume表别名未知(如 i)，因此查询无法识别 Claims_Volume 列。

重新定位join中的表(即在join条件之前放置sub_claims_volume查询)应该可以修复它，例如:

SELECT bdx_pi_policy_accounts.policyid,
   bdx_pi_policy_accounts.renewal,
   sub_claims_volume.claims_volume,
   pi_loadings_claims_volume.loading
FROM   bdx_pi_policy_accounts
LEFT JOIN (SELECT pi_claims.policyid,
    RIGHT(pi_claims.policykey, 1) AS Renewal,
    Count(pi_claims.claimid)      AS claims_volume
    FROM   pi_claims
    GROUP  BY pi_claims.policyid,
    pi_claims.policykey) sub_claims_volume
    ON bdx_pi_policy_accounts.policyid = sub_claims_volume.policyid
    AND bdx_pi_policy_accounts.renewal = sub_claims_volume.renewal  
LEFT JOIN pi_loadings_claims_volume
   ON sub_claims_volume.claims_volume =
   pi_loadings_claims_volume.claims_per_year