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=utf8COMMENT='环比实现表';

2、其中数据（包括13个月，为了演示跨年的时候计算犬匮渝扮）insert into ringratio (id, num, ye锾攒揉敫ar, 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、查询sqlselect ( thismonth.num-lastmonth.num)/lastmonth.num '环比增长' ,thismonth.year ‘年’,thismonth.month ‘月’from ringratio thismonth,ringratio lastmonthwherecase when thismonth.month >1 then thismonth.year=lastmonth.year and thismonth.month = lastmonth.month+1when thismonth.month = 1 then thismonth.year=lastmonth.year+1 and lastmonth.month=12 end简单说明就是本月数据和前一个月的数据作为两个表进行内连接，之后进行查询本sql适合在数据量较小的表（如数据中间表、汇总表）中进行查询