MySql:从一列中选择值到结果中的多列

Question

我有这样的表:

Bike_orders

ID | model_id | order_date | male_or_female_bike
5 | 099 | 2014-04-08 | M 
12 | 099 | 2014-04-08 | F
19 | 012 | 2014-02-12 | NULL
40 | 123 | 2014-04-08 | F
33 | 040 | 2014-04-08 | NULL

其中 'male_or_female_bike' 列:
M 或 NULL = 男自行车
F = 女自行车

(现在请忽略“模型 ID”列 - 它只是通知我需要它来创建与另一个表的连接)

理想的结果:

order_date | count_male | count_female  
2014-04-08 | 2 | 2 |
2014-02-12 | 1 | 0 |

我想知道:每天售出多少辆 F/M 自行车。就像我需要按日期“划分”male_or_female_bike。

我只能这样想:

select id, 
(
select male_or_female_bike
from bike_orders o2
where o2.male_or_female_bike = 1
and o2.id = o.id
) as F, 

(
select male_or_female_bike
from bike_orders o3
where o3.id = o.id
and (male_or_female_bike = 0 or male_or_female_bike is null)
) as M

from bike_orders o
where /*some other condition here*/
GROUP BY date

但是没有效果。是否存在用于此目的的专用 MySQL？还有其他更好/更快的方法吗？

Answer 1

你可以简单地这样做:

SELECT order_date,
       SUM(CASE WHEN male_or_female_bike IS NULL THEN 1 WHEN male_or_female_bike='M' THEN 1 ELSE 0 END) as MaleCount,
       SUM(CASE WHEN male_or_female_bike ='F' THEN 1 ELSE 0 END) as FemaleCount
FROM Bike_orders
GROUP BY order_date
ORDER BY order_date DESC

结果:

ORDER_DATE                         MALECOUNT     FEMALECOUNT
April, 08 2014 00:00:00+0000       2             2
February, 12 2014 00:00:00+0000    1             0

Fiddle Demo