按间隔对数据进行分组 [英] Grouping data by interval
本文介绍了按间隔对数据进行分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
亲爱的,
我对SQL Server 2008有一点疑问。
这是我的数据:
NAME DUE_IN VALUE
----------------------
A 15 15000
B 30 23000
A 45 15200
A 78 45000
C 45 13000
D 10 45700
D 45 45800
A 78 10220
B 10 78900
A 46 98700
A 35 45000
我想把它归为一类:
NAME DUE_IN VALUE
A(< 15)sum(value)
A(15-30)sum(value)
A (> 30)sum(value)
B(< 15)sum(value)
B(15-30)sum(value)
B(> 30)sum(value) )
C(< 15)sum(value)
C(15-30)sum(value)
C(> 30)sum(value)
D(< ; 15)总和(价值)
D(15-30)总和(价值)
D(> 30)总和(价值)
我怎么能这样做......我根本不知道.. :(
解决方案
一个选项如下:
> < pre lang =sql> 选择 namee,' ( < 15)',sum(vvalue)来自 #te 其中 due_in< = 15 group by namee union
选择 namee,' (15-30)',sum(vvalue)来自 #te 其中 due_in 15 和 30 group by namee union
选择 namee,' > 30',sum(vvalue)来自 #te 其中 due_in> 30 group by namee
检查:
CREATE TABLE TestData
(
[NAME] VARCHAR ( 10 ),
DUE_IN INT ,
[VALUE] INT
)
INSERT INTO TestData([NAME],DUE_IN,[VALUE])
VALUES (' A', 15 , 15000 ),
(' B',< span class =code-digit> 30 , 23000 ),
(' A', 45 ,< span class =code-digit> 15200 ),
(' A', 78 , 45000 ),
(' C', 45 , 13000 ),
(' D', 10 , 45700 ),
(' D', 45 , 45800 ),
(' A', 78 ,< span class =code-digit> 10220 ),
(' B', 10 , 78900 ),
('' A', 46 , 98700 ) ,
(' A', 35 , 45000 )
DECLARE @ tmp 表(RangeId INT IDENTITY ( 1 , 1 ),RangeName VARCHAR ( 10 ),RangeMin INT ,RangeMax INT )
INSERT INTO @ tmp (RangeName,Rang eMin,RangeMax)
VALUES (' > ; 15', 0 , 14 ),(' 15-30', 15 , 30 ),(' > 30', 31 , 999999 )
SELECT t1 。[Name],t2.RangeName AS DUE_IN,SUM(t1。[Value]) AS [SumOfValue ]
FROM TestData As t1
INNER JOIN @ tmp AS t2 ON t 1. [DUE_IN]> = t2.RangeMin AND t1。[DUE_IN]< = t2.RangeMax
GROUP BY t1。[Name],t2.RangeName,t2.RangeId
ORDER BY t1。[Name],t2.RangeId
结果:
名称DUE_IN SumOfValue
A 15-30 15000
A> 30 214120
B> 15 78900
B 15-30 23000
C> 30 13000
D> 15 45700
D> 30 45800
Dear all,
I have a little question about SQL Server 2008.
This is My Data:
NAME DUE_IN VALUE
----------------------
A 15 15000
B 30 23000
A 45 15200
A 78 45000
C 45 13000
D 10 45700
D 45 45800
A 78 10220
B 10 78900
A 46 98700
A 35 45000
I want to group it into this:
NAME DUE_IN VALUE
A (<15) sum(value)
A (15-30) sum(value)
A (>30) sum(value)
B (<15) sum(value)
B (15-30) sum(value)
B (>30) sum(value)
C (<15) sum(value)
C (15-30) sum(value)
C (>30) sum(value)
D (<15) sum(value)
D (15-30) sum(value)
D (>30) sum(value)
How can i do that.. I have no idea at all.. :(
解决方案
one option is as follows:
>select namee, '(<15)', sum(vvalue) from #te where due_in <= 15 group by namee union select namee, '(15-30)', sum(vvalue) from #te where due_in between 15 and 30 group by namee union select namee, '>30', sum(vvalue) from #te where due_in > 30 group by namee
Check this:
CREATE TABLE TestData ( [NAME] VARCHAR(10), DUE_IN INT, [VALUE] INT ) INSERT INTO TestData ([NAME], DUE_IN, [VALUE]) VALUES('A', 15, 15000), ('B', 30, 23000), ('A', 45, 15200), ('A', 78, 45000), ('C', 45, 13000), ('D', 10, 45700), ('D', 45, 45800), ('A', 78, 10220), ('B', 10, 78900), ('A', 46, 98700), ('A', 35, 45000) DECLARE @tmp TABLE(RangeId INT IDENTITY(1,1), RangeName VARCHAR(10), RangeMin INT, RangeMax INT) INSERT INTO @tmp (RangeName, RangeMin, RangeMax) VALUES('>15', 0, 14),('15-30', 15, 30), ('>30', 31, 999999) SELECT t1.[Name], t2.RangeName AS DUE_IN, SUM(t1.[Value]) AS [SumOfValue] FROM TestData As t1 INNER JOIN @tmp AS t2 ON t1.[DUE_IN]>=t2.RangeMin AND t1.[DUE_IN]<=t2.RangeMax GROUP BY t1.[Name], t2.RangeName, t2.RangeId ORDER BY t1.[Name], t2.RangeId
Result:
Name DUE_IN SumOfValue A 15-30 15000 A >30 214120 B >15 78900 B 15-30 23000 C >30 13000 D >15 45700 D >30 45800
这篇关于按间隔对数据进行分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文