我有一些代码:
CREATE OR REPLACE TEMPORARY VIEW cctv_join_1
AS
SELECT *
FROM cctv1_details AS A
LFET JOIN cctv3_details AS B ON (A.CDE_WR = B.CDE_WR) AND A.CDE_dist = B.CDE_dist
然后我尝试
CREATE OR REPLACE TEMPORARY VIEW cctv_join_2
AS
(SELECT *
FROM cctv_join_1 AS A
LEFT JOIN cctv5_details AS B ON (A.CDE_WR = B.CDE_WR) AND (A.CDE_dist = B.CDE_dist))
但我得到一个错误
SQL语句中的错误:AnalysisException:引用'A.CDE_WR'不明确,可能是:A.CDE_WR,A.CDE_WR。第6行pos 4
在数据块中。我该如何处理?
该查询:
SELECT *
FROM cctv1_details c1d LEFT JOIN
cctv3_details c3d
ON c1d.CDE_WR = c3d.CDE_WR AND c1d.CDE_dist = c3d.CDE_dist
正在使用SELECT *
。这*
是两个表中所有列的简写。
显然,两个表中的合并列具有重复的列名;至少,CDE_WR
而且CDE_dist
-可能还有其他人。通用解决方案是列出所有列:
SELECT c1d.col1, c1d.col2, . . . c3d.colx, c3d.coly
FROM cctv1_details c1d LEFT JOIN
cctv3_details c3d
ON c1d.CDE_WR = c3d.CDE_WR AND c1d.CDE_dist = c3d.CDE_dist;
但是,这通常简称为:
SELECT c1d.*, c3d.colx, c3d.coly
FROM cctv1_details c1d LEFT JOIN
cctv3_details c3d
ON c1d.CDE_WR = c3d.CDE_WR AND c1d.CDE_dist = c3d.CDE_dist;
请注意,我将表别名更改为表名称的合理缩写,使查询更加清晰和易于维护。