我在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查询？
最佳答案
如果你想在一条线上有一个给定日期的所有奶牛饲料，你可以试试这样的方法：
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