问题描述
你好！
这是我的问题.我正在使用带有SSRS的SQL Server 2008R2.
我得到了一个包含两个字段的简单数据集，例如:
Hello !
Here is my problem. I''m working on SQL Server 2008R2 with SSRS.
I got a simple dataset with two fields as for exemple :
Weight | Grade
---------------
50 | 1.20
50 | 1.00
50 | 1.12
50 | 2.34
50 | 1.75
50 | 1.73
而且我想绘制一个图表，其中X轴为等级，Y轴为Sum(Weight).
问题是，我的成绩有很多不同的值，我必须对这些值进行分组，以便为每个范围获得一列.如果我还没有明确表示自己，这就是我想要的:
And I would like to draw a chart with Grade in X axis and Sum(Weight) as Y axis.
The problem is, I''ve got a lot of different values for grades and I have to group the values in order to get one column for each range. If i haven''t made myself clear yet, here is what I want :
Weight
200|
|
150|
|
100|
| |
50 | | |
|_____________|_________________|___|________
0 0.5 1 1.3 1.5 1.7 2 2.5
Grade
也就是说我们有150个介于1和1.3之间，100个介于1.7和2之间...等等.
我尝试使用间隔进行此操作，但它似乎仅适用于标签...我尝试并在Google上搜索了有关如何执行此操作的任何解释.我只能在等级"值上进行舍入"，但这无济于事，因为我希望能够选择划定特定范围的值.
在此先感谢您的帮助！
That is to say we have 150 between 1 and 1.3, 100 between 1.7 and 2... etc.
I tried to do this with the intervals but it seems to be only for labels... I tried and googled without any explanation on how to do that. I can only manage to do a "round" on the "Grade" value, but that''s not helpful as I want to be able to choose the values delimiting a specific range.
Thanks in advance for your help !
推荐答案
SELECT 1.23 [Value], ROUND(1.23,1) AS [RndValue]
UNION ALL
SELECT 1.24 [Value], ROUND(1.24,1) AS [RndValue]
UNION ALL
SELECT 1.25 [Value], ROUND(1.25,1) AS [RndValue]
UNION ALL
SELECT 1.26 [Value], ROUND(1.26,1) AS [RndValue]
UNION ALL
SELECT 1.27 [Value], ROUND(1.27,1) AS [RndValue]
UNION ALL
SELECT 1.28 [Value], ROUND(1.28,1) AS [RndValue]
UNION ALL
SELECT 1.29 [Value], ROUND(1.29,1) AS [RndValue]
UNION ALL
SELECT 1.30 [Value], ROUND(1.30,1) AS [RndValue]
UNION ALL
SELECT 1.31 [Value], ROUND(1.31,1) AS [RndValue]
返回值:
| Value | RndValues |
|---|---|
| 1.23 | 1.20 |
| 1.24 | 1.20 |
| 1.25 | 1.30 |
| 1.26 | 1.30 |
| 1.27 | 1.30 |
| 1.28 | 1.30 |
| 1.29 | 1.30 |
| 1.30 | 1.30 |
| 1.31 | 1.30 |
您可以使用模数 [ ^ ].
Returned values:
| Value | RndValues |
|---|---|
| 1.23 | 1.20 |
| 1.24 | 1.20 |
| 1.25 | 1.30 |
| 1.26 | 1.30 |
| 1.27 | 1.30 |
| 1.28 | 1.30 |
| 1.29 | 1.30 |
| 1.30 | 1.30 |
| 1.31 | 1.30 |
You can "play" with modulo[^] too.
SELECT 1.23 [Value], ROUND(1.23,0) AS [RndValue], CONVERT(INT, 1.23 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.24 [Value], ROUND(1.24,0) AS [RndValue], CONVERT(INT, 1.24 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.25 [Value], ROUND(1.25,0) AS [RndValue], CONVERT(INT, 1.25 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.26 [Value], ROUND(1.26,0) AS [RndValue], CONVERT(INT, 1.26 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.27 [Value], ROUND(1.27,0) AS [RndValue], CONVERT(INT, 1.27 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.28 [Value], ROUND(1.28,0) AS [RndValue], CONVERT(INT, 1.28 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.29 [Value], ROUND(1.29,0) AS [RndValue], CONVERT(INT, 1.29 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.30 [Value], ROUND(1.30,0) AS [RndValue], CONVERT(INT, 1.30 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.31 [Value], ROUND(1.31,0) AS [RndValue], CONVERT(INT, 1.31 * 10 % 10) AS [Modulo]
| Value | RndValues | Modulo |
|---|---|---|
| 1.23 | 1.00 | 2 |
| 1.24 | 1.00 | 2 |
| 1.25 | 1.00 | 2 |
| 1.26 | 1.00 | 2 |
| 1.27 | 1.00 | 2 |
| 1.28 | 1.00 | 2 |
| 1.29 | 1.00 | 3 |
| 1.30 | 1.00 | 3 |
| 1.31 | 1.00 | 3 |
...或...
使用案例 [ ^ ]表达式;)
| Value | RndValues | Modulo |
|---|---|---|
| 1.23 | 1.00 | 2 |
| 1.24 | 1.00 | 2 |
| 1.25 | 1.00 | 2 |
| 1.26 | 1.00 | 2 |
| 1.27 | 1.00 | 2 |
| 1.28 | 1.00 | 2 |
| 1.29 | 1.00 | 3 |
| 1.30 | 1.00 | 3 |
| 1.31 | 1.00 | 3 |
... or ...
use CASE[^] expression ;)
USE A_TEST; --my database
DECLARE @sql1 NVARCHAR(1000)
DECLARE @sql2 NVARCHAR(2000)
SET @sql1 = 'SELECT [GradeRng] = ' +
'CASE ' +
' WHEN [Grade] >=1 AND [Grade] <1.3 THEN ''1 to 1.29'' ' +
' WHEN [Grade] >=1.3 AND [Grade] <1.7 THEN ''1.3 to 1.69'' ' +
' WHEN [Grade] >=1.7 AND [Grade] <=2 THEN ''1.7 to 2'' ' +
'END, [Weight] ' +
'FROM Table_2 '
--EXEC (@sql1)
SET @sql2 = 'SELECT DT.[GradeRng], SUM(DT.[Weight]) AS [SumOfWeight] ' +
'FROM (' + @sql1 + ') AS DT ' +
'GROUP BY DT.[GradeRng], DT.[Weight] ' +
'ORDER BY DT.[GradeRng], DT.[Weight] '
EXEC (@sql2)
结果:
| GradeRng | SumOfWeight |
|---|---|
| 1至1.29 | 500 |
| 1.3至1.69 | 1000 |
| 1.7至2 | 1500 |
results:
| GradeRng | SumOfWeight |
|---|---|
| 1 to 1.29 | 500 |
| 1.3 to 1.69 | 1000 |
| 1.7 to 2 | 1500 |
这篇关于将相同值范围内的图表列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！