mysql - 从不同表中选择具有动态名称的行作为与 articleId 匹配的新表中的列名

Question

假设我有以下两个表

CREATE TABLE `articles` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `artId` int(11) NOT NULL,
 `price` float NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `artId` (`artId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `priceHistory` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `artId` int(11) NOT NULL,
 `price` float NOT NULL,
 `checkdate` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

以及以下数据

INSERT INTO `test`.`articles` (`id`, `artId`, `price`) VALUES (NULL, '1', '5'), (NULL, '2', '2'), (NULL, '3', '3'), (NULL, '4', '9.50'), (NULL, '5', '1.3');


INSERT INTO `test`.`priceHistory` (`id`, `artId`, `price`, `checkdate`) VALUES (NULL, '1', '5', '2014-11-10 04:19:56'), (NULL, '1', '8.50', '2014-11-09 04:19:56'), (NULL, '1', '2.5', '2014-11-08 04:19:56'), (NULL, '2', '2', '2014-11-10 04:19:56'), (NULL, '2', '2.5', '2014-11-09 04:19:56'), (NULL, '3', '3', '2014-11-10 04:19:56'), (NULL, '4', '9.50', '2014-11-10 04:19:56'), (NULL, '4', '10', '2014-11-09 04:19:56'), (NULL, '4', '8', '2014-11-08 04:19:56'), (NULL, '4', '7', '2014-11-07 04:19:56'), (NULL, '5', '1.3', '2014-11-10 04:19:56'), (NULL, '5', '2', '2014-11-09 04:19:56');

我现在想做的是以下内容。我想从 articles 表中选择所有文章，并将每个价格变化/价格历史条目都放在同一行中。例如，最终结果应如下所示:

id  articleId   currentPrice    2014-11-07        2014-11-08    2014-11-09      2014-11-10
1   1           5               NULL              2.5               8.5             5
2   2           2               NULL              NULL              2.5             2
3   3           3               NULL              NULL              NULL            3
4   4           9.5             7                 8                 10              9.5
5   5           1.3             NULL              NULL              2               1.3

基本上，我想从另一个表中选择日期并将它们作为列名放入并相应地填写数据。这在某种程度上可能与 SELECT 相关吗？我在这个主题上找到了多个资源，但它们要么针对固定​​数量的行，我需要它使其动态，因为日期时间可能会持续到明年(极端示例)，或者它们只针对一个表。这些是我找到的资源

1. Problems to select the correct row from a second table
2. Using MySQL GROUP_CONCAT or PIVOT on multiple joined tables
3. MySQL - How to display row value as column name using concat and group_concat
4. Select dynamic Columns in mysql
5. mysql select dynamic row values as column names, another column as value
6. Mysql query to dynamically convert rows to columns
7. MySQL - Rows to Columns
8. http://buysql.com/mysql/12-how-to-pivot-tables.html

Answer 1

好吧，您应该付出更多的努力来尝试调整您为您的案例找到的解决方案之一，或者如果您不理解其中的某些内容 - 更加努力，询问您不理解的具体内容 -如何连接两个表，如何连接字符串，如何在 MySQL 中使用变量等，了解这些东西是如何工作的。

也就是说，让我们以解决方案 #6 为例 - Mysql query to dynamically convert rows to columns ，并根据您的情况进行调整。

SET @sql = NULL;

SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'MAX(IF(CAST(checkdate AS date) = ''', CAST(checkdate AS date), ''', h.price, NULL)) AS ', '`', CAST(checkdate AS date), '`'
    )
    ORDER BY checkdate
  )
INTO
  @sql
FROM
  priceHistory;

SET @sql = CONCAT('
  SELECT
    a.id AS articleId,
    a.price AS currentPrice,
    ', @sql, '
  FROM
    articles a LEFT JOIN priceHistory h
  ON
    a.id = h.artId
  GROUP
    BY a.id
');

PREPARE stmt FROM @sql;

EXECUTE stmt;

瞧瞧!

+-----------+--------------+------------+------------+------------+--------------------+
| articleId | currentPrice | 2014-11-07 | 2014-11-08 | 2014-11-09 | 2014-11-10         |
+-----------+--------------+------------+------------+------------+--------------------+
|         1 |            5 |       NULL |        2.5 |        8.5 |                  5 |
|         2 |            2 |       NULL |       NULL |        2.5 |                  2 |
|         3 |            3 |       NULL |       NULL |       NULL |                  3 |
|         4 |          9.5 |          7 |          8 |         10 |                9.5 |
|         5 |          1.3 |       NULL |       NULL |          2 | 1.2999999523162842 |
+-----------+--------------+------------+------------+------------+--------------------+

在您的数据结构中需要修复三件事。

1. 从 articles 中删除 artId - 它完全是多余的。
2. 将 checkdate 的类型更改为 date。
3. 永远不要使用float 来存钱(看看值1.2999999523162842)，使用NUMERIC相反!