表Project 项目
projectId cost
10001 10000
10002 20000
10003 5000表InvestList 投资列表
projectId Investment
10001 2000
10001 3000
10002 1000求结果, 就是查询出所有项目 已被投资了多少钱,没被投资的显示0
projectId cost Investment
10001 10000 5000
10002 20000 1000
10003 5000 0
projectId cost
10001 10000
10002 20000
10003 5000表InvestList 投资列表
projectId Investment
10001 2000
10001 3000
10002 1000求结果, 就是查询出所有项目 已被投资了多少钱,没被投资的显示0
projectId cost Investment
10001 10000 5000
10002 20000 1000
10003 5000 0
from Project a
left join (select projectId,sum(Investment) as Investment from InvestList group by projectId)b
on a.projectId=b.projectId
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-12 15:06:05
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[Project]
if object_id('[Project]') is not null drop table [Project]
go
create table [Project]([projectId] int,[cost] int)
insert [Project]
select 10001,10000 union all
select 10002,20000 union all
select 10003,5000
--> 测试数据:[InvestList]
if object_id('[InvestList]') is not null drop table [InvestList]
go
create table [InvestList]([projectId] int,[Investment] int)
insert [InvestList]
select 10001,2000 union all
select 10001,3000 union all
select 10002,1000
--------------开始查询--------------------------
select a.*,ISNULL(b.[Investment],0)[Investment]
from [Project] a LEFT JOIN (SELECT [projectId],SUM([Investment])[Investment] FROM [InvestList] GROUP BY [projectId])b ON a.[projectId]=b.[projectId]
----------------结果----------------------------
/*
projectId cost Investment
----------- ----------- -----------
10001 10000 5000
10002 20000 1000
10003 5000 0
*/
;with Project(projectId,cost) as
(
select 10001,10000
union all select 10002,20000
union all select 10003,5000
),
InvestList(projectId,Investment) as
(
select 10001,2000
union all select 10001,3000
union all select 10002,1000
)select a.*,ISNULL(b.Investment,0) as Investment
from Project a
left join (select projectId,sum(Investment) as Investment from InvestList group by projectId)b
on a.projectId=b.projectId/*
projectId cost Investment
10001 10000 5000
10002 20000 1000
10003 5000 0
*/