ODPS SQL 报错：Illegal union operation - type mismatch for UNION, left has 2 columns while right has 3 columns 解决方法

{"title":"运行以下语句：select \n u.user_id,\n u.natural_cust_id,\n u.prov_id\nfrom zq_dwa.dwa_m_cus_al_cb_user_info u\ninner join \n(select \n a.natural_cust_id,\n b.cust_level\nfrom zq_dwa.dwa_m_cus_al_cb_user_info a \nleft join zq_dwa.dwa_m_cus_al_list_nat_info b on a.natural_cust_id = b.nature_id\nwhere b.cust_level = '0'\n\nunion all \n\nselect \n a.prov_id,\n a.natural_cust_id,\n b.cust_level\nfrom zq_dwa.dwa_m_cus_al_cb_user_info a \nleft join zq_dwa.dwa_m_cus_al_list_nat_info b on a.prov_id = b.prov_id and a.natural_cust_id = b.nature_id\nwhere b.cust_level = '1') t \non u.user_id = t.user_id and u.natural_cust_id = t.natural_cust_id and u.prov_id = t.prov_id\ngroup by u.natural_cust_id order by u.total_fee limit 50;后报错， 报错如下：FAILED: ODPS-0130241:[11,1] Illegal union operation - type mismatch for UNION, left has 2 columns while right has 3 columns\nODPS-0130071:[15,18] Semantic analysis exception - column t.user_id cannot be resolved; Did you mean u.user_id ?\nODPS-0130071:[15,86] Semantic analysis exception - column t.prov_id cannot be resolved; Did you mean u.prov_id ?\nODPS-0130071:[1,8] Semantic analysis exception - column reference u.user_id should appear in GROUP BY key\nODPS-0130071:[1,38] Semantic analysis exception - column reference u.prov_id should appear in GROUP BY key\nODPS-0130071:[17,10] Semantic analysis exception - column reference u.total_fee should appear in GROUP BY key；\n如何改正\n内容：根据报错信息，可以看到以下问题：\n\n1. 在union操作中，左边的查询结果有2列，而右边的查询结果有3列，导致类型不匹配。需要保证union操作的两个查询结果的列数一致。\n2. 在on子句中，引用了t.user_id和t.prov_id，但是在子查询中没有包含这两个列，所以报错"column t.user_id cannot be resolved; Did you mean u.user_id ?"和"column t.prov_id cannot be resolved; Did you mean u.prov_id ?"。需要根据实际需求，修改子查询的结果列。\n3. 在group by子句中，引用了u.total_fee，但是在select子句中没有包含该列，所以报错"column reference u.total_fee should appear in GROUP BY key"。需要将u.total_fee添加到select子句中。\n\n根据以上问题，可以修改原始语句如下：\n\nsql\nselect \n u.user_id,\n u.natural_cust_id,\n u.prov_id,\n u.total_fee\nfrom zq_dwa.dwa_m_cus_al_cb_user_info u\ninner join \n(select \n a.user_id,\n a.natural_cust_id,\n a.prov_id\nfrom zq_dwa.dwa_m_cus_al_cb_user_info a \nleft join zq_dwa.dwa_m_cus_al_list_nat_info b on a.natural_cust_id = b.nature_id\nwhere b.cust_level = '0'\n\nunion all \n\nselect \n a.user_id,\n a.natural_cust_id,\n a.prov_id\nfrom zq_dwa.dwa_m_cus_al_cb_user_info a \nleft join zq_dwa.dwa_m_cus_al_list_nat_info b on a.prov_id = b.prov_id and a.natural_cust_id = b.nature_id\nwhere b.cust_level = '1') t \non u.user_id = t.user_id and u.natural_cust_id = t.natural_cust_id and u.prov_id = t.prov_id\ngroup by u.user_id, u.natural_cust_id, u.prov_id, u.total_fee\norder by u.total_fee\nlimit 50;\n\n\n请注意，根据实际需求，可能还需要根据具体情况做一些其他调整。