mysql - 如何从两个以行为列的表中检索数据？ (MySQL 中的动态枢轴)

Question

我的 MySQL 数据库中有两个表(表 1 和表 2)，如下图所示。我想从两个表中获取数据，就像表 3 一样，如果我的表 1 行增加，则表 3 列增加。mysql 一次查询就能得到数据吗？

CREATE TABLE `table_1` (
  `id` smallint(4) NOT NULL,
  `name` varchar(31) NOT NULL,
  `price` decimal(6,2) NOT NULL DEFAULT '0.00',
  `status` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table_1` (`id`, `name`, `price`, `status`) VALUES
(1, 'name1', '1.00', 1),
(2, 'name2', '2.00', 1),
(3, 'name3', '3.00', 1),
(4, 'name4', '5.00', 1),
(5, 'name5', '10.00', 1),
(6, 'name6', '15.00', 1),
(7, 'name7', '20.00', 1),
(8, 'name8', '50.00', 1);

CREATE TABLE `table_2` (
  `id` mediumint(6) NOT NULL,
  `table1_id` smallint(4) NOT NULL,
  `qry` smallint(4) NOT NULL DEFAULT '1',
  `total` decimal(6,2) NOT NULL DEFAULT '0.00',
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table_2` (`id`, `table1_id`, `qry`, `total`, `date`) VALUES
(1, 1, 10, '10.00', '2017-12-02'),
(2, 2, 20, '40.00', '2017-12-02'),
(3, 3, 10, '30.00', '2017-12-02'),
(4, 5, 5, '50.00', '2017-12-02'),
(5, 4, 20, '100.00', '2017-12-03'),
(6, 6, 10, '150.00', '2017-12-03'),
(7, 7, 5, '100.00', '2017-12-03'),
(8, 8, 2, '100.00', '2017-12-03');

Answer 1

您的要求称为“动态数据透视查询”，因为在标准 SQL 中不可能将数据转换为列标题。您必须编写一个查询来为您生成一个新查询。

生成的查询代码将如下所示:

SELECT t2.`date`
 , max(CASE WHEN name = 'name1' THEN t2.total END) AS name1
 , max(CASE WHEN name = 'name2' THEN t2.total END) AS name2
 , max(CASE WHEN name = 'name3' THEN t2.total END) AS name3
 , max(CASE WHEN name = 'name4' THEN t2.total END) AS name4
 , max(CASE WHEN name = 'name5' THEN t2.total END) AS name5
 , max(CASE WHEN name = 'name6' THEN t2.total END) AS name6
 , max(CASE WHEN name = 'name7' THEN t2.total END) AS name7
 , max(CASE WHEN name = 'name8' THEN t2.total END) AS name8
FROM table_1 t1
INNER JOIN table_2 t2 ON t1.id = t2.id
GROUP BY t2.`date`

要创建该查询，请使用以下命令(这是动态 SQL 查询)

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when t1.name = ''',
      Name,
      ''' then t2.total end) AS ',
      replace(Name, ' ', '')
    )
  ) INTO @sql
from Table_1;

SET @sql = CONCAT(
'SELECT t2.`date`, '
, @sql
, ' from table_1 t1
inner join table_2 t2 on t1.id = t2.id
group by t2.`date`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见this Demo

+---------------------+-------+-------+-------+-------+--------+--------+--------+--------+
|        date         | name1 | name2 | name3 | name4 | name5  | name6  | name7  | name8  |
+---------------------+-------+-------+-------+-------+--------+--------+--------+--------+
| 02.12.2017 00:00:00 | 10,00 | 40,00 | 30,00 | 50,00 | NULL   | NULL   | NULL   | NULL   |
| 03.12.2017 00:00:00 | NULL  | NULL  | NULL  | NULL  | 100,00 | 150,00 | 100,00 | 100,00 |
+---------------------+-------+-------+-------+-------+--------+--------+--------+--------+