tableA 有三个字段codeA, codeB, codeC
A1, B1, C1
A2 B2, C2
null B3, C3
null C4tableB一个字段为codes,Descript
Codes ,Descript
A1 ,'aaaaa'
A2 ,'cccc'
Bn ' dddd'
Cn ,'CCCCNNN'即tableA 中的每个字段的值,都在tableB中有对应的值.现在呢? 求一sql sentence.要求列表字段结果为:
CodeA,DescriptA (此字段来自TableB),CodeB,DescriptB,CodeC,DescriptC hot to be?
A1, B1, C1
A2 B2, C2
null B3, C3
null C4tableB一个字段为codes,Descript
Codes ,Descript
A1 ,'aaaaa'
A2 ,'cccc'
Bn ' dddd'
Cn ,'CCCCNNN'即tableA 中的每个字段的值,都在tableB中有对应的值.现在呢? 求一sql sentence.要求列表字段结果为:
CodeA,DescriptA (此字段来自TableB),CodeB,DescriptB,CodeC,DescriptC hot to be?
(select descript from tableB where codeA=codes),
select codeB,
(select descript from tableB where codeB=codes),
select codeC,
(select descript from tableB where codeC=codes)
form tableA
;with cte as
(
select a.codeA,b.Descript,rn=ROW_NUMBER() over(order by getdate()) from tableA a join tableB b on a.codeA=b.Codes),cte1 as
(
select a.codeB,b.Descript,rn=ROW_NUMBER()over(order by getdate()) from tableA a join tableB b on a.codeB=b.Codes
),cte2 as
(
select a.codeC,b.Descript,rn=ROW_NUMBER()over(order by getdate()) from tableA a join tableB b on a.codeC=b.Codes
)select x0.codeA,x0.Descript as DescriptA,x1.codeB,x2.Descript as DescriptB,x2.codeC,x2.Descript as DescriptC from cte x0
left join cte1 x1 on x0.rn=x1.rn
left join cte2 x2 on x0.rn=x2.rn