请问这种 汇总的SQL语句怎么写比较好?

[wzqx]

表A 是有userid ,data1,data2,data3,data4,data5
表B 是有userid ,money 两个字段

为省略起见 现在表A只写到 data 实际上可能会去到data100

现在想得到的结果是表A的全部内容 再加上表B按userid的汇总.也就是大概是这个样子


表A
userid data1 data2 data3 data4 data5
1 1 2 3 4 5
2 6 6 6 6 6
3 7 7 7 7 7



表B
userid money
1 1
1 2
1 3
2 4
2 5
2 6
3 3

要求查询的结果:
userid data1 data2 data3 data4 data5 money
1 1 2 3 4 5 6
2 6 6 6 6 6 15
3 7 7 7 7 7 3

如果 数据字段 非常多例如去到data100 那么这个sql语句会不会非常的长?

[xiaoshengnen]

select a.*,b.money from 表A a,表B b where a.userid=b.userid

[xiaoshengnen]

select a.*,b.money from 表A a,(select userid,sum(money) money from 表B group by userid) b where a.userid=b.userid

[中国风]

SELECT  a.* ,
        ISNULL(B.[money], 0) AS [money]
FROM    A
        LEFT JOIN ( SELECT  userid ,
                            SUM([money]) AS [money]
                    FROM    B
                    GROUP BY userid
                  ) AS B ON a.userid = B.userid;

[中国风]

当B没对应的USERID记录时显示为0 如果B存在记录才显示可用把LEFT JOIN 改为 INNER JOIN

[中国风]

也可这样用

SELECT  a.* ,
        ISNULL(B.[money], 0) AS [money]
FROM    A
        CROSS APPLY ( SELECT    SUM([money]) AS [money]
                      FROM      B
                      WHERE     userid = a.userid
                    ) AS B;