表一:
ID Name
1 hwj1
2 hwj2表二:
ParentId Money(int)
1 50
1 70
2 30
2 40描述:表二ParentId关联表一ID现在想用一SQL查询实现以下结果:ID Name TotalMoney
1 hwj1 120
2 hwj2 70这个SQL怎么写,越简单越好!
ID Name
1 hwj1
2 hwj2表二:
ParentId Money(int)
1 50
1 70
2 30
2 40描述:表二ParentId关联表一ID现在想用一SQL查询实现以下结果:ID Name TotalMoney
1 hwj1 120
2 hwj2 70这个SQL怎么写,越简单越好!
--TRY:
select a.id,a.[name],sum(isnull([money],0)) totalmoney from tb01 a inner join tb02 b on
a.id=b.parentid group by a.id,a.[name]
declare @tb01 table(id int,[name] varchar(50))
insert into @tb01
select 1,'hwj1' union all
select 2,'hwj2' declare @tb02 table(parentid int,[money] money)
insert into @tb02
select 1,50 union all
select 1,70 union all
select 2,30 union all
select 2,40
select a.id,a.[name],sum(isnull([money],0)) totalmoney from @tb01 a inner join @tb02 b on
a.id=b.parentid group by a.id,a.[name]/*
1 hwj1 120.00
2 hwj2 70.00
*/
create table tb1 (id int,name varchar(50))
insert into tb1
select 1,'hwj1' union all
select 2,'hwj2' create table tb2 (parentid int,money int)
insert into tb2
select 1,50 union all
select 1,70 union all
select 2,30 union all
select 2,40
go--SQL语句
select * , TotalMoney = (select sum(money) from tb2 where tb2.parentid = tb1.id) from tb1drop table tb1 , tb2--结果
/*id name TotalMoney
----------- -------------------------------------------------- -----------
1 hwj1 120
2 hwj2 70(所影响的行数为 2 行)
*/
select * , TotalMoney = (select sum(money) from tb2 where tb2.parentid = tb1.id) from tb1这个应该最简单
from MoneyT,dbo.employee
where MoneyT.paraentid=dbo.employee.id
group by id