如何写这个分组计算环比的ysql语句

时间: 2019-10-27 12:33:49     标签: mysql     

创建表

CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `name` text ,
  `year` int(2) ,
  `num` int(5) ,
  PRIMARY KEY (`id`)
);

写入数据

INSERT INTO tb (id, name,year,num) VALUES ('1', 'a','19', 500);
INSERT INTO tb (id, name,year,num) VALUES ('2', 'a','18', 400);
INSERT INTO tb (id, name,year,num) VALUES ('3', 'b','19', 400);
INSERT INTO tb (id, name,year,num) VALUES ('4', 'b','18', 200);
INSERT INTO tb (id, name,year,num) VALUES ('5', 'c','19', 400);
INSERT INTO tb (id, name,year,num) VALUES ('6', 'c','18', 100);

我们的测试数据如下：

+----+------+------+------+
| id | name | year | num  |
+----+------+------+------+
|  1 | a    |   19 |  500 |
|  2 | a    |   18 |  400 |
|  3 | b    |   19 |  400 |
|  4 | b    |   18 |  200 |
|  5 | c    |   19 |  400 |
|  6 | c    |   18 |  100 |
+----+------+------+------+

现在，需要获得每家公司的增加值18年到19年，请问如何写mysql 语句？

+------+------+
| name |growth|
+------+------+
| a    |  100 |
| b    |  200 |
| c    |  300 |
+------+------+

最佳答案

SELECT 
    a.`name`,
    IF(a.`num` - b.`num` IS NULL,
        a.`num`,
        a.`num` - b.`num`) AS `growth`
FROM
    (
        SELECT `name`, `num`
        FROM `tb`
        WHERE `year` = 19
    ) a
LEFT JOIN
(
    SELECT `name`, `num`
    FROM `tb`
    WHERE `year` = 18
) b ON a.`name` = b.`name`;