范围中值的SQL SUM [英] SQL SUM of values in a Range
问题描述
您好,
有人可以帮帮我吗?
我有一张桌子T,有2个字段X和Y
X   Y
$
1   3
4 2
12 8
6 1
22 4
44 7
7 2
11 4
我希望在某个范围内补偿X,例如
范围   ; 总和
0< X< = 10 8
10< X< = 20 12
20< X< = 30 4
30< X< = 40 0
40< X< = 50 7
如何用SQL建立这个?
< blockquote>
下面是一个方法,使用带有行构造函数的派生表。
SELECT RangeName,COALESCE(SUM(TY),0)AS [sum]
FROM(VALUES
('0< X< = 10',0,10)
,('10< X< = 20',10,20)
,( '20< X< = 30',20,30)
,('30,('40 )AS范围(RangeName,LowRangeValue,HighRangeValue)
LEFT JOIN dbo.T ON TX> Ranges.LowRangeValue AND TX< = Ranges.HighRangeValue
GROUP BY RangeName
ORDER BY RangeName;
Hello,
Can somebody help me?
I have a table T with 2 fields X and Y
X Y
1 3
4 2
12 8
6 1
22 4
44 7
7 2
11 4
I want to SUM the X in a certain range like
range sum
0<X<=10 8
10<X<=20 12
20<X<=30 4
30<X<=40 0
40<X<=50 7
How can I establish this with SQL?
Below is one method, using a derived table with row constructors.
SELECT RangeName, COALESCE(SUM(T.Y), 0) AS [sum] FROM (VALUES ('0<X<=10', 0, 10) ,('10<X<=20', 10, 20) ,('20<X<=30', 20, 30) ,('30<X<=40', 30, 40) ,('40<X<=50', 40, 50) ) AS Ranges(RangeName, LowRangeValue, HighRangeValue) LEFT JOIN dbo.T ON T.X > Ranges.LowRangeValue AND T.X <= Ranges.HighRangeValue GROUP BY RangeName ORDER BY RangeName;
这篇关于范围中值的SQL SUM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!