求一个语句SQL查询波动
有一个表,字段3个,
GroupID GroupName Value
1 a 34
1 b 45
1 c 490
2 d 34
2 e 45
2 f 3.4
3 g 45
3 h 39
3 i 4400
.......
可以看出Value中,有一些值,明显发生了波动,基本值至少扩大10倍(缩小10倍)
请问如何写语句,可以达到这个结果
GroupID GroupName result(结果能看出以下意思就行,不必一样)
1 c 比前值大概增加10倍
2 f 比前值大概缩小10倍
3 i 比前值大概增加100倍
[解决办法]
MSSQL2005及以上版本:
CREATE TABLE t1( id INT, name VARCHAR(5), value DECIMAL(18,1))INSERT INTO t1SELECT 1, 'a', 34 UNION ALLSELECT 1, 'b', 45 UNION ALLSELECT 1, 'c', 490 UNION ALLSELECT 2, 'd', 34 UNION ALLSELECT 2, 'e', 45 UNION ALLSELECT 2, 'f', 3.4 UNION ALLSELECT 3, 'g', 45 UNION ALLSELECT 3, 'h', 39 UNION ALLSELECT 3, 'i', 4400SELECT * FROM t1;WITH AAA AS( SELECT ROW_NUMBER() OVER(ORDER BY name) AS rowindex,* FROM t1),BBB AS( SELECT *,(SELECT value FROM AAA WHERE rowindex=A1.rowindex+1) AS value1 FROM AAA AS A1),CCC AS( SELECT *,value1/value AS AC FROM BBB )SELECT *,CASE WHEN AC>=100 THEN '比前值大概增加100倍' WHEN AC>=10 AND AC<100 THEN '比前值大概增加10倍' WHEN AC<=0.1 THEN '比前值大概缩小10倍' END AS [说明]FROM CCC WHERE AC>=10 OR AC<=0.1-------------------------------rowindex id name value value1 AC 说明2 1 b 45.0 490.0 10.88888888888888888888 比前值大概增加10倍3 1 c 490.0 34.0 0.06938775510204081632 比前值大概缩小10倍5 2 e 45.0 3.4 0.07555555555555555555 比前值大概缩小10倍6 2 f 3.4 45.0 13.23529411764705882352 比前值大概增加10倍8 3 h 39.0 4400.0 112.82051282051282051282 比前值大概增加100倍