让我们假设我有以下两个表
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实现吗?我在这个主题上找到了多个资源,但它们要么用于固定数量的行,我需要它保持动态,因为日期时间可能会持续到明年(极端示例),要么只用于一个表。这些是我找到的资源
- 从第二个表中选择正确行的问题
- 在多个联接表上使用MySQL GROUP_CONCT或PIVOT
- MySQL-如何使用concat和group_concat将行值显示为列名
- 在mysql中选择动态列
- mysql选择动态行值作为列名,另一列作为值
- 将行动态转换为列的Mysql查询
- MySQL-行到列
- http://buysql.com/mysql/12-how-to-pivot-tables.html
好吧,你应该付出更多的努力,尝试将你找到的解决方案之一应用于你的案例,或者如果你不理解其中的一些内容-更努力,询问你不理解的具体内容-如何连接两个表,如何连接字符串,如何在MySQL中使用变量,等等,学习这些内容是如何工作的。
也就是说,让我们以solution #6
为例-Mysql查询来动态地将行转换为列,并根据您的情况进行调整。
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 |
+-----------+--------------+------------+------------+------------+--------------------+
数据结构中有三件事需要修复。
- 从
articles
中删除artId
—它是完全冗余的 - 将
checkdate
的类型更改为date
- 永远不要使用
float
来存储金钱(看看值1.2999999521312842),而是使用NUMERIC