我有一个电子商务表'orders'，这个表有一个'state'记录和一个'fruit'记录。我正在尝试（但失败）创建一个查询，该查询将返回每个状态的结果以及按顺序显示的前3个最受欢迎的水果。
“orders”表如下所示：
id State Fruit
----------------
1 CA grape
2 FL orange
3 CA grape
4 FL grapefruit
5 CA orange
6 CA grape
7 FL orange
8 CA peach
9 CA orange
10 FL orange
11 FL grapefruit
12 FL peach
etc etc etc
对该表的查询结果将是：
the_state the_fruits
------------------------
CA grape, orange, peach
FL orange, grapefruit, peach
我试过这个：
SELECT state as the_state,
(select count(id) as count, fruit from orders where state = the_state order by count(id) limit 3 ) as the_fruits
FROM orders
group by fruit
order by count(id) DESC
但这不是一个有效的查询，我不确定我是否在正确的轨道上
最佳答案
在MySQL中限制分组数据的结果是相当困难的。在不同的线程上有许多解决方案，但这可能在很大程度上取决于您拥有的数据的类型和数量。
以下可能是最简单的解决方案。
mysql> INSERT INTO orders VALUES
-> ('1', 'CA', 'grape'),
-> ('2', 'FL', 'orange'),
-> ('3', 'CA', 'grape'),
-> ('4', 'FL', 'grapefruit'),
-> ('5', 'CA', 'orange'),
-> ('6', 'CA', 'grape'),
-> ('7', 'FL', 'orange'),
-> ('8', 'CA', 'peach'),
-> ('9', 'CA', 'orange'),
-> ('10', 'FL', 'orange'),
-> ('11', 'FL', 'grapefruit'),
-> ('12', 'FL', 'peach'),
-> ('13', 'CA', 'apple'),
-> ('14', 'FL', 'apple');
Query OK, 14 rows affected (0.03 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select state, fruit, count(fruit) cf from orders group by state, fruit order by state, cf desc;
+-------+------------+----+
| state | fruit | cf |
+-------+------------+----+
| CA | grape | 3 |
| CA | orange | 2 |
| CA | peach | 1 |
| CA | apple | 1 |
| FL | orange | 3 |
| FL | grapefruit | 2 |
| FL | peach | 1 |
| FL | apple | 1 |
+-------+------------+----+
8 rows in set (0.00 sec)
SELECT state
, SUBSTRING_INDEX(GROUP_CONCAT(fruit ORDER BY cf DESC, fruit),',',3) top3
FROM
( SELECT state
, fruit
, COUNT(fruit) cf
FROM orders
GROUP
BY state
, fruit
) t1
GROUP
BY state;
+-------+-------------------------+
| state | top3 |
+-------+-------------------------+
| CA | grape,orange,peach |
| FL | orange,grapefruit,apple |
+-------+-------------------------+
2 rows in set (0.00 sec)
关于mysql - 带嵌套列表的MySQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26164863/