mysql - 获取最高值的子查询

Question

输入

+-------+---------+----------+
| Name  | Country | State    |
+-------+---------+----------+
| jon   | US      | Chicago  |
+-------+---------+----------+
| kathy | US      | CA       |
+-------+---------+----------+
| linda | US      | LA       |
+-------+---------+----------+
| harry | UK      | wales    |
+-------+---------+----------+
| steve | UK      | scotland |
+-------+---------+----------+
| ram   | India   | ap       |
+-------+---------+----------+
| jai   | India   | ka       |
+-------+---------+----------+
| raju  | India   | ap       |
+-------+---------+----------+
| ravi  | India   | mp       |
+-------+---------+----------+

我无法使用以下查询传递计数最高的国家(印度)，因为它返回 2(国家和计数)值。请指正。

select name from table a 
where a.country in 
(SELECT country, COUNT(*) as c FROM table b 
GROUP BY country 
ORDER BY c DESC LIMIT 1);

输出:

应显示在同一张表中计数最高的州(在计数最高的国家-印度下)的详细信息。

+---------+-------+----+
| ram     | india | ap |
+---------+-------+----+
| raju    | india | ap |
+---------+-------+----+

Answer 1

试试这个

select * from table e
where e.state in
(select d.state from table a 
where a.country in 
(SELECT country  FROM table b 
 GROUP BY country 
 ORDER BY count(*) DESC LIMIT 1);
 GROUP BY state 
 ORDER BY count(*) DESC LIMIT 1);