SQL 怎么实现环比

1、表结构

CREATE TABLE `ringratio` (

  `id` int(11) NOT NULL AUTO_INCREMENT  COMMENT '主键id，自增',

  `num` int(11) NOT NULL  COMMENT '具体数据',

  `year` smallint(6) NOT NULL  COMMENT '年份',

  `month` smallint(5) unsigned NOT NULL  COMMENT '月份',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='环比实现表';

2、其中数据（包括13个月，为了演示跨年的时候计算）

insert into ringratio (id, num, year, month) values (1, 100, 2017, 1);

insert into ringratio (id, num, year, month) values (2, 130, 2017, 2);

insert into ringratio (id, num, year, month) values (3, 110, 2017, 3);

insert into ringratio (id, num, year, month) values (4, 90, 2017, 4);

insert into ringratio (id, num, year, month) values (5, 150, 2017, 5);

insert into ringratio (id, num, year, month) values (6, 190, 2017, 6);

insert into ringratio (id, num, year, month) values (7, 210, 2017, 7);

insert into ringratio (id, num, year, month) values (8, 120, 2017, 8);

insert into ringratio (id, num, year, month) values (9, 130, 2017, 9);

insert into ringratio (id, num, year, month) values (10, 180, 2017, 10);

insert into ringratio (id, num, year, month) values (11, 110, 2017, 11);

insert into ringratio (id, num, year, month) values (12, 220, 2017, 12);

insert into ringratio (id, num, year, month) values (13, 330, 2018, 1);

3、查询sql

select (  thismonth.num-lastmonth.num)/lastmonth.num '环比增长' ,thismonth.year ‘年’,thismonth.month ‘月’

from ringratio thismonth,ringratio lastmonth 

where

 case when thismonth.month >1 then  thismonth.year=lastmonth.year and thismonth.month = lastmonth.month+1 

when thismonth.month = 1 then  thismonth.year=lastmonth.year+1 and  lastmonth.month=12  end

简单说明

就是本月数据和前一个月的数据作为两个表进行内连接，之后进行查询

本sql适合在数据量较小的表（如数据中间表、汇总表）中进行查询