帮忙写一个SQL语句:
表结构如下:MasterTable:主键是iID。
ID Value
1 1a
2 2a
subTable:主键是: ID+SubID,其中ID是外键,同MasterTable的ID关联。
ID SubID Value2
1 01 101a
1 02 102a
2 01 201a
2 02 202a
需要取得如下结果:ID value subIDValue01 subIDValue01
1 1a 101a 102a
2 2a 201a 202a
尽量写个简单的SQL,不要太长。那种拿子查询拼出来的我能写出一个,但不知道是不是最优解。
会不会是空或者还有03
if object_id('[MasterTable]') is not null drop table [MasterTable]
go
create table [MasterTable]([ID] int,[Value] varchar(2))
insert [MasterTable]
select 1,'1a' union all
select 2,'2a'
if object_id('[subTable]') is not null drop table [subTable]
go
create table [subTable]([ID] int,[SubID] varchar(2),[Value2] varchar(4))
insert [subTable]
select 1,'01','101a' union all
select 1,'02','102a' union all
select 2,'01','201a' union all
select 2,'02','202a'
---查询---
select
a.ID,
a.value,
b.Value2 as subIDValue01,
c.Value2 as subIDValue02
from
MasterTable a
left join
subTable b on a.ID=b.id and b.SubID='01'
left join
subTable c on a.ID=c.id and c.SubID='02'
---结果---
ID value subIDValue01 subIDValue02
----------- ----- ------------ ------------
1 1a 101a 102a
2 2a 201a 202a(2 行受影响)
left join
(select s.id as s11,
max(case subid when '01' then value2 end) as s12,
max(case subid when '02' then value2 end) as s13
from subTable s group by id)s1
on m.id=s1.s11
max(case n.SubID when '01' then n.Value2 else '' end) subIDValue01,
max(case n.SubID when '02' then n.Value2 else '' end) subIDValue02
from MasterTable m , subTable n
where m.id = n.id
group by m.ID , m.Value
a.ID,
a.value,
b.Value2 as subIDValue01,
c.Value2 as subIDValue02
from
MasterTable a
left join
subTable b on a.ID=b.id and b.SubID='01'
left join
subTable c on a.ID=c.id and c.SubID='02'
select a.id,a.value,b.value2,b.subid
from [MasterTable] a inner join [subTable] b on a.id=b.id) ab
pivot(
max([Value2])
for subid
in ([01],[02])
) piv