多表组合统计汇总的问题

[tatty_bad]

TableA

Id Name
1 张三
2 李四
3 王五

TableB

Id Nid Money
1 1 200
2 1 300
3 3 100
4 3 500
5 3 200

需要汇总的数据内容

Id Name Total
1 张三 500
2 李四 0
3 王五 800

我现在的语句，没办法把李四的这条数据给显示出来，只能出来1和3

select b.id,a.name,sum(b.money) from table a left join table b on a.id=b.nid group by b.id,a.name,sum(b.money)

[卖水果的net]

-- 使用子查询
select A.ID, A.NAME, (select SUM(b.Money) from B where A.ID = B.NID) Total
from A

[二月十六]

--测试数据
if not object_id(N'Tempdb..#TableA') is null
	drop table #TableA
Go
Create table #TableA([Id] int,[Name] nvarchar(22))
Insert #TableA
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五'
GO
if not object_id(N'Tempdb..#TableB') is null
	drop table #TableB
Go
Create table #TableB([Id] int,[Nid] int,[Money] int)
Insert #TableB
select 1,1,200 union all
select 2,1,300 union all
select 3,3,100 union all
select 4,3,500 union all
select 5,3,200
Go
--测试数据结束
SELECT #TableA.Id,
       Name,
       ISNULL(SUM(Money),0) AS Total
FROM #TableA
    LEFT JOIN #TableB
        ON #TableB.Nid = #TableA.Id
GROUP BY #TableA.Id,
         Name
ORDER BY #TableA.Id		 

[tiegenZ]

思路：
1.先统计TableB中的数据,根据Nid分组求和
2. TableA 与TableB左联接 ，这样TableA中的数据即使在TableB中没有对应的数据也都会显示出来
3. 利用函数将null替换成0
这样就可以实现你需要的查询效果了

具体SQL如下:
select A.Name,isNull(B.total,0) as'Total'
from #TableA A
left join
(select Nid,SUM(Money) as 'total' from #TableB group by Nid) B
on A.id=B.Nid;

效果图：

[文盲老顾]

with a as (
	select 1 as id,N'张三' as name union all
	select 2,N'李四' union all
	select 3,N'王五'
),b as (
	select 1 as id,1 as nid,200 as money union all
	select 2,1,300 union all
	select 3,3,100 union all
	select 4,3,500 union all
	select 5,3,200
)
select * from a
cross apply(
	select isnull(sum(money),0) as total,count(0) as 次数,isnull(avg(money),0) as 平均消费 from b where nid=a.id
) b

[qch2015qch]

with a as (
    select 1 as id,N'张三' as name union all
    select 2,N'李四' union all
    select 3,N'王五'
),b as (
    select 1 as id,1 as nid,200 as money union all
    select 2,1,300 union all
    select 3,3,100 union all
    select 4,3,500 union all
    select 5,3,200
)
select a.name, SUM(isnull(b.money,0)) money from a left join b
	on a.id = b.nid
group by a.id, a.name