Question

sql server, left join on，on附近有语法错误

select cast(Service_Order_No as nvarchar)+CAST(Item_No as nvarchar) as 'Key1',
Service_Order_No, Item_No
from dbo.GH_Billing_ID_TH_MY_IN as A
left join
(select cast([Service Order ID] as nvarchar)+CAST([Service Order Line] as nvarchar) as 'Key2',
[Service Order ID],[Service Order Line]
from dbo.SO_Raw_All as B)
on A.Key1= B.Key2;
提示：
消息 156，级别 15，状态 1，第 8 行
关键字 'on' 附近有语法错误。
谢谢

Answer 1

晕，你没必要加那么多别名 但你最后报错的那句话 ON前面的要别名的

from dbo.SO_Raw_All as B) 别名
on A.Key1= B.Key2;

追问

也是提示Key1无效呢？

追答

引号去掉，引号前的as 也去掉 用空格代替

追问

select A.Service_Order_No,A.Item_No from
(... as 'Key1', 一样 )as A left join (... as 'Key2'一样 ) as B on A.Key1= B.Key2;貌似可以了。之前语句的A是只指表dbo.GH_Billing_ID_TH_MY_IN，上面这样写是不是就是把Key1和后面两列一起作为一个整体‘A’呢？

追答

不行的 你这样就算差的出来 速度也是很慢了 不如写个存储试试

Answer 2

select cast(Service_Order_No as nvarchar)+CAST(Item_No as nvarchar) as 'Key1',
Service_Order_No, Item_No
from dbo.GH_Billing_ID_TH_MY_IN A
left join
(select cast([Service Order ID] as nvarchar)+CAST([Service Order Line] as nvarchar) as 'Key2',
[Service Order ID],[Service Order Line]
from dbo.SO_Raw_All ) B
on A.Key1= B.Key2;

追问

提示说：
消息 207，级别 16，状态 1，第 8 行
列名 'Key1' 无效。
请问是为什么呢？

追答

select (cast(Service_Order_No as nvarchar)+CAST(Item_No as nvarchar) )as 'Key1',
Service_Order_No

这个地方错了
对应的，这个地方也错了
select (cast([Service Order ID] as nvarchar)+CAST([Service Order Line] as nvarchar)) as 'Key2',