MySQL如何仅从第一个表而不是第二个表限制来自JOIN-Query的行数？

I have a two Tables one with Countries and second with Cities.

我有两个表一个与国家，第二个与城市。

First Table is Countries

第一张表是国家

country_id | country_name

country_id |国家的名字

Second Table is Cities

第二张表是城市

country_id | city_id | city_name

country_id | city_id |城市名

I want to limit amount of queries from second Table to 3 Cities. Because one Country have a lot of Cities. I need only 5 Cities.

我想限制从第二个表到3个城市的查询量。因为一个国家有很多城市。我只需要5个城市。

My Query:

我的查询：

$query = “SELECT c.country_name, p.city_name FROM (SELECT * FROM Cities LIMIT 3) AS p LEFT JOIN Countries as c ON p.county_id = c.county_id”;ter code here

$result = mysqli_query($db, $query);

$my_array = array();

while($row = mysqli_fetch_assoc){
 array_push($my_array, $row);
}

If I fill $my_array i get 3 results from Database

如果我填写$ my_array，我会从数据库获得3个结果

My output:

我的输出：

[0] => Array ( [country_name] => Country 1 [city_name] => City 1 )

[0] =>数组（[country_name] =>国家1 [city_name] =>城市1）

[1] => Array ( [country_name] => Country 1 [city_name] => City 2 )

[1] =>数组（[country_name] =>国家1 [city_name] =>城市2）

[2] => Array ( [country_name] => Country 1 [city_name] => City 3 )

[2] =>数组（[country_name] =>国家1 [city_name] =>城市3）

I need something like this

我需要这样的东西

County 1
-----------------------
City 1 of Country 1
City 2 of Country 1
City 3 of Country 1
City 4 of Country 1
City 5 of Country 1

County 2
-----------------------
City 1 of Country 2
City 2 of Country 2
City 3 of Country 2
City 4 of Country 2
City 5 of Country 2

County 3
-----------------------
City 1 of Country 3
City 2 of Country 3
City 3 of Country 3
City 4 of Country 3
City 5 of Country 3

What I do wrong? I use MySQL 5.6.26

我做错了什么？我使用MySQL 5.6.26

Thank you.

谢谢。

1 个解决方案

#1

Like that?

像那样？

SELECT city_id, city_name FROM Cities AS p LEFT JOIN Countries AS c ON c.country_id = p.country_id WHERE c.country_id = 'id' LIMIT 0,5