27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT
a.Id,a.Name,SUM(IIF(ISNULL(b.[Money]),0,b.[Money])) AS Total,a.Tel
FROM TableA AS a
LEFT JOIN TableB AS b ON b.NID=a.ID
GROUP BY a.Id,a.Name,a.Tel
--测试数据
if not object_id(N'Tempdb..#TableA') is null
drop table #TableA
Go
Create table #TableA([Id] int,[Name] nvarchar(22),[Tel] NVARCHAR(20))
Insert #TableA
select 1,N'张三',13800001111 union all
select 2,N'李四',13800002222 union all
select 3,N'王五',13800003333
GO
if not object_id(N'Tempdb..#TableB') is null
drop table #TableB
Go
Create table #TableB([Id] int,[Nid] int,[Money] int,[time] Date)
Insert #TableB
select 1,1,200,'2018-3-17' union all
select 2,1,300,'2018-4-12' union all
select 3,3,100,'2018-3-5' union all
select 4,3,500,'2018-5-7' union all
select 5,3,200,'2018-5-10'
Go
--测试数据结束
SELECT #TableA.Id,
Name,
ISNULL(SUM(Money),0) AS Total,
Tel
FROM #TableA
LEFT JOIN #TableB
ON #TableB.Nid = #TableA.Id
AND MONTH(time) IN (4,5,6)
GROUP BY #TableA.Id,
Name,
Tel
ORDER BY #TableA.Id
-- 使用子查询
-- 试试这个,Access 的子查询不知道行不行。
select A.ID,
A.NAME,
(select SUM(b.Money) from B
where A.ID = B.NID
AND B.time between '2018-04-01 00:00:00' and '2018-06-30 23:59:59') Total,
A.TEL
from A