SQL -指定了多次列

Question

当尝试在SQL中运行此查询时，我得到以下错误:

SELECT *
FROM [Submissions] AS [s]
INNER JOIN [SubmissionHistories] AS [s0] ON [s].[Id] = [s0].[SubmissionId]
INNER JOIN
( SELECT TOP 1 WITH TIES *
FROM OHis
ORDER BY row_number() OVER (PARTITION BY ApplicationId ORDER BY OwnerChangeDate DESC)
) AS [oh] ON [oh].[ApplicationId] = [s].ApplicationId
INNER JOIN 
( SELECT TOP 1 WITH TIES *
FROM AHis
ORDER BY row_number() OVER (PARTITION BY ApplicationId ORDER BY AuthorizedSubmitterChangeDate DESC)
) AS [ash] ON [ash].[ApplicationId] = [s].ApplicationId

列'ApplicationId'被多次指定为' submission '。我错过了什么?

Answer 1

这里所发生的是一列叫做ApplicationId出现在多个表。因为你正在做一个毯子SELECT *，你的SQL引擎不能处理这个。纠正措施将前缀ApplicationId与别名范围到一个特定的表。例如，假设Submissions和SubmissionHistories有一个ApplicationId列，您可以使用:

SELECT s.ApplicationId AS sAId, s0.ApplicationId AS s0AId, -- other columns here
FROM ...

请注意，由于您所看到的问题，总是最好显式列出您选择的所有列。

Answer 2

用连接语句中创建的表别名作为列名的前缀。

A.Wine_ID,
A.Grape_Name,
A.Grape,
B.Grape, 
B.Grape_Vineyard_Location
FROM WINE_TABLE A
JOIN GRAPE_VINEYARD_TABLE B
ON A.Wine_ID=B.Wine_ID

在我的select语句中，'Grape'列前的前缀(A, B)允许我从WINE_TABLE A和GRAPE_VINEYARD_TABLE B中拉入Grape列。