时间戳

首页 > 解决方案 > 如何从主ID加入表并将ID与主ID进行比较

sql - 如何从主ID加入表并将ID与主ID进行比较

问题描述

当我尝试获取结果时，出现以下错误：

消息 4104，级别 16，状态 1，行 2 无法绑定多部分标识符“res.MainStaying”。

select * 
from ConvictedPersons as cp
inner  join (
    select * 
    from Person 
    where ID = cp.id
) as hp 
    on hp.ID is not null

select hrt.NameRU, r2as.ID, res.* 
from PunishmentMeasure as pm  
    inner join CourtDecisions as cd 
        on cd.ID = pm.CourtDecisionID
    inner join Resolutions as res 
        on res.ID = cd.ResolutionID
    left join  ( 
        select top 1 * 
        from Resolutions 
        where MainStayingID = res.MainStayingID 
        order by ID DESC
    ) as r2as 
        on r2as.ID is not null

标签： sqlsql-servertsqljoin

解决方案

From作为(or ) 子句一部分的子查询join无权访问主查询，这就是您收到错误消息的原因。
（顺便说一句，在第一次选择时，您应该得到相同的错误，只是在cp.id而不是res.MainStayingID）

第一个查询可以通过简化来修复：

select * 
from ConvictedPersons as cp
inner join Person
    on Person.ID = cp.Id

left join可以通过将 更改为相关子查询来修复第二个查询：

select  hrt.NameRU, 
        (
            select top 1 ID 
            from Resolutions 
            where MainStayingID = res.MainStayingID 
            order by ID DESC
        ) as ID, 
        res.* 
from PunishmentMeasure as pm  
inner join CourtDecisions as cd 
    on cd.ID = pm.CourtDecisionID
inner join Resolutions as res 
    on res.ID = cd.ResolutionID

推荐阅读