--sorryselect a.*,b.费用 from ( select AA.客户ID,substring(','+AA.房间ID,bb.id+1,charindex(',',','+AA.房间ID+',',bb.id+1)-bb.id-1) as 房间ID from lease aa,#dali bb where substring(','+AA.房间ID,bb.id,500) like ',_%' ) a left jion cost b on a.客户ID=b.客户ID and a.房间ID=b.房间ID
select a.*,b.费用 from #dali a left OUTER JOIN cost b on a.客户ID=b.客户ID and a.房间ID=b.房间ID
select a.*,b.费用 from ( 你上面的语句 ) a left jion cost b on a.客户ID=b.客户ID and a.房间ID=b.房间ID
select a.*,b.费用 from ( select AA.客户ID,substring(','+AA.房间ID,bb.id+1,charindex(',',','+AA.房间ID+',',bb.id+1)-bb.id-1) as 房间ID from lease aa,#dali bb where substring(','+AA.房间ID,bb.id,500) like ',_%' ) a left jion cost b on a.客户ID=b.客户ID and a.房间ID=b.房间ID order by a.客户ID,a.房间ID
from
( select AA.客户ID,substring(','+AA.房间ID,bb.id+1,charindex(',',','+AA.房间ID+',',bb.id+1)-bb.id-1) as 房间ID from lease aa,#dali bb where substring(','+AA.房间ID,bb.id,500) like ',_%' ) a
left jion cost b on a.客户ID=b.客户ID and a.房间ID=b.房间ID
from #dali a left OUTER JOIN cost b on a.客户ID=b.客户ID and a.房间ID=b.房间ID
from
( 你上面的语句 ) a
left jion cost b on a.客户ID=b.客户ID and a.房间ID=b.房间ID
我还有两个表
一个为 “客户名称”客户ID 客户名称
1 test1
2 test2
3 test3
4 test4另一个为 “房间名称”房间ID 房间名称
1 room1
2 room2
3 room3
4 room4
5 room5
8 room8
请问如何将上边查询结果中的房间ID等转化为名称,即最后查询结果为
客户名称 房间名称 费用
test1 room1 500
test2 room2 (NULL)
test2 room3 200
test3 room3 (NULL)
test3 room4 (NULL)
test3 room5 (NULL)
test4 room8 100
from
( select AA.客户ID,substring(','+AA.房间ID,bb.id+1,charindex(',',','+AA.房间ID+',',bb.id+1)-bb.id-1) as 房间ID from lease aa,#dali bb where substring(','+AA.房间ID,bb.id,500) like ',_%' ) a
left jion cost b on a.客户ID=b.客户ID and a.房间ID=b.房间ID
order by a.客户ID,a.房间ID
我还有两个表
一个为 “客户名称”客户ID 客户名称
1 test1
2 test2
3 test3
4 test4另一个为 “房间名称”房间ID 房间名称
1 room1
2 room2
3 room3
4 room4
5 room5
8 room8
请问如何将上边查询结果中的房间ID等转化为名称,即最后查询结果为
客户名称 房间名称 费用
test1 room1 500
test2 room2 (NULL)
test2 room3 200
test3 room3 (NULL)
test3 room4 (NULL)
test3 room5 (NULL)
test4 room8 100