php - Mysql按不同和缺失和新的两个日期求和

Question

Mysql查询两个日期的总和TOTAL by different and missing and new按名称分组

10/10/2016  xx  10
10/10/2016  xx  15
10/10/2016  yy  15
10/10/2016  yy  45
10/10/2016  zz  25
10/10/2016  zz  5
10/10/2016  xx  5
10/11/2016  xx  15
10/11/2016  zz  25
10/11/2016  zz  5
10/11/2016  xx  45
10/11/2016  aa  45

我想要这个结果

name    10/10/2016  10/11/2016  DIFF
+-----+-----+----+-----+-----+-----+
aa      NULL        45          45
xx      30          60          30
yy      60          NULL        -60
zz      30          30          0

表格

CREATE TABLE IF NOT EXISTS `test` (`date` date NOT NULL,`name` varchar(10) NOT NULL,`total` int(10) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` (`date`, `name`, `total`) VALUES
('2016-10-10', 'xx', 10),('2016-10-10', 'xx', 15),('2016-10-10', 'yy', 15),('2016-10-10', 'yy', 45),('2016-10-10', 'zz', 25),('2016-10-10', 'zz', 5),
('2016-10-10', 'xx', 5),('2016-10-11', 'xx', 15),('2016-10-11', 'zz', 25),('2016-10-11', 'zz', 5),('2016-10-11', 'xx', 45),('2016-10-11', 'aa', 45);

Answer 1

假设您在查询中只有两个日期，您应该能够在此处进行常规数据透视查询。下面有一个技巧。如果给定的 name 没有针对给定的 date 的条目，您希望在预期的输出中显示 NULL。但是在差异列中，您希望将该条目视为零。我通过添加一个 ELSE 条件来实现这一点，当给定的名称/日期不存在时该条件使用零。

SELECT name,
       SUM(CASE WHEN date = '2016-10-10' THEN total END) AS 10_10_2016,
       SUM(CASE WHEN date = '2016-10-11' THEN total END) AS 10_11_2016,
       SUM(CASE WHEN date = '2016-10-11' THEN total ELSE 0 END) -
       SUM(CASE WHEN date = '2016-10-10' THEN total ELSE 0 END) AS DIFF
FROM test
GROUP BY name