Mysql求和两个日期被不同的、缺失的和新的

Question

Mysql查询和总计两个日期，按不同和缺失，以及按名称的新组

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

假设查询中只有两个日期，您应该能够在这里执行常规的枢轴查询。下面有一个窍门。在您的预期输出中，如果给定的NULL没有给定date的条目，则希望date出现。但是在difference列中，您希望将该条目视为零。我通过添加一个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

Answer 2

SELECT *,IFNULL(data1,0)-IFNULL(data2,0) FROM(
SELECT  `date`,`name`,
        MAX(IF(`date` = '2016-10-10',tot, NULL)) data1,
        MAX(IF(`date` ='2016-10-11',tot, NULL)) data2
FROM    (SELECT `date`,`name`,SUM(total) AS tot FROM test GROUP BY `name`,`date`) AS tab
GROUP   BY `name`)  AS t