时间戳

首页 > 解决方案 > 在 MySQL 中将数据从行转置到列（没有任何聚合）

mysql - 在 MySQL 中将数据从行转置到列（没有任何聚合）

问题描述

我需要在 MySQL 中将数据从行转换为列，但没有任何聚合。

例如，我有下表：

 CREATE TABLE `MyTable` ( `id` INT NOT NULL AUTO_INCREMENT ,`city` VARCHAR(10) NOT NULL , 
`category` VARCHAR(10) NOT NULL , `item`VARCHAR(10) NOT NULL , `price` DECIMAL(5,2) NOT NULL , 
`date` DATE NOT NULL ,PRIMARY KEY (`id`)) ENGINE = InnoDB;

使用以下数据：

INSERT INTO `MyTable` (`id`, `city`, `category`, `item`, `price`, `date`) VALUES (NULL, 'A', 'Cat1','It1', '10.00', '2018-01-01'), 
(NULL, 'A', 'Cat1','It1', '20.00', '2018-01-02'), 
(NULL, 'A', 'Cat1','It2', '30.00', '2018-01-03'),
(NULL, 'A', 'Cat2','It1', '40.00', '2018-01-04'),
(NULL, 'B', 'Cat1','It1', '50.00', '2018-01-05'),
(NULL, 'A', 'Cat1', 'It1', '100.00', '2018-02-11'), 
(NULL, 'A', 'Cat1','It1', '200.00', '2018-02-12'), 
(NULL, 'A', 'Cat1','It2', '300.00', '2018-02-13'),
(NULL, 'A', 'Cat2','It1', '400.00', '2018-02-14'),
(NULL, 'B', 'Cat1','It1', '500.00', '2018-02-15')

当我使用这个查询

 SELECT city, category, item, sum(CASE WHEN EXTRACT(YEAR_MONTH FROM `date`) = "201801" 
THEN price ELSE 0 END) AS "Jan", sum(CASE WHEN EXTRACT(YEAR_MONTH FROM `date`) = "201802" 
THEN price ELSE 0 END) AS "Feb" FROM MyTable WHERE 1 GROUP BY city, category, item

然后我得到'Item1'的汇总结果：

在此处输入图像描述

但我需要没有任何聚合的所有事务： 在此处输入图像描述

我应该如何编写查询以获得正确的结果？

标签： mysql

解决方案

您尝试做的一般类称为PIVOT。在 PIVOT 中，行的子集就像表一样，因为来自单个列的数据被划分为多个列。 JOIN可以在这里为您提供帮助：

SELECT
  tblItems.city, tblItems.category, tblItems.item,
  tblJan.price AS Jan,
  tblFeb.price AS Feb
FROM
  (SELECT city, category, item
   FROM MyTable
   GROUP BY city, category, item) AS tblItems
INNER JOIN
  (SELECT city, category, item, price
   FROM MyTable
   WHERE EXTRACT(YEAR_MONTH FROM `date`) = "201801") AS tblJan
ON (tblItems.city = tblJan.city AND tblItems.category = tblJan.category AND tblItems.item = tblJan.item)
INNER JOIN
  (SELECT city, category, item, price
   FROM MyTable
   WHERE EXTRACT(YEAR_MONTH FROM `date`) = "201802") AS tblFeb
ON (tblItems.city = tblFeb.city AND tblItems.category = tblFeb.category AND tblItems.item = tblFeb.item)
ORDER BY tblItems.city, tblItems.category, tblItems.item;

SQL 小提琴：http ://sqlfiddle.com/#!9/f77978a/3

但是，您的下一个问题将是：“如果我不提前知道我将拥有多少个子表，我该如何动态地执行此操作？” 在手头的情况下，这可能无关紧要，因为一年中有固定的月份数。但迟早你会想要动态地执行 PIVOT。

一些 RDBMS 具有内置PIVOT功能，但不幸的是，MySQL 不是其中之一。因此，对于 MySQL，您需要动态构建 PIVOT 查询的逻辑，然后执行构建的查询。该逻辑可以在 MySQL 中的存储过程中，也可以在具有 MySQL 库函数的外部语言中（大多数现代语言都有）。

编辑：或者，您可以考虑遵循@Madhur Bhaiya 的评论，并将信息的显示委托给从数据库中获取数据的程序；SQL 很强大，但是——与其他一切一样——它不是满足您所有需求的正确位置。

推荐阅读