在SQL中获取组中另一行的值的总和。 [英] Get sum of values to another row in a group in SQL.

问题描述

我有一个表Parameter_List，其中包含字段Parameter_ID，Parameters_Name，Target_Value，GroupID。

值可以是

I have a table Parameter_List with fields Parameter_ID,Parameters_Name, Target_Value, GroupID.
The values can be

Parameter_ID    Parameters_Name Target_Value    GroupID Sequence_No
1   Para_1  1000    2   1
2   Para_2  2000    2   2
3   Para_3  3000    2   3
4   Para_4  NULL    2   4
5   Para_5  5000    2   5

6   Para_1  1450    3   1
7   Para_2  1200    3   2
8   Para_4  NULL    3   3
9   Para_5  3000    3   4

10  Para_2  3000    4   1
11  Para_3  4000    4   2
12  Para_4  NULL    4   3

如您所见，Para_4的Target_Value永远是NULL。

现在当我想显示记录时，我希望Para_4的值为该组的Para_4以上参数的Target_Value的总和。

For例如，对于GroupID 2，Para_4的值应该是前3行的总和。表有一个Sequence_No列。所以Para_4应该将Target_Value作为该特定组中它上面所有行的总和。

它应该按GroupID分组。因此结果看起来像

As you can see, Target_Value for Para_4 is always NULL.
Now when I want to display the records, I want the value for Para_4 as the sum of Target_Value of the parameteres above Para_4 for that Group.
For example, for GroupID 2, value of Para_4 should be sum of first 3 rows. Table has a Sequence_No column. So Para_4 should have Target_Value as a sum of all rows above it in that specific group.
It should group by GroupID. So the result would look like

Para_1  1000    2
Para_2  2000    2
Para_3  3000    2
Para_4  6000    2
Para_5  5000    2

Para_1  1450    3
Para_2  1200    3
Para_4  2650    3
Para_5  3000    3

我尝试了什么：



我试图使用UNION，但我不认为是一个很好的解决方案（或者是？）。它如何影响性能？

What I have tried:

I tried to use UNION but I don't think it is a good solution (or is it?). How does it affect the performance?

推荐答案

可以通过使用下面的sql语句来实现：

It's possible to achieve by using below sql statement:

SELECT T.Parameters_Name, SUM(T.Target_Value) AS Target_Value, T.GroupID
FROM (
  SELECT 'Para_4' AS Parameters_Name, Target_Value, GroupID
  FROM Tmp
  WHERE Parameters_Name IN ('Para_1', 'Para_2', 'Para_3')
  UNION ALL
  SELECT Parameters_Name, Target_Value, GroupID
  FROM Tmp
  WHERE Parameters_Name <> 'Para_4'
) AS T
GROUP BY T.Parameters_Name, T.GroupID

SQL小提琴 [ ^ ]



注意：如果 Para_4 总是 NULL ，则 WHERE SELECT 语句中的code>子句是多余的。

SQL Fiddle[^]

Note: In case when Para_4 is always NULL, a WHERE clause in second SELECT statement is redundant .

Declare @table table(Parameter_ID int,Parameters_Name varchar(10),Target_Value int,GroupID int,Sequence_No int)
insert into @table values
(1,   'Para_1',  1000,    2,   1),
(2,   'Para_2',  2000,    2,   2),
(3,   'Para_3',  3000,    2,   3),
(4,   'Para_4',  NULL,    2,   4),
(5,   'Para_5',  5000,    2,   5),
								 
(6,   'Para_1',  1450,    3,   1),
(7,   'Para_2',  1200,    3,   2),
(8,   'Para_4',  NULL,    3,   3),
(9,   'Para_5',  3000,    3,   4),
								 
(10,  'Para_2',  3000,    4,   1),
(11,  'Para_3',  4000,    4,   2),
(12,  'Para_4',  NULL,    4,   3)

select *,sum(Target_Value)over(partition by GroupID order by Sequence_No)Tot from @table order by Parameter_ID

这篇关于在SQL中获取组中另一行的值的总和。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！