主表
id name age
1 陈 25
2 李 30
子表
id phone address
1 13889885377 沈阳
1 13478458521 沈阳
2 13978451254 大连
1 18254784125 沈阳
子表同一个人 地址是一样
当我要传入参数为1的时候
得到
id name age phone1 phone2 phone3 address
1 陈 25 13889885377 13478458521 18254784125 沈阳
当我要传入参数为1的时候
得到
id name age phone1 phone2 phone3 address
1 陈 25 13978451254 大连
------解决思路----------------------
use tempdb
go
create table main(id int,name varchar(30),age int)
insert into main
select 1,'陈',25 union all
select 2,'李',30
create table sub(id int,phone varchar(30),[address] varchar(50))
insert into sub
select 1,'13889885377','沈阳' union all
select 1,'13478458521','沈阳' union all
select 2,'13978451254','大连' union all
select 1,'18254784125','沈阳'
declare @id int
set @id=2
;with sel as
(select a.id,a.name,a.age,b.phone,b.[address],row_number() over(partition by a.id order by getdate()) as rn
from main a
join sub b on a.id=b.id
)
select id,name,age,max(case when rn=1 then phone else '' end) as phone1,
max(case when rn=2 then phone else '' end) as phone2,
max(case when rn=3 then phone else ''end) as phone3,
[address]
from sel
where id=@id
group by id,name,age,address