在mysql中将一列转换为三个新列

ha5z0ras 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(377)

我在mysql中有一个包含以下列的表：

数据示例：

'16', '1', 'Evening', '6.00', '2014-06-05 08:14:35', '2014-06-04', '6.00'
'17', '2', 'Afternoon', '8.00', '2014-06-05 08:15:29', '2014-06-04', '8.00'
'18', '3', 'Afternoon', '4.00', '2014-06-05 08:17:32', '2014-06-04', '4.00'
'19', '4', 'Afternoon', '4.00', '2014-06-05 08:18:25', '2014-06-04', '4.00'
'20', '17', 'Morning', '2.00', '2014-06-05 09:11:40', '2014-06-05', '2.00'
'21', '17', 'Morning', '3.00', '2014-06-05 09:16:36', '2014-06-04', '3.00'
'22', '17', 'Evening', '2.00', '2014-06-05 09:17:43', '2014-06-04', '2.00'
'23', '19', 'Morning', '1.00', '2014-06-05 10:12:47', '2014-06-05', '1.00'
'24', '20', 'Morning', '2.00', '2014-06-05 10:16:02', '2014-06-05', '2.00'
'25', '22', 'Morning', '2.00', '2014-06-05 10:27:34', '2014-06-05', '2.00'
'26', '23', 'Morning', '2.00', '2014-06-05 10:28:08', '2014-06-05', '2.00'
'27', '24', 'Morning', '1.00', '2014-06-05 10:30:20', '2014-06-05', '1.00'
'28', '27', 'Morning', '1.00', '2014-06-05 10:43:12', '2014-06-05', '1.00'
'29', '27', 'Combined', '2.00', '2014-06-05 10:45:45', '2014-06-04', '2.00'
'31', '28', 'Morning', '3.00', '2014-06-05 11:04:32', '2014-06-05', '3.00'

我想转换时间栏，这样我有三个新的栏晚上，下午和上午，并记录数量，使列看起来像 id, cow_id, Evening, Afternoon, Morning, date_added, date,quantity_type 有人能给我一个关于如何使用mysql查询的解决方案吗？

mysql

来源：https://stackoverflow.com/questions/51492833/transposing-a-column-into-three-new-columns-in-mysql

2条答案

按热度按时间

nqwrtyyt1#

如果你想把某一天所有的奶牛饲料都放在一行上，你可以这样做：

SELECT cow_id, 
    SUM(CASE time WHEN 'Evening' THEN quantity ELSE 0 END) AS Evening,
    SUM(CASE time WHEN 'Afternoon' THEN quantity ELSE 0 END) AS Afternoon,
    SUM(CASE time WHEN 'Morning' THEN quantity ELSE 0 END) AS Morning,
    SUM(CASE time WHEN 'Combined' THEN quantity ELSE 0 END) AS Combined,
    `date`
FROM cows
GROUP BY cow_id, date

对于您的示例数据，这将产生如下结果（注意，我必须为“组合”时间添加另一列）：

cow_id  Evening Afternoon   Morning Combined    date    
1       6       0           0       0           2014-06-04  
2       0       8           0       0           2014-06-04  
3       0       4           0       0           2014-06-04  
4       0       4           0       0           2014-06-04  
17      2       0           3       0           2014-06-04  
17      0       0           2       0           2014-06-05  
19      0       0           1       0           2014-06-05  
20      0       0           2       0           2014-06-05  
22      0       0           2       0           2014-06-05  
23      0       0           2       0           2014-06-05  
27      0       0           0       2           2014-06-04  
27      0       0           1       0           2014-06-05  
28      0       0           3       0           2014-06-05

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

bnl4lu3b2#

试试这个：

SELECT id, cow_id,
    IF(time = 'Morning',quantity,0) AS Morning,
    IF(time = 'Afternoon',quantity,0) AS Afternoon,
    IF(time = 'Evening',quantity,0) AS Evening,
    date_added, quantity_type
FROM OriginalTable

它只包括 quantity 如果 time 匹配特定值，然后对列名使用别名以使输出更漂亮。

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

我来回答

在mysql中将一列转换为三个新列

2条答案

相关问题

热门标签

最新问答