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

表1

UserId1workworkTime1
--------------------------
1A3
1B2
2C1
3D4


表2

UserId2workworkTime2
---------------------------
1A2
2C1
3B2
4C2

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

WorkUserIdWorkTime1 UserId2WorkTime2
---------------------------------
A1312
B1232
C2121
Cnullnull42
D34nullnull

[解决办法]
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