MSSQL中两表一对多的关系的聚合查询问题
0
悬赏园豆：5
[已解决问题]
解决于 2012-04-19 10:18
A表：
主键ID
1
2
B表：
主键ID A表ID 型号 数量
1 1 AA 4
2 1 AB 3
3 2 CS 2
4 2 DF 5
C表：
主键ID B表ID 重量
1 1 10
2 2 5
3 3 22
4 4 15
写SQL求出A表中每个ID对应的总重量，谢谢
最佳答案
0
select sum(重量) from C where B表ID in(select B表.主键ID from where B表.A表ID=A表.主键ID)
试试这个吧。
收获园豆：5
应该还要乘以数量的啊？
@xu_happy_you: sum就是求重量和啊。你先试试，看看求的总重量是不是的。
@荒野的呼唤:
1 DECLARE @maxBatchId INT --最大的BatchId 2 DECLARE @BW VARCHAR(15) --DECLARE @INVS_NUM INT 3 SET @maxBatchId=(SELECT MAX(BatchId) 4 FROM ShippingLabel.dbo.ShipBatch(nolock)) 5 IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE id=OBJECT_ID(N'tempdb..#temp1') AND TYPE='U') 6 DROP TABLE #temp1 7 --每个invs_num对应包装商品后的总量(临时表) 8 SELECT * INTO #temp1 FROM (SELECT fob_desc,invs_num 9 FROM omsdata.dbo.ins_data(nolock) 10 WHERE invs_num IN(SELECT PackageId 11 FROM ShippingLabel.dbo.Shipping(nolock) 12 WHERE BatchId=@maxBatchId) 13 ) A 14 IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE id=OBJECT_ID(N'tempdb..#temp2') AND TYPE='U') 15 DROP TABLE #temp2 16 --每个invs_num对应单个商品的总量(临时表) 17 SELECT * INTO #temp2 FROM(SELECT SUM(totalWT) totalWT,invs_num 18 FROM (SELECT A.PROD_QTY*B.ut_wt totalWT,A.invs_num 19 FROM omsdata.dbo.invt_log A(nolock) LEFT JOIN omsdata.dbo.inv B(nolock) ON A.PROD_CD=B.PROD_CD 20 WHERE A.invs_num IN(SELECT PackageId 21 FROM ShippingLabel.dbo.Shipping(nolock) 22 WHERE BatchId=@maxBatchId) 23 AND PROD_COMP='1') D 24 GROUP BY invs_num) E 25 26 SELECT t1.invs_num 27 FROM #temp1 t1 LEFT JOIN #temp2 t2 ON t1.invs_num=t2.invs_num 28 WHERE (t1.fob_desc-t2.totalWT)>(t2.totalWT*0.1)
我这样实现了我的需求，但是总觉得 每个invs_num对应单个商品的总量(临时表)这个地方可以简化，直接表一对多的SUM(个数*重量)聚合查询，但是就是报错，不知道怎么回事！
@xu_happy_you: 报什么错误？
@荒野的呼唤:
1 DECLARE @maxBatchId INT --最大的BatchId 2 DECLARE @BW VARCHAR(15) --DECLARE @INVS_NUM INT 3 SET @maxBatchId=(SELECT MAX(BatchId) 4 FROM ShippingLabel.dbo.ShipBatch(nolock)) 5 SELECT fob_desc,invs_num 6 FROM omsdata.dbo.ins_data(nolock) 7 WHERE invs_num IN(SELECT PackageId 8 FROM ShippingLabel.dbo.Shipping(nolock) 9 WHERE BatchId=@maxBatchId) 10 --A.PROD_QTY,A.PROD_CD,A.invs_num 11 SELECT SUM(A.PROD_QTY*B.ut_wt) totalWT,A.* 12 FROM omsdata.dbo.invt_log A(nolock) LEFT JOIN omsdata.dbo.inv B(nolock) ON A.PROD_CD=B.PROD_CD 13 WHERE A.invs_num IN(SELECT PackageId 14 FROM ShippingLabel.dbo.Shipping(nolock) 15 WHERE BatchId=@maxBatchId) AND PROD_COMP='1' 16 GROUP BY A.invs_num
我先前是这样写的聚合查询，但是报错
Msg 8120, Level 16, State 1, Line 11
Column 'omsdata.dbo.invt_log.LOG_DT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
@xu_happy_you: 知道原因了，真是搞昏了，A.*不用要
