select
a.字段1,a.字段2,a.字段3,
max(case b.字段b when 1 then b.字段c end),
max(case b.字段b when 2 then b.字段c end)
from
表A a
left join
表B b
on
a.字段1=b.字段A
group by
a.字段1,a.字段2,a.字段3
a.字段1,a.字段2,a.字段3,
max(case b.字段b when 1 then b.字段c end),
max(case b.字段b when 2 then b.字段c end)
from
表A a
left join
表B b
on
a.字段1=b.字段A
group by
a.字段1,a.字段2,a.字段3
insert into @a select 'A',11,12
insert into @a select 'B',21,22
insert into @a select 'C',31,32
insert into @a select 'D',41,42
insert into @a select 'E',51,52
declare @b table(字段a varchar(8),字段b int,字段c varchar(8))
insert into @b select 'A',1,'E'
insert into @b select 'A',2,'F'
insert into @b select 'B',1,'F'
insert into @b select 'B',2,'E'
insert into @b select 'C',1,'E'
insert into @b select 'C',2,'F'
insert into @b select 'D',1,'F'select
a.字段1,a.字段2,a.字段3,
max(case b.字段b when 1 then b.字段c end) as 字段4,
max(case b.字段b when 2 then b.字段c end) as 字段5
from
@A a
left join
@B b
on
a.字段1=b.字段A
group by
a.字段1,a.字段2,a.字段3/*
字段1 字段2 字段3 字段4 字段5
-------- ----------- ----------- -------- --------
A 11 12 E F
B 21 22 F E
C 31 32 E F
D 41 42 F NULL
E 51 52 NULL NULL
*/
a.字段1,a.字段2,a.字段3,
字段4=(select top 1 字段c from 表B b1 where 字段a=字段1 and 字段2=1),
字段5=(select top 1 字段c from 表B b1 where 字段a=字段1 and 字段2=2)
from
表A a
declare @A table(字段1 varchar(1),字段2 int,字段3 int)
insert @A
select 'A',11,12 union all
select 'B',21,22 union all
select 'C',31,32 union all
select 'D',41,42 union all
select 'E',51,52
--原始数据:@B
declare @B table(字段a varchar(1),字段b int,字段c varchar(2))
insert @B
select 'A',1,'E ' union all
select 'A',2,'F ' union all
select 'B',1,'F ' union all
select 'B',2,'E ' union all
select 'C',1,'E ' union all
select 'C',2,'F ' union all
select 'D',1,'F 'select a.*,字段4=b.字段c,字段5=c.字段c
from @A a
left join (select * from @B where 字段b=1) b
on a.字段1=b.字段a
left join (select * from @B where 字段b=2) c
on a.字段1=c.字段a/*
字段1 字段2 字段3 字段4 字段5
A 11 12 E F
B 21 22 F E
C 31 32 E F
D 41 42 F NULL
E 51 52 NULL NULL
*/
select a.* , isnull(t1.字段4,'') 字段4, isnull(t2.字段5,'') 字段5 from a
left join (select a.* , b.字段c 字段4 from a,b where a.字段1 = b.字段a and b.字段b = 1) t1 on a.字段1 = t1.字段a
left join (select a.* , b.字段c 字段5 from a,b where a.字段1 = b.字段a and b.字段b = 2) t2 on a.字段1 = t2.字段a