为什么在尝试连接 SQL Server 查询中的表时出现错误

加里·格拉斯普尔

我写了下面的查询来收集与卡车司机收入有关的数据。主查询和连接查询都正常工作。但是，当我将它们组合起来时，出现错误

消息 4104，级别 16，状态 1，第 37 行
无法绑定多部分标识符“tn.ctripnumber”

与相关tRev.ctripnumber = tn.ctripnumber。我曾尝试使用收入表名称以及 tRev 别名。

select 
    tn.ctripnumber as "Load Number",
    tr.cresourcedesc as "Carrier/Driver",
    tr.resourcetype as "Resource Type",
    tn.cfirmorigin as "Pickup Origin",
    tn.corigaddress as "Origin Address",
    tn.corigcity as "Origin City",
    tn.corigstate as "Origin State",
    tn.corigzip as "Origin Zip",
    tn.dorigappt_start as "Scheduled Arrival Date",
    tn.dorigappt_end as "Scheduled Late Arrival Date",
    tn.dtripstartdate as "Actual Arrival Date",
    datediff(minute,tn.dorigappt_start,tn.dtripstartdate) as "Arrival Diff",
    (case when tn.dtripstartdate-tn.dorigappt_start < 0 then 'Early' when tn.dorigappt_start-tn.dtripstartdate = 0 then 'On-time' else 'Late' end) as Arrival_Rank,
    tn.cfirmdestination as "Delivery Destination",
    tn.cdestaddress as "Destination Address",
    tn.cdestcity as "Destination City",
    tn.cdeststate as "Destination State",
    tn.cdestzip as "Destination Zip",
    tn.ddestappt_start as "Scheduled Delivery Date",
    tn.ddestappt_end as "Scheduled Late Delivery Date",
    tn.ddeliverydate as "Actual Delivery Date",
    datediff(minute,tn.ddestappt_start,tn.ddeliverydate) as "Delivery Diff",
    (case when tn.ddeliverydate-tn.ddestappt_start < 0 then 'Early' when tn.ddeliverydate-tn.ddestappt_start = 0 then 'On-time' else 'Late' end) as "Delivery Rank",
    tn.nideadheadmiles as "Deadhead Miles",
    tn.niloadedmiles as "Loaded Miles",
    tn.nideadheadmiles + tn.niloadedmiles as "Total Miles"
from 
    tripnumber tn,tripresources tr
inner join
    (select  
         r.ctripnumber, sum(Revenue_Subtotal) as "Revenue" 
     from 
         (select 
              r.ctripnumber, r.cmethod, sum(r.curevenue) as Revenue_Subtotal
          from 
              revenuedtl r 
          where 
              r.cmethod in (select distinct r.cmethod from revenuedtl r)
          group by 
              r.ctripnumber, r.cmethod) r
     --where r.cmethod like 'BROK%'
     group by 
         r.ctripnumber) tRev on tRev.ctripnumber = tn.ctripnumber
where 
    tn.ctripnumber = tr.ctripnumber
    --and tn.ctripnumber = '324412'
    and tr.resourcetype in ('D','M')
    and tn.dtripstartdate >= '2018-12-01 00:00:00.000'
    and tn.dtripstartdate < '2019-01-01 00:00:00.000'

戈登·利诺夫

这是你的from条款：

from tripnumber tn,
     tripresources tr join
     (select r.ctripnumber, sum(Revenue_Subtotal) as "Revenue" 
      from (select r.ctripnumber, r.cmethod, sum(r.curevenue) as Revenue_Subtotal
            from revenuedtl r 
            where r.cmethod in (select distinct r.cmethod from revenuedtl r)
            group by r.ctripnumber, r.cmethod
           ) r
    --where r.cmethod like 'BROK%'
      group by r.ctripnumber
     ) tRev
     on tRev.ctripnumber = tn.ctripnumber

简单规则：永远不要在FROM子句中使用逗号。始终使用正确、明确、标准的 JOIN语法。

所以，重写这个使用适当的JOIN：

from tripnumber tn join
     tripresources tr
     on tn.ctripnumber = tr.ctripnumber join
     (select r.ctripnumber, sum(Revenue_Subtotal) as "Revenue" 
      from (select r.ctripnumber, r.cmethod, sum(r.curevenue) as Revenue_Subtotal
            from revenuedtl r 
            where r.cmethod in (select distinct r.cmethod from revenuedtl r)
            group by r.ctripnumber, r.cmethod
           ) r
    --where r.cmethod like 'BROK%'
      group by r.ctripnumber
     ) tRev
     on tRev.ctripnumber = tn.ctripnumber

你的错误就会消失。

您的查询失败的技术原因是的范围规则,不同于CROSS JOIN. 您实际上可以替换逗号CROSS JOIN以解决问题。但正确的解决方案是正确表达您的连接。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。