一对多关系，按一聚合多

作者：ikmb@163.com QQ:154179812

说明：

表tbChuKuWuZiJiLu与表tbKuCunJiLuBiao为一对多关系，char_CKWZJL_SN和char_KCJLB_SN分别是两表的主键。vchar_XiangGuanRuKuWuZiSN是表tbChuKuWuZiJiLu的外键。

需求为：

tbChuKuWuZiJiLu. float_CKWZJL_YingTiZhongLiang—Sum(tbKuCunJiLuBiao. float_KCJLB_ZhongLiang where vchar_XiangGuanRuKuWuZiSN = tbChuKuWuZiJiLu. char_CKWZJL_SN)

步骤一：

select char_CKWZJL_SN,float_CKWZJL_YingTiShuLiang,vchar_XiangGuanRuKuWuZiSN,char_KCJLB_SN,float_KCJLB_ZhongLiang from tbChuKuWuZiJiLu as t1,tbKuCunJiLuBiao as t2 where t1.char_CKWZJL_SN=t2.vchar_XiangGuanRuKuWuZiSN

结果：

说明：多表tbKuCunJiLuBiao每条记录都显示出来了，一表tbChuKuWuZiJiLu重复。考虑按表tbChuKuWuZiJiLu聚合。

步骤二：

select char_CKWZJL_SN,sum(float_CKWZJL_YingTiShuLiang) as ck,sum(float_KCJLB_ZhongLiang) as rk from tbChuKuWuZiJiLu as t1,tbKuCunJiLuBiao as t2 where t1.char_CKWZJL_SN=t2.vchar_XiangGuanRuKuWuZiSN group by char_CKWZJL_SN

结果：

说明：varchar类型列不能聚合。基本要实现目标。

步骤三：

可以了。

优化临时表：

if object_id('tempdb..#tem1') is not null begin drop table #tem1 end create table #tem1 (sn char(11) primary key,ck int,rk int) insert into #tem1 select char_CKWZJL_SN,sum(float_KCJLB_ZhongLiang) as rk from tbChuKuWuZiJiLu as t1,tbKuCunJiLuBiao as t2 where t1.char_CKWZJL_SN=t2.vchar_XiangGuanRuKuWuZiSN group by char_CKWZJL_SN; select tem_1.sn,(ck-rk) as ck_rk from #tem1 as tem_1,tbChuKuWuZiJiLu as temp_ck where ck-rk>0 and tem_1.sn=temp_ck.char_CKWZJL_SN order by (ck-rk) desc; drop table #tem1;

一对多关系，按一聚合多(存储过程)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!