一对多关系，按一聚合多

作者：[e-mail] 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 nullbegin drop table #tem1endselect char_CKWZJL_SN ,avg(float_CKWZJL_YingTiShuLiang) as ck,sum(float_KCJLB_ZhongLiang) as rk into #tem1 from tbChuKuWuZiJiLu as t1,tbKuCunJiLuBiao as t2 where t1.char_CKWZJL_SN=t2.vchar_XiangGuanRuKuWuZiSN group by char_CKWZJL_SN;select tem_1.char_CKWZJL_SN,ck,rk,(ck-rk) as ck_rk from #tem1 as tem_1,tbChuKuWuZiJiLu as temp_ck where ck-rk>0 and tem_1.char_CKWZJL_SN=temp_ck.char_CKWZJL_SN order by (ck-rk) desc;drop table #tem1; 结果：

 

可以了。

 

优化临时表：

if object_id('tempdb..#tem1') is not nullbegindrop table #tem1endcreate table #tem1 (sn char(11) primary key,ck int,rk int) insert into #tem1 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;select tem_1.sn,ck,rk,(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;

 

 

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