SQL - 左连接中的表别名

Question

我正在尝试使用左连接编写简单的SQL查询。

这是查询：

SELECT * 
FROM ( 
    SELECT * 
    FROM TRN_IN.COIT AS TRANSMISSIONS 
    LEFT JOIN ( 
     SELECT TRNNumber ,COUNT(ID) 
     FROM TRN_IN.COIT AS TOTAL_LINES 
     WHERE DataPiece02 = 'TO' 
     GROUP BY TRNNumber 
    ) ON TRANSMISSIONS.TRNNumber = TOTAL_LINES.TRNNumber 
) 

二表的别名是TOTAL_LINES。

运行时，出现TOTAL_LINES未列出的错误。

奇怪的是，当试图运行本身的第二个查询，也没有任何错误：

(SELECT TRNNumber ,COUNT(ID) 
FROM TRN_IN.COIT AS TOTAL_LINES 
WHERE DataPiece02 = 'TO' 
GROUP BY TRNNumber) 

任何人有线索？

Answer 1

希望你在错误的地方分配AS TOTAL_LINES表别名。

在第二个子查询后放置表别名可以解决您的问题。

你可以试试下面的查询：

SELECT * 
FROM (
    SELECT * 
    FROM TRN_IN.COIT AS TRANSMISSIONS 
    LEFT JOIN (
     SELECT TRNNumber 
      ,COUNT(ID) AS CountDetails 
     FROM TRN_IN.COIT 
     WHERE DataPiece02 = 'TO' 
     GROUP BY TRNNumber 
     ) AS TOTAL_LINES ON TRANSMISSIONS.TRNNumber = TOTAL_LINES.TRNNumber 
    ) AS Result 

Answer 2

TOTAL_LINES必须是整个选择的别名，而不是在FROM子句中的表。更改为：

SELECT * FROM (SELECT * FROM TRN_IN.COIT AS TRANSMISSIONS 

LEFT JOIN (SELECT TRNNumber ,COUNT(ID) FROM TRN_IN.COIT WHERE DataPiece02 = 'TO' GROUP BY TRNNumber) AS TOTAL_LINES 

ON TRANSMISSIONS.TRNNumber = TOTAL_LINES .TRNNumber) 

Answer 3

试试这个..

SELECT TOTAL_LINES.TRNNumber,COUNT(TOTAL_LINES.ID) FROM TRN_IN.COIT TRANSMISSIONS LEFT JOIN TRN_IN.COIT TOTAL_LINES 
ON TRANSMISSIONS.TRNNumber = TOTAL_LINES.TRNNumber 
WHERE TOTAL_LINES.DataPiece02 = 'TO' GROUP BY TOTAL_LINES.TRNNumber;