如何选择表行值作为值和的列

Question

我有下表：

CREATE TABLE products
(
date DATE,
productname VARCHAR(80),
quantity INT(5)
);

INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-12-16','toy',5);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-12-18','santa',8);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-12-23','tree',15);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-11-16','toy',5);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-11-19','santa',2);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-11-24','tree',5);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-01','toy',10);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-04','santa',20);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-09','tree',30);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-01','toy',40);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-04','santa',30);
INSERT INTO `products`(`date`, `productname`, `quantity`) VALUES ('2016-10-09','tree',20)

我希望看到每个月的日期为一行，产品名称为列的数量之和，因此我创建了如下查询：

SELECT DATE_FORMAT(`date`, '%Y-%m') As Date, 
IF(`productname` = 'santa', SUM(`quantity`), 'none') As santa,
IF(`productname` = 'toy', SUM(`quantity`), 'none') As toy,
IF(`productname` = 'tree', SUM(`quantity`), 'none') As tree  

FROM `products`

GROUP BY DATE_FORMAT(`date`, '%Y-%m'),`productname`

这给了我这样的感觉：

+---------+-------+------+------+
| Date    | santa | toy  | tree |
+---------+-------+------+------+
| 2016-10 | 50    | none | none |
+---------+-------+------+------+
| 2016-10 | none  | 50   | none |
+---------+-------+------+------+
| 2016-10 | none  | none | 50   |
+---------+-------+------+------+
| 2016-11 | 2     | none | none |
+---------+-------+------+------+
| 2016-11 | none  | 5    | none |
+---------+-------+------+------+
| 2016-11 | none  | none | 5    |
+---------+-------+------+------+
| 2016-12 | 8     | none | none |
+---------+-------+------+------+
| 2016-12 | none  | 5    | none |
+---------+-------+------+------+
| 2016-12 | none  | none | 15   |
+---------+-------+------+------+

这几乎很好，但我希望它是这样的，所以在一个特定的月份只有一排：

+---------+-------+------+------+
| Date    | santa | toy  | tree |
+---------+-------+------+------+
| 2016-10 | 50    | 50   | 50   |
+---------+-------+------+------+
| 2016-11 | 2     | 5    | 5    |
+---------+-------+------+------+
| 2016-12 | 8     | 5    | 15   |
+---------+-------+------+------+

是否可以通过查询来实现？

Answer 1

这应该可以

SELECT DATE_FORMAT(`date`, '%Y-%m') As Date,
  IFNULL(Sum(Case when `productname` = 'santa' then `quantity` end),0) As santa, 
  IFNULL(Sum(Case when `productname` = 'toy' then `quantity` end),0) As toy, 
  IFNULL(Sum(Case when `productname` = 'tree' then `quantity` end),0) As tree 
FROM `products`
GROUP BY DATE_FORMAT(`date`, '%Y-%m');

Answer 2

看起来您想要对产品名称执行枢轴查询。试试这个：

SELECT DATE_FORMAT(date, '%Y-%m') AS Date, 
       COALESCE(SUM(CASE WHEN productname = 'santa'
                         THEN quantity END), 'none') AS santa,
       COALESCE(SUM(CASE WHEN productname = 'toy'
                         THEN quantity END), 'none') AS toy,
       COALESCE(SUM(CASE WHEN productname = 'tree'
                         THEN quantity END), 'none') AS tree,
FROM products
GROUP BY DATE_FORMAT(date, '%Y-%m')