34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb1(id int,zonge int)
insert into tb1 select 1,100
insert into tb1 select 2,200
insert into tb1 select 3,300
create table tb2(id int,tb1_id int,name varchar(10))
insert into tb2 select 1,1,'王五'
insert into tb2 select 2,1,'小四'
insert into tb2 select 3,2,'大三'
insert into tb2 select 4,3,'張三'
insert into tb2 select 5,3,'李四'
select tb2.*, convert(numeric(18,1),1.0*isnull(tb1.zonge,0)/a.num) as zonge
from tb2
left join tb1
on tb2.tb1_id=tb1.id
left join
(select tb1_id,count(*) as num from tb2 group by tb1_id) a
on tb2.tb1_id=a.tb1_id
/*
id tb1_id name zonge
----------- ----------- ---------- --------------------
1 1 王五 50.0
2 1 小四 50.0
3 2 大三 200.0
4 3 張三 150.0
5 3 李四 150.0
*/
drop table tb1,tb2