请进！，求数据合并的SQL语句！

[duoshanx]

A区消费单

userID year pay1
3707123 2001 100
3707123 2002 200

B区消费单

userID year pay2
3707123 2003 3000
3707123 2002 2000

想做view,结果如下：

userId year pay1 pay2
3707123 2001 100
3707123 2002 200 2000
3707123 2003 3000

[pengdali]

select userID,[year],sum(pay1) pay1,sum(pay2) pay2 from (
select userID,[year],pay1,null pary2 from A区消费单
union all
select userID,[year],null pay1,pary2 from A区消费单) tem group by userID,[year]

[pengdali]

select userID,[year],sum(pay1) pay1,sum(pay2) pay2 from (
select userID,[year],pay1,null pary2 from A区消费单
union all
select userID,[year],null pay1,pary2 from B区消费单) tem group by userID,[year]

[pengdali]

或：

select isnull(a.userID,b.userID) userID,isnull(a.[year],b.[year]) [year],a.pay1,b.pay2 from A区消费单 a full join B区消费单 b on a.userID=b.userID and a.[year]=b.[year]

[duoshanx]

谢谢!十分的感谢! !!!!
已经是3.0了？
563 ？!
靠!晕!

[duoshanx]

如果是多个表的话，是不是用第二个方法(select isnull.....)不行呢？

我是多个表，用的是第一个方法

[duoshanx]

SELECT ISNULL(a.userId, ISNULL(c.userId, b.userId)) AS userID, ISNULL(a.[year],
ISNULL(c.[year], b.[year])) AS year, a.pay1, b.pay2, c.pay3
FROM dbo.bb b FULL OUTER JOIN
dbo.cc c ON b.userId = c.userId AND b.[year] = c.[year] FULL OUTER JOIN
dbo.aa a ON c.[year] = a.[year] AND c.userId = a.userId AND b.userId = a.userId AND
b.[year] = a.[year]

这是三个表!

[pengdali]

第二个麻烦些。

select isnull(isnull(a.userID,b.userID),c.userid) userID,isnull(isnull(a.[year],b.[year]),c.[year]) [year],a.pay1,b.pay2,c.pay3 from A区消费单 a full join B区消费单 b on a.userID=b.userID and a.[year]=b.[year] full join C区消费单 c on a.userID=c.userID and a.[year]=c.[year]

[duoshanx]

我第二个方法做的太麻烦了，好处只有一个，可以不分isnull的顺序，但没有用：)
再次感谢3.0!