在mysql中将数据从行转换到列(没有任何聚合)

b5lpy0ml 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(306)

我需要在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

来源：https://stackoverflow.com/questions/53189259/transpose-data-from-rows-to-columns-in-mysql-without-any-aggregation

1条答案

按热度按时间

rjee0c151#

你要做的事情的一般类叫做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号楼
但是，您的下一个问题是：“如果我不提前知道将有多少子表，如何动态地执行此操作？”在本例中，这可能无关紧要，因为一年中有固定的月份数。但迟早，您会希望动态执行枢轴。
一些RDBMS具有内置的 PIVOT 但不幸的是，mysql不是其中之一。因此，对于mysql，您需要逻辑来动态构建pivot查询，然后执行构建的查询。这种逻辑可以在mysql中的存储过程中，也可以在具有mysql库函数的外部语言中（大多数现代语言都有）。
编辑：或者，您可以考虑跟随@madhur bhaiya的注解，并将信息的显示委托给从数据库获取数据的程序；sql是强大的，但是——和其他任何东西一样——它不是满足您所有需求的合适地方。

赞(0）回复(0）举报 2021-06-18

我来回答

在mysql中将数据从行转换到列(没有任何聚合)

1条答案

相关问题

热门标签

最新问答