坚持SQL求和 [英] Stuck with SQL summation

问题描述

我有一张看起来有点像这样的桌子

I have a table that looks somewhat like this

hiy_hno, GIO_A1MC_1, GIO_A1MC_2,GIO_A1MC_3, GIO_A1MC_4, GIO_A1MC_5,FWM_ADL1_1, FWM_ADL1_2, FWM_ADL1_3, FWM_ADL1_4, FWM_ADL1_5
13312928 F20001     F10002 F10003 A00008 A00008 1200 0 0 0 0 
13313226 F10002     F10003 A00008 A00008 F20001 0    0 0 0 1198 
13313126 F20001     F10002 F10003 A00008 A00008 1102 0 0 0 0 
13312793 F20001     F10002 F10003 A00008 A00008 1190 0 0 0 0 
13312834 F20001     F10002 F10003 A00008 A00008 1007 0 0 0 0 

现在hiy_hno是关键领域。我需要找到所有数量的F20001元素的总和。元素标识符可以在GIO_A1MC_x字段中的任何一个中，并且在FWM_ADL1_x字段中可以是其对应的值。该问题在第1和第2行中说明，其中元素F20001被放置在不同的列中，因此它们的相应量也被放置在不同的列中。



答案可以自由地在表格结构中包含任何必要的更改，但要注意元素的顺序（及其相应的数量）将始终保持可变的事实。 />


提前致谢。



PS - 伙计这实际上是来自各种材料箱的数据和从它们释放的元素数量。有20个这样的箱子，所以总和（某种情况逻辑）不会是一个优雅的解决方案。

Now hiy_hno is the key field. I need to find the sum of all quantities of "F20001" element. The element identifier may be in any one of GIO_A1MC_x fields and its corresponding value in FWM_ADL1_x field. The problem is illustrated in 1st and 2nd row where the element "F20001" gets placed in different columns and hence their corresponding quantities is also placed in different columns.

Answers are free to include any necessary changes in table structure but with cognizance to the fact that order of elements(and their corresponding quantities) will always remain variable.

Thanks in advance.

P.S. - Guys this is actually data coming from various material bins and the quantity of element released from them.There are 20 such bins, so "sum(some sort of case logic)" wont be an elegant solution.

推荐答案

select sum (
  case
    when GIO_A1MC_1 = 'F20001' then FWM_ADL1_1
    when GIO_A1MC_2 = 'F20001' then FWM_ADL1_2
    when GIO_A1MC_3 = 'F20001' then FWM_ADL1_3
    when GIO_A1MC_4 = 'F20001' then FWM_ADL1_4
    when GIO_A1MC_5 = 'F20001' then FWM_ADL1_5
    else 0
    end)
    from t2

这假设值不能在同一行中出现两次。如果可以，那么你可以使用5个CTE来聚合每组值。

This assumes the value can't appear twice in the same row. If it can, then you could use 5 CTEs to aggregate each set of values.

请看看例子：

Please, have a look at example:

CREATE TABLE #tmp (	hiy_hno INT, GIO_A1MC_1 VARCHAR(30), GIO_A1MC_2 VARCHAR(30),
		GIO_A1MC_3 VARCHAR(30), GIO_A1MC_4 VARCHAR(30), GIO_A1MC_5 VARCHAR(30),
		FWM_ADL1_1 INT, FWM_ADL1_2 INT, FWM_ADL1_3 INT,
		FWM_ADL1_4 INT,FWM_ADL1_5 INT)

INSERT INTO #tmp (hiy_hno, GIO_A1MC_1, GIO_A1MC_2,GIO_A1MC_3, GIO_A1MC_4, GIO_A1MC_5,FWM_ADL1_1, FWM_ADL1_2, FWM_ADL1_3, FWM_ADL1_4, FWM_ADL1_5)
VALUES (13312928, 'F20001', 'F10002', 'F10003', 'A00008', 'A00008', 1200, 0, 0, 0, 0),
(13313226, 'F10002', 'F10003', 'A00008', 'A00008', 'F20001', 0, 0, 0, 0, 1198),
(13313126, 'F20001', 'F10002', 'F10003', 'A00008', 'A00008', 1102, 0, 0, 0, 0), 
(13312793, 'F20001', 'F10002', 'F10003', 'A00008', 'A00008', 1190, 0, 0, 0, 0), 
(13312834, 'F20001', 'F10002', 'F10003', 'A00008', 'A00008', 1007, 0, 0, 0, 0)


SELECT hiy_hno, SUM(COALESCE(FWM_ADL1_1, FWM_ADL1_2, FWM_ADL1_3, FWM_ADL1_4, FWM_ADL1_5)) AS SUM_FWM_ADL_x
FROM #tmp
WHERE COALESCE(GIO_A1MC_1, GIO_A1MC_2,GIO_A1MC_3, GIO_A1MC_4, GIO_A1MC_5) = 'F20001'
GROUP BY hiy_hno

DROP TABLE #tmp

结果：

Result:

hiy_hno		SUM_FWM_ADL_x
13312793	1190
13312834	1007
13312928	1200
13313126	1102

如你所见，价值' F20001'存放在任何地方！



如果您需要我们的帮助，您需要提供有关您的问题的更多详细信息。





我认为你的表应该是这样的：

As you can see, value 'F20001' is stored everywhere!

You need to provide more details about your issue if you want our help.


I think your table should looks like:

CREATE TABLE TableName (
    hiy_hno INT,
    RouteID INT,
    GIOA1MC VARCHAR(30),
    FWM_ADL1 INT
)

您可以通过这种方式从现有表传输数据：

You can transfer data from existing table in that way:

INSERT INTO TableName (hiy_hno, RouteID, GIOA1MC, FWM_ADL1)
SELECT hiy_hno, RouteID, GIOA1MC, FWM_ADL1
FROM (
	SELECT hiy_hno, 1 AS RouteID, GIO_A1MC_1 AS GIOA1MC, FWM_ADL1_1 AS FWM_ADL1
	FROM #tmp
	UNION ALL
	SELECT hiy_hno, 2 AS RouteID, GIO_A1MC_2 AS GIOA1MC, FWM_ADL1_2 AS FWM_ADL1
	FROM #tmp
	UNION ALL
	SELECT hiy_hno, 3 AS RouteID, GIO_A1MC_3 AS GIOA1MC, FWM_ADL1_3 AS FWM_ADL1
	FROM #tmp
	UNION ALL
	SELECT hiy_hno, 4 AS RouteID, GIO_A1MC_4 AS GIOA1MC, FWM_ADL1_4 AS FWM_ADL1
	FROM #tmp
	UNION ALL
	SELECT hiy_hno, 5 AS RouteID, GIO_A1MC_5 AS GIOA1MC, FWM_ADL1_5 AS FWM_ADL1
	FROM #tmp
) AS T	
ORDER BY hiy_hno, RouteID

然后你就可以使用了像这样的查询：

Then you'll be able to use query like that:

SELECT SUM(FWM_ADL1) AS SumOfF20001
FROM TableName
WHERE GIOA1MC = 'F20001'

其他值：

For other values:

SELECT GIOA1MC, SUM(FWM_ADL1) AS SumOfGIOA1MC
FROM TableName
GROUP BY GIOA1MC

这篇关于坚持SQL求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！