在多子SQL中显示引用列。

Question

-------  id   -------  id   -------  id   -------  id   -------
| t-1 | <---> | t-a | <---> | t-b | <---> | s-a | <---> | s-b |
-------       -------       -------       -------       ------- 

SELECT distinct 
   t-a.col, t-b.col
FROM 
   INNER JOIN t-1 ON t-1.id = t-a.id
   LEFT OUTER JOIN t-b on t-a.id = t-b.id
GROUP BY
   T-1.id

所以我想要的是显示来自s的列(以及显示的所有其他列)，它是由t引用的s-a引用的。我可以从the和sub中显示任何我想显示的内容，但是在连接其他子表时会出现错误。

SELECT  distinct
        inspections.inspection_id as InspectionId,
        convert(char(3),inspections.Inspection_Type) + ' ' + Inspection_Desc as Inspection,
        inspections.current_status as Current_Status,
        case when left(inspections.current_status,1) = 'X' then 'SCHEDULED' else inspection_status.status_description end as Current_Status_Name,
        convert(varchar(10),max(distinct inspection_history.scheduled_date),101) as 'Last_Scheduled',
        convert(varchar(10),max(distinct inspection_history.inspect_date),101) as 'Last_Inspected',
        inspections.inspection_type,
        case when (inspections.current_status <> 'A' and left(inspections.current_status,1) <> 'X' and left(inspections.current_status,1) <> 'S') then dbo.fn_DependencyCheckMessage(@PermitNum,inspections.inspection_type) else '' end as Dependency_Message,
        case when (inspections.current_status <> 'A' and left(inspections.current_status,1) <> 'X' and left(inspections.current_status,1) <> 'S') and (dbo.fn_DependencyCheck(@PermitNum,inspections.inspection_type)=1 and dbo.fn_CheckTempDeposits(@PermitNum,inspections.inspection_type)=1 and dbo.fn_CertOccCheck(@PermitNum,inspections.inspection_type)=1) then 1 else 0 end as AllowSchedule, 
        inspection_comments.comment_description as comment

    FROM inspections

        inner join permit_arch on permit_arch.permit_id = Inspections.permit_id
        inner join inspection_types on inspection_types.inspection_type = Inspections.inspection_type
        LEFT OUTER JOIN inspection_history ON inspections.inspection_id = inspection_history.inspection_id
        LEFT OUTER JOIN inspection_status ON inspections.current_status = inspection_status.status_code

        LEFT JOIN inspection_history_comment ON inspection_history.insp_hist_id = inspection_history_comment.inspection_history_id
        LEFT JOIN inspection_comments ON inspection_history_comment.comment_code = inspection_comments.comment_code

    WHERE

        permit_arch.permit_number = @PermitNum
        AND
        (inspection_types.internal_only is null or inspection_types.internal_only = 0)
        AND
        (inspection_history.status <> 'D' or inspection_history.status is null)

    GROUP BY permit_arch.permit_number,permit_arch.permit_status, inspections.Inspection_Type, inspection_types.inspection_desc, inspections.current_status,inspection_status.status_description, 
                 inspections.inspection_id

    HAVING Permit_arch.permit_number = @Permitnum and (inspections.current_status <> 'D' or inspections.current_status is null) and inspections.inspection_type <> 34

    ORDER BY inspections.inspection_type ASC

我的加入是最后两个。SSMS错误：

Msg 8120，16级，状态1，过程procWebGetPermitInspectionsForSchedulingA，第20行批处理开始行7 列'inspection_comments.comment_description‘在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。 Msg 145，15级，状态1，过程procWebGetPermitInspectionsForSchedulingA，第10行批处理开始行7 如果指定select DISTINCT，则按项排序必须出现在选择列表中。

我想得到列inspection_comments.description

Answer 1

查询中不需要group by。当在sum, max语句中有一个聚合函数(如select等)时，就使用它。您还没有在查询中在t-a之前提到inner join。我已经在我的查询中包括了这一点。

而且，Left outer join只不过是Left join本身。因此，我在查询中也进行了更改。

试试这个：-

SELECT distinct 
   t-a.col, t-b.col, s-b.col
FROM t-a
   INNER JOIN t-1 ON t-1.id = t-a.id
   LEFT JOIN t-b on t-a.id = t-b.id
   LEFT JOIN s-a on t-b.id=s-a.id
   LEFT JOIN s-b on s-a.id=s-b.id;