22,209
社区成员
发帖
与我相关
我的任务
分享
--借用#1的演示数据
select a.name, total = a.total+b.total
from #ta a join (select cid, total=sum(total1) from #tb group by cid) b on a.id = b.cid
name total
---- -----------
张三 32
李四 25
王 65
(3 行受影响)
--> 测试数据: #ta
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (id int,name varchar(4),total int)
insert into #ta
select 1,'张三',10 union all
select 2,'李四',15 union all
select 3,'王',30
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,cid int,name varchar(4),total1 int)
insert into #tb
select 1,1,'张三',10 union all
select 2,1,'张三',12 union all
select 3,2,'李四',10 union all
select 4,3,'王',15 union all
select 5,3,'王',10 union all
select 6,3,'王',10
select name,total=sum(total)
from
(
select * from #ta
union all
select cid,name,total1 from #tb
)t
group by name
order by total desc
name total
---- -----------
王 65
张三 32
李四 25
(3 行受影响)