MySQL案例 1.6.2 用列表展示与上一年的比较结果

作者: SQL开发者

问题:

准备数据:

create table sales (
    year int not null,
    sale int not null
);
insert into sales(year, sale)
            values(1990, 50),
                   (1991, 51),
                    (1992, 52),
                    (1993, 52),
                    (1994, 50),
                    (1995, 50),
                    (1996, 49),
                    (1997, 55);
/*
 year | sale
------+------
 1990 |   50
 1991 |   51
 1992 |   52
 1993 |   52
 1994 |   50
 1995 |   50
 1996 |   49
 1997 |   55
(8 rows)

问题: 比较每一年与上一年的销量, 并输出如下结果:

 year | sale | var
------+------+-----
 1990 |   50 | ▬
 1991 |   51 | ↑
 1992 |   52 | ↑
 1993 |   52 | →
 1994 |   50 | ↓
 1995 |   50 | →
 1996 |   49 | ↓
 1997 |   55 | ↑

解法:

解法一(使用关联子查询):

select s1.year,
    s1.sale,
    case when s1.sale = (select sale from sales s2 where s2.year+1 = s1.year) then '→'
        when s1.sale > (select sale from sales s2 where s2.year+1 = s1.year) then '↑'
        when s1.sale < (select sale from sales s2 where s2.year+1 = s1.year) then '↓'
    else '▬' end var
    from sales s1;
/*
 year | sale | var
------+------+-----
 1990 |   50 | ▬
 1991 |   51 | ↑
 1992 |   52 | ↑
 1993 |   52 | →
 1994 |   50 | ↓
 1995 |   50 | →
 1996 |   49 | ↓
 1997 |   55 | ↑
*/

解法二(使用外连接):

select s1.year, s1.sale,
    case when s1.sale = s2.sale then '→'
         when s1.sale > s2.sale then '↑'
        when s1.sale < s2.sale then '↓'
    else '▬' end var
    from sales s1 left outer join sales s2
        on s1.year = s2.year + 1;
/*
 year | sale | var
------+------+-----
 1990 |   50 | ▬
 1991 |   51 | ↑
 1992 |   52 | ↑
 1993 |   52 | →
 1994 |   50 | ↓
 1995 |   50 | →
 1996 |   49 | ↓
 1997 |   55 | ↑
*/

附加题:

上一题的时间轴是竖着的, 现在改成横着的展示试一下:

 趋势 | 1990 | 1991 | 1992 | 1993 | 1994 | 1995 | 1996 | 1997
------+------+------+------+------+------+------+------+------
 年份 | ▬    | ↑    | ↑    | →    | ↓    | →    | ↓    | ↑
(1 row)

解法:

select '年份' "趋势",
        case when sum(case when s1.year = 1990 and s1.sale - s2.sale > 0 then 1 else null end) > 0 then '↑'
             when sum(case when s1.year = 1990 and s1.sale - s2.sale < 0 then -1 else null end) < 0 then '↓'
             when sum(case when s1.year = 1990 and s1.sale - s2.sale = 0 then 0 else null end) < 0 then '↓'
        else '▬' end   "1990",
        case when sum(case when s1.year = 1991 and s1.sale - s2.sale > 0 then 1 else null end) > 0 then '↑'
             when sum(case when s1.year = 1991 and s1.sale - s2.sale < 0 then -1 else null end) < 0 then '↓'
             when sum(case when s1.year = 1991 and s1.sale - s2.sale = 0 then 0 else null end) = 0 then '→'
        else '▬' end "1991",
        case when sum(case when s1.year = 1992 and s1.sale - s2.sale > 0 then 1 else null end) > 0 then '↑'
             when sum(case when s1.year = 1992 and s1.sale - s2.sale < 0 then -1 else null end) < 0 then '↓'
             when sum(case when s1.year = 1992 and s1.sale - s2.sale = 0 then 0 else null end) = 0 then '→'
        else '▬' end "1992",
        case when sum(case when s1.year = 1993 and s1.sale - s2.sale > 0 then 1 else null end) > 0 then '↑'
             when sum(case when s1.year = 1993 and s1.sale - s2.sale < 0 then -1 else null end) < 0 then '↓'
             when sum(case when s1.year = 1993 and s1.sale - s2.sale = 0 then 0 else null end) =  0 then '→'
        else '▬' end "1993",
        case when sum(case when s1.year = 1994 and s1.sale - s2.sale > 0 then 1 else null end) > 0 then '↑'
             when sum(case when s1.year = 1994 and s1.sale - s2.sale < 0 then -1 else null end) < 0 then '↓'
             when sum(case when s1.year = 1994 and s1.sale - s2.sale = 0 then 0 else null end) =  0 then '→'
        else '▬' end "1994",
        case when sum(case when s1.year = 1995 and s1.sale - s2.sale > 0 then 1 else null end) > 0 then '↑'
             when sum(case when s1.year = 1995 and s1.sale - s2.sale < 0 then -1 else null end) < 0 then '↓'
             when sum(case when s1.year = 1995 and s1.sale - s2.sale = 0 then 0 else null end) =  0 then '→'
        else '▬' end "1995",
        case when sum(case when s1.year = 1996 and s1.sale - s2.sale > 0 then 1 else null end) > 0 then '↑'
             when sum(case when s1.year = 1996 and s1.sale - s2.sale < 0 then -1 else null end) < 0 then '↓'
             when sum(case when s1.year = 1996 and s1.sale - s2.sale = 0 then 0 else null end) =  0 then '→'
        else '▬' end "1996",
        case when sum(case when s1.year = 1997 and s1.sale - s2.sale > 0 then 1 else null end) > 0 then '↑'
             when sum(case when s1.year = 1997 and s1.sale - s2.sale < 0 then -1 else null end) < 0 then '↓'
             when sum(case when s1.year = 1997 and s1.sale - s2.sale = 0 then 0 else null end) =  0 then '→'
        else '▬' end "1997"
    from sales s1
        left outer join sales s2
            on s1.year = s2.year+1;
/*
 趋势 | 1990 | 1991 | 1992 | 1993 | 1994 | 1995 | 1996 | 1997
------+------+------+------+------+------+------+------+------
 年份 | ▬    | ↑    | ↑    | →    | ↓    | →    | ↓    | ↑
(1 row)
*/

🐒 这样写好累啊 ,有更好的方式吗?

更多推荐

更多
  • MySQL实战宝典-27分布式事务:我们到底要不要使用2PC? 27 分布式事务:我们到底要不要使用 2PC?计。但是我们一直在回避分布式数据库中最令人头疼的问题,那就是分布式事务。 今天,我们就来学习分布式事务的概念,以及如何在海量互联网业务中实现它。 分布式事务概念 事务的概念相信你已经非
  • MySQL实战宝典-11索引出错:请理解CBO的工作原理 11 索引出错:请理解 CBO 的工作原理的使用已经有了一定的了解。 而在实际工作中,我也经常会遇到一些同学提出这样的问题:MySQL 并没有按照自己的预想来选择索引,比如创建了索引但是选择了全表扫描,这肯定是 MySQL 数据库的
  • MySQL实战宝典-21数据库备份:备份文件也要检查! 21 数据库备份:备份文件也要检查!据库可以进行切换(比如 20 讲基于复制或者 InnoDB Cluster 技术的高可用解决方案)。 除了高可用设计外,对架构师来说,还要做好备份架构的设计。因为我们要防范意外情况的发生,比如黑客删
  • MySQL实战宝典-15MySQL复制:最简单也最容易配置出错 15 MySQL 复制:最简单也最容易配置出错结构设计、索引设计。对业务开发的同学来说,掌握这些内容已经能很好地面向业务逻辑进行编码工作了。 但是业务需要上线,所以除了表和索引的结构设计之外,你还要做好高可用的设计。因为在真实的生产环
  • MySQL实战宝典-07表的访问设计:你该选择SQL还是NoSQL? 07 表的访问设计:你该选择 SQL 还是 NoSQL?的访问选型。这样一来,字段类型选择 + 物理存储设计 + 表的访问设计,就完成了表结构设计的所有内容。 前面 6 讲,我演示的都是通过 SQL 的方式对表进行访问,但从 MySQ
  • MySQL实战宝典-17高可用设计:你怎么活用三大架构方案? 17 高可用设计:你怎么活用三大架构方案?是为了铺垫 MySQL 数据库的高可用架构设计。因为复制是高可用的基础,但只用复制同步数据又远远不够,你还要结合自己的业务进行高可用设计。 同时,高可用也不仅仅是数据库的事情,你要从业务的全流
  • MySQL实战宝典-06表压缩:不仅仅是空间压缩 06 表压缩:不仅仅是空间压缩用户表、订单表。既然我们已经掌握了表的逻辑设计,那这一讲就继续学习不同业务表的物理存储设计。 据我观察,很多同学不会在表结构设计之初就考虑存储的设计,只有当业务发展到一定规模才会意识到问题的严重性。而物理
  • MySQL实战宝典-19高可用套件:选择这么多,你该如何选? 19 高可用套件:选择这么多,你该如何选?求,通过无损半同步复制的方式进行三园区的同城容灾设计,以及三地务中心的跨城容灾设计。 但是当数据库发生宕机时,MySQL 的主从复制并不会自动地切换,这需要高可用套件对数据库主从进行管理。
  • MySQL实战宝典-09索引组织表:万物皆索引 09 索引组织表:万物皆索引步深入了解 MySQL 的 B+ 树索引的具体使用,这一讲我想和你聊一聊 MySQL InnoDB 存储引擎的索引结构。 InnoDB 存储引擎是 MySQL 数据库中使用最为广泛的引擎,在海量大并发的 OL
  • MySQL实战宝典-12JOIN连接:到底能不能写JOIN? 12 JOIN 连接:到底能不能写 JOIN?优工作。但除了单表的 SQL 语句,还有两大类相对复杂的 SQL,多表 JOIN 和子查询语句,这就要在多张表上创建索引,难度相对提升不少。 而很多开发人员下意识地认为 JOIN 会降低
  • 近期文章

    更多
    文章目录

      推荐作者

      更多