疑难查询
有两个表
表Ａ：字段１，字段２，字段３
A 11 12
B 21 22
C 31 32
D 41 42
E 51 52
表Ｂ：字段a，字段b，字段c
A 1 E
A 2 F
B 1 F
B 2 E
C 1 E
C 2 F
D 1 F
求一个查询，得到表(字段４为表Ｂ中字段b值等于１的，字段５为表Ｂ中字段b值等于２的)
字段１，字段２，字段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
[解决办法]
declare @a table(字段1 varchar(8),字段2 int,字段3 int)
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
*/
