求助：多表关联查询，
sql server
有三个表，结构相同
表1
ID USER_DM PROJ_NAME SCORE CREATE_DATE
1 A 科目1 90 2012-02-02
2 B 科目2 90 2012-02-02
3 C 科目3 90 2012-02-02
4 D 科目4 90 2012-02-02
5 B 科目4 90 2012-02-02
表2
ID USER_DM PROJ_NAME SCORE CREATE_DATE
1 A 科目1 90 2012-02-02
2 G 科目2 90 2012-02-02
3 A 科目3 90 2012-02-02
4 E 科目5 90 2012-02-02
5 F 科目6 90 2012-02-02
表3
ID USER_DM PROJ_NAME SCORE CREATE_DATE
1 F 科目1 90 2012-02-02
2 D 科目2 90 2012-02-02
3 C 科目3 90 2012-02-02
4 E 科目5 90 2012-02-02
现在想汇总为一个表
USER_DM1 PROJ_NAME2 SCORE3 USER_DM2 PROJ_NAME2 SCORE2 USER_DM3 PROJ_NAME3 SCORE3
A 科目1 90 A 科目1 90 -- -- --
B 科目2 90 -- -- -- -- --- -
求大神 sql怎么写
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-04-07 17:22:08
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[表1]
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([ID] int,[USER_DM] varchar(1),[PROJ_NAME] varchar(5),[SCORE] int,[CREATE_DATE] datetime)
insert [表1]
select 1,'A','科目1',90,'2012-02-02' union all
select 2,'B','科目2',90,'2012-02-02' union all
select 3,'C','科目3',90,'2012-02-02' union all
select 4,'D','科目4',90,'2012-02-02' union all
select 5,'B','科目4',90,'2012-02-02'
--> 测试数据：[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([ID] int,[USER_DM] varchar(1),[PROJ_NAME] varchar(5),[SCORE] int,[CREATE_DATE] datetime)
insert [表2]
select 1,'A','科目1',90,'2012-02-02' union all
select 2,'G','科目2',90,'2012-02-02' union all
select 3,'A','科目3',90,'2012-02-02' union all
select 4,'E','科目5',90,'2012-02-02' union all
select 5,'F','科目6',90,'2012-02-02'
--> 测试数据：[表3]
if object_id('[表3]') is not null drop table [表3]
go
create table [表3]([ID] int,[USER_DM] varchar(1),[PROJ_NAME] varchar(5),[SCORE] int,[CREATE_DATE] datetime)
insert [表3]
select 1,'F','科目1',90,'2012-02-02' union all
select 2,'D','科目2',90,'2012-02-02' union all
select 3,'C','科目3',90,'2012-02-02' union all
select 4,'E','科目5',90,'2012-02-02'
--------------开始查询--------------------------
select a.[USER_DM] AS [USER_DM1],a.[PROJ_NAME] AS [PROJ_NAME1],a.[SCORE] AS [SCORE1],
b.[USER_DM] AS [USER_DM2],b.[PROJ_NAME] AS [PROJ_NAME2],b.[SCORE] AS [SCORE2],
c.[USER_DM] AS [USER_DM3],c.[PROJ_NAME] AS [PROJ_NAME3],c.[SCORE] AS [SCORE3]
from [表1] a LEFT JOIN [表2] b ON a.[USER_DM]=b.[USER_DM]
LEFT JOIN [表3] c ON a.[USER_DM]=c.[USER_DM]
----------------结果----------------------------
/*
USER_DM1 PROJ_NAME1 SCORE1 USER_DM2 PROJ_NAME2 SCORE2 USER_DM3 PROJ_NAME3 SCORE3
-------- ---------- ----------- -------- ---------- ----------- -------- ---------- -----------
A 科目1 90 A 科目1 90 NULL NULL NULL
A 科目1 90 A 科目3 90 NULL NULL NULL
B 科目2 90 NULL NULL NULL NULL NULL NULL
C 科目3 90 NULL NULL NULL C 科目3 90
D 科目4 90 NULL NULL NULL D 科目2 90
B 科目4 90 NULL NULL NULL NULL NULL NULL
*/
