求各交叉表的查询

Question

select a.字段1，b.字段2,
    b.字段3，b.字段4，b.字段5
from 表1 a inner join 表2 b
   on a.字段1=b.字段3

Answer 1

如果表2的a1，a2，a3，a4是固定的，就用以下语句。--建立测试环境
Create Table 表1
(字段1 Varchar(10),
 字段2 Varchar(10))
Create Table 表2
(字段3 Varchar(10),
 字段4 Varchar(10),
 字段5 Varchar(10))
--插入数据
Insert 表1 Values('id1',     'name1')
Insert 表1 Values('id2',     'name2')
Insert 表1 Values('id3',     'name3')
Insert 表1 Values('id4',     'name4')Insert 表2 Values('id1',    'a1',       'b1')
Insert 表2 Values('id1',    'a2',       'b2')
Insert 表2 Values('id1',    'a3',       'b3')
Insert 表2 Values('id1',    'a4',       'b4')
Insert 表2 Values('id2',    'a2',       'b3')
Insert 表2 Values('id3',    'a1',       'b2')
--测试
Select 
A.*,
Max(Case When B.字段4='a1' Then 字段5 End) As a1,
Max(Case When B.字段4='a2' Then 字段5 End) As a2,
Max(Case When B.字段4='a3' Then 字段5 End) As a3, 
Max(Case When B.字段4='a4' Then 字段5 End) As a4
from 表1 A 
Left Join 表2 B
On A.字段1=B.字段3
Group By A.字段1,A.字段2
--删除测试环境
Drop Table 表1,表2
--结果
/*
字段1 字段2 a1 a2 a3 a4
id1 name1 b1 b2 b3 b4
id2 name2 NULL b3 NULL NULL
id3 name3 b2 NULL NULL NULL
id4 name4 NULL NULL NULL NULL
*/

Answer 2

如果不固定就用动态SQL语句--建立测试环境
Create Table 表1
(字段1 Varchar(10),
 字段2 Varchar(10))
Create Table 表2
(字段3 Varchar(10),
 字段4 Varchar(10),
 字段5 Varchar(10))
--插入数据
Insert 表1 Values('id1',     'name1')
Insert 表1 Values('id2',     'name2')
Insert 表1 Values('id3',     'name3')
Insert 表1 Values('id4',     'name4')Insert 表2 Values('id1',    'a1',       'b1')
Insert 表2 Values('id1',    'a2',       'b2')
Insert 表2 Values('id1',    'a3',       'b3')
Insert 表2 Values('id1',    'a4',       'b4')
Insert 表2 Values('id2',    'a2',       'b3')
Insert 表2 Values('id3',    'a1',       'b2')
--测试
Declare @S Nvarchar(2000)
Set @S='Select A.*'
Select @S=@S+N',Max(Case When B.字段4='''+字段4+N''' Then 字段5 End) As '+字段4
from (Select Distinct 字段4 from 表2) A Order By 字段4
Set @S=@S+N' from 表1 A Left Join 表2 B On A.字段1=B.字段3 Group By A.字段1,A.字段2'
--Select @S
EXEC(@S)
--删除测试环境
Drop Table 表1,表2
--结果
/*
字段1 字段2 a1 a2 a3 a4
id1 name1 b1 b2 b3 b4
id2 name2 NULL b3 NULL NULL
id3 name3 b2 NULL NULL NULL
id4 name4 NULL NULL NULL NULL
*/