我在 SQL Server 2008 数据库中有一个表,其中包含一个数字列,我想按 1 到 10 的比例排列该列。
这是一个示例,其中列(Scale
)是我想用SQL完成的
Name Count (Scale)
----------------------
A 19 2
B 1 1
C 25 3
D 100 10
E 29 3
F 60 7
在我上面的例子中,最小和最大计数是 1 和 100(这可能每天都不同)。
我想得到每条记录所属的数字。
1 = 0-9
2 = 10-19
3 = 20-29 and so on...
它必须是动态的,因为这些数据每天都在变化,所以我不能像这样使用带有静态数字的 WHERE
子句:WHEN Count Between 0 and 10...
试试这个,但请注意,从技术上讲,值 100 不在 90-99 范围内,因此可能应该归类为 11,因此为什么值 60 的等级为 6 而不是 7:
SQL 小提琴
MS SQL Server 2008 架构设置:
查询 1:
create table #scale
(
Name Varchar(10),
[Count] INT
)
INSERT INTO #scale
VALUES
('A', 19),
('B', 1),
('C', 25),
('D', 100),
('E', 29),
('F', 60)
SELECT name, [COUNT],
CEILING([COUNT] * 10.0 / (SELECT MAX([Count]) - MIN([Count]) + 1 FROM #Scale)) AS [Scale]
FROM #scale
结果:
| NAME | COUNT | SCALE |
|------|-------|-------|
| A | 19 | 2 |
| B | 1 | 1 |
| C | 25 | 3 |
| D | 100 | 10 |
| E | 29 | 3 |
| F | 60 | 6 |
这让你得到答案,60 变成 7,因此 100 等于 11:
SELECT name, [COUNT],
CEILING([COUNT] * 10.0 / (SELECT MAX([Count]) - MIN([Count]) FROM #Scale)) AS [Scale]
FROM #scale
WITH MinMax(Min, Max) AS (SELECT MIN(Count), MAX(Count) FROM Table1)
SELECT Name, Count, 1 + 9 * (Count - Min) / (Max - Min) AS Scale
FROM Table1, MinMax
Scale
列设为PERSISTED COMPUTED
列,如下所示:
alter table test drop column Scale
ALTER TABLE test ADD
Scale AS (case when Count between 0 and 9 then 1
when Count between 10 and 19 then 2
when Count between 20 and 29 then 3
when Count between 30 and 39 then 4
when Count between 40 and 49 then 5
when Count between 50 and 59 then 6
when Count between 60 and 69 then 7
when Count between 70 and 79 then 8
when Count between 80 and 89 then 9
when Count between 90 and 100 then 10
end
)PERSISTED
GO
演示
select ntile(10) over (order by [count])