mysql - SQL 查询 : compound

Question

有一个MySQL的查询。这种查询在前端很容易解决，但需要MySQL中的一种机制。你如何计算 1 列中的 2 个不同项目？例如:我有这张 table :

NAME    FRUIT
A   APPLE
A   APPLE
A   ORANGE
A   APPLE
A   ORANGE
B   APPLE
B   ORANGE
B   ORANGE
B   ORANGE

需要o/P:

NAME    No._of_apples   No._of_oranges
A   3            2
B   1            3

谁能澄清我尝试的代码，以获得此 O/P:

select distinct msisdn, count(fruit) no._of_apples, count(fruit) no._of_oranges
from table,

where true

group by 1
order by 1

Answer 1

尝试

SELECT NAME, 
       SUM(IF(FRUIT = 'APPLE', 1, 0)) AS no_of_apples,
       SUM(IF(FRUIT = 'ORANGE', 1, 0)) AS no_of_oranges
FROM table
GROUP BY NAME
ORDER BY NAME