高手请看: 一个SQL问题,急呀!!!!!!!!!!!!!

表1

UserId1 work workTime1
--------------------------
1 A 3
1 B 2
2 C 1
3 D 4


表2

UserId2 work workTime2
---------------------------
1 A 2
2 C 1
3 B 2
4 C 2

我现在要得到这样的表,请问怎么实现?

Work UserId WorkTime1   UserId2 WorkTime2
---------------------------------
A 1 3 1 2
B 1 2 3 2
C 2 1 2 1
C null null 4 2
D 3 4 null null

------解决方案--------------------
select
a.Work,
b.UserId,
b.WorkTime1,
c.UserId2,
c.WorkTime2
from
(select distinct Work from 表1 union select distinct Work from 表2) a
left join
表1 b
on
a.Work=b.Work
left join
表2 c
on
a.Work=c.Work
------解决方案--------------------
CREATE TABLE #T1(UserId1 int,[work] char(2),workTime1 int)
CREATE TABLE #T2(UserId2 int,[work] char(2),workTime2 int)
INSERT INTO #T1
SELECT 1, 'A ',3 UNION ALL
SELECT 1, 'B ',2 UNION ALL
SELECT 2, 'C ',1 UNION ALL
SELECT 3, 'D ',4
INSERT INTO #T2
SELECT 1, 'A ',2 UNION ALL
SELECT 2, 'C ',1 UNION ALL
SELECT 3, 'B ',2 UNION ALL
SELECT 4, 'C ',2

SELECT A.[work],A.UserId1,A.WorkTime1,B.UserId2,B.WorkTime2
FROM #T1 AS A INNER JOIN
#T2 AS B ON A.[UserId1]=B.[UserId2] AND A.[work]=B.[work] OR (A.[workTime1]=B.[workTime2] AND A.[work]=B.[work])
UNION ALL
SELECT A.[work],NULL,NULL,B.UserId2,B.WorkTime2
FROM #T1 AS A INNER JOIN
#T2 AS B ON A.[work]=B.[work] AND (A.[UserId1] <> B.[UserId2] AND A.[workTime1] <> B.[workTime2])
UNION ALL
SELECT [work],UserId1,WorkTime1,NULL,NULL
FROM #T1
WHERE NOT EXISTS(SELECT 1 FROM #T2 WHERE #T2.[work]=#T1.[work])


DROP TABLE #T1,#T2