星空网 > 软件开发 > 数据库

根据“坐标”生成趋势图

数据库环境:SQL SERVER 2008R2

  有一“坐标”表t,表结构如下:

  id           int,

  num       int

  字段id是序号,递增且连续,字段num是数值类型。id可以看成是坐标轴的横轴,num则跟纵轴有关系,

连续的2行记录,如果后一行的num值比前一行的num值大,则是递增趋势,反之,是递减趋势。要实现的效果如下图1:

根据“坐标”生成趋势图

  实现思路:

  将id=1的“坐标”所在纵轴设定为0,然后遍历后续所有的“坐标”,后面“坐标”的num值比前一个“坐标”的num大的,则纵坐标+1,

否则纵坐标-1。再对整理后的结果集进行行转列,根据纵坐标分组。

1.建表,插入测试数据

根据“坐标”生成趋势图根据“坐标”生成趋势图
CREATE TABLE t(id INT,num INT)INSERT INTO t VALUES(1,1);INSERT INTO t VALUES(2,3);INSERT INTO t VALUES(3,4);INSERT INTO t VALUES(4,7);INSERT INTO t VALUES(5,5);INSERT INTO t VALUES(6,2);INSERT INTO t VALUES(7,6);INSERT INTO t VALUES(8,8);INSERT INTO t VALUES(9,4);INSERT INTO t VALUES(10,0);INSERT INTO t VALUES(11,9);INSERT INTO t VALUES(12,10);INSERT INTO t VALUES(13,12);INSERT INTO t VALUES(14,11);INSERT INTO t VALUES(15,17);INSERT INTO t VALUES(16,4);INSERT INTO t VALUES(17,2);INSERT INTO t VALUES(18,1);

View Code

2.遍历所有“坐标”,生成分组依据

根据“坐标”生成趋势图根据“坐标”生成趋势图
WITH x1 ( id, num, gp )     AS ( SELECT  id ,            num ,            0 AS gp        FROM   t x0        WHERE  id = 1        UNION ALL        SELECT  x0.id ,            x0.num ,            CASE WHEN x0.num > x1.num THEN x1.gp - 1               ELSE x1.gp + 1            END AS gp        FROM   t x0 ,            x1        WHERE  x0.id = x1.id + 1       )

View Code

3.行转列实现最终结果集

根据“坐标”生成趋势图根据“坐标”生成趋势图
SELECT ISNULL(CAST([1] AS VARCHAR(2)), '') [1] ,      ISNULL(CAST([2] AS VARCHAR(2)), '') [2] ,      ISNULL(CAST([3] AS VARCHAR(2)), '') [3] ,      ISNULL(CAST([4] AS VARCHAR(2)), '') [4] ,      ISNULL(CAST([5] AS VARCHAR(2)), '') [5] ,      ISNULL(CAST([6] AS VARCHAR(2)), '') [6] ,      ISNULL(CAST([7] AS VARCHAR(2)), '') [7] ,      ISNULL(CAST([8] AS VARCHAR(2)), '') [8] ,      ISNULL(CAST([9] AS VARCHAR(2)), '') [9] ,      ISNULL(CAST([10] AS VARCHAR(2)), '') [10] ,      ISNULL(CAST([11] AS VARCHAR(2)), '') [11] ,      ISNULL(CAST([12] AS VARCHAR(2)), '') [12] ,      ISNULL(CAST([13] AS VARCHAR(2)), '') [13] ,      ISNULL(CAST([14] AS VARCHAR(2)), '') [14] ,      ISNULL(CAST([15] AS VARCHAR(2)), '') [15] ,      ISNULL(CAST([16] AS VARCHAR(2)), '') [16] ,      ISNULL(CAST([17] AS VARCHAR(2)), '') [17] ,      ISNULL(CAST([18] AS VARCHAR(2)), '') [18]  FROM  ( SELECT  *       FROM   x1      ) AS t1 PIVOT( MAX(num) FOR id IN ( [1], [2], [3], [4], [5], [6],                        [7], [8], [9], [10], [11],                        [12], [13], [14], [15], [16],                        [17], [18] ) )AS t2

View Code

  如果要实现比较逼真的趋势图,用“/”、“\”替代数值,实现下图2的效果,只需在遍历坐标的时候再做些处理即可。

根据“坐标”生成趋势图

  实现的SQL脚本:

根据“坐标”生成趋势图根据“坐标”生成趋势图
WITH  x1 ( id, num, cc, gp )     AS ( SELECT  id ,            num ,            '/' AS cc ,            0 AS gp        FROM   t x0        WHERE  id = 1        UNION ALL        SELECT  x0.id ,            x0.num ,            CASE WHEN x0.num > x1.num THEN '/'               ELSE '\'            END AS cc ,            CASE WHEN x0.num > x1.num THEN x1.gp - 1               ELSE x1.gp + 1            END AS gp        FROM   t x0 ,            x1        WHERE  x0.id = x1.id + 1       )  SELECT ISNULL([1], '') [1] ,      ISNULL([2], '') [2] ,      ISNULL([3], '') [3] ,      ISNULL([4], '') [4] ,      ISNULL([5], '') [5] ,      ISNULL([6], '') [6] ,      ISNULL([7], '') [7] ,      ISNULL([8], '') [8] ,      ISNULL([9], '') [9] ,      ISNULL([10], '') [10] ,      ISNULL([11], '') [11] ,      ISNULL([12], '') [12] ,      ISNULL([13], '') [13] ,      ISNULL([14], '') [14] ,      ISNULL([15], '') [15] ,      ISNULL([16], '') [16] ,      ISNULL([17], '') [17] ,      ISNULL([18], '') [18]  FROM  ( SELECT  id ,            cc ,            gp       FROM   x1      ) AS t1 PIVOT( MAX(cc) FOR id IN ( [1], [2], [3], [4], [5], [6],                        [7], [8], [9], [10], [11], [12],                        [13], [14], [15], [16], [17],                        [18] ) )AS t2

View Code

 




原标题:根据“坐标”生成趋势图

关键词:

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

有什么邮件营销策划方法?如何设计邮件模板?:https://www.kjdsnews.com/a/1550691.html
独立站的运营策略有哪些?自建站经营战略?:https://www.kjdsnews.com/a/1550692.html
合规经营,是企业的“免死金牌”:https://www.kjdsnews.com/a/1550693.html
【税务】个税汇算中关于境外所得的8个热点问题解答:https://www.kjdsnews.com/a/1550694.html
6家快递公司入围《财富》中国500强;空客、波音发布新业绩:https://www.kjdsnews.com/a/1550695.html
论环境的重要性,如何批量养国外买家账号去海淘及Poshmark、Etsy店群:https://www.kjdsnews.com/a/1550696.html
37号文今后是否会更新?一文详解关键信息 :https://www.kjdsnews.com/a/1836441.html
探讨内地人开设香港账户的可行性 :https://www.kjdsnews.com/a/1836442.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流