mysql数据库连接限制,mysql-获取数据库连接表有限制

这个问题已经在这里有了答案：            >            mysql select top n max values                                    4个

香港专业教育学院一直试图联接两个表,但仅显示从联接的表有限的结果数(2).不幸的是我无法获得正确的结果.这些是我的表：

目的地

id name

------------

1 Bahamas

2 Caribbean

3 Barbados

航海

id name destination

---------------------------------

1 Adventure 1

2 For Kids 2

3 All Inclusive 3

4 Seniors 1

5 Singles 2

6 Disney 1

7 Adults 2

这是我尝试过的查询：

SELECT

d.name as Destination,

s.name as Sailing

FROM destinations d

JOIN sailings s

ON s.destination = d.id

LIMIT 2

但是由于限制,这给了我2：

Destination Sailing

-------------------------

Bahamas Adventure

Caribbean For Kids

我希望将LIMIT 2仅应用于联接表航行

预期成绩：

Destination Sailing

-------------------------

Bahamas Adventure

Bahamas Seniors

Caribbean Singles

Caribbean For Kids

有人可以指出正确的方向吗？

解决方法:

尝试

select tmp.name as destination,d.name as sailings from (

SELECT

id,

name,

destination

FROM

(

SELECT

id,

name,

destination,

@rn := IF(@p = destination, @rn + 1, 1) AS rn,

@p := destination

FROM sailings

JOIN (SELECT @p := NULL, @rn := 0) AS vars

ORDER BY destination

) AS T1

WHERE rn <= 2

)tmp

JOIN (SELECT * FROM destinations limit 0,2) d

ON(tmp.destination=d.id)

我做了2个派生表并加入了它们

标签：join,sql,mysql,database

来源： https://codeday.me/bug/20191026/1932685.html