SQL限制ID列 [英] SQL LIMIT by id column

问题描述

我有以下声明:

SELECT s.*, u.`fname`, u.`lname`, aif.`airport`
FROM services s
INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id`
INNER JOIN users u
    ON s.`service_provider_id` = u.`id`

比如说，这就是我得到的结果:

Lets say for example that this is the result I am getting:

id | ----- | ------ | ...............
 1 |  Jack |  Jones | ...............
 1 |  Moses|  Cohen | ...............
 2 |  Tom  |  Jones | ...............
 3 |  Luke | SkyWal | ...............

所以我需要使用 LIMIT 2 ，这样我才能获得前3行(而不仅是前2行).因此，限制应基于id列.使用 OFFSET 时，它也应该起作用.

So I need to use LIMIT 2 so that I will get the first 3 rows (and not only the first 2). So the Limit should be based on the id column. It should work also when using OFFSET.

这怎么办?

推荐答案

不是直接读取services表，而是使用子查询并将其联接.

Instead of reading the services table directly, use a sub query and join to that.

select s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
    ( select * from services order by id limit 2) as s INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id` INNER JOIN users u
    ON s.`service_provider_id` = u.`id`

取决于您的mysql版本，您将无法使用限制和偏移量进行子查询IN(此版本的MySQL尚不支持"LIMIT& IN/ALL/ANY/SOME子查询")，但仍会工作.

Depending on your version of mysql, you can't have a subquery IN using limit and offset (This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery') but this will still work.

如果您将内部查询设置为此:

If you set the inner query to this:

(SELECT s.id
FROM services s
INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id`
INNER JOIN users u
    ON s.`service_provider_id` = u.`id`
LIMIT 2)

然后，它将仅返回具有airport_in_flight和用户的服务(可能添加一个不同的子句).

Then it will only return services with airports_in_flight and users (possibly add a distinct clause).

编辑以澄清:

现在，您可以选择以下选项:

Right now you have this as your select:

select s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
        services as s INNER JOIN airports_in_flight aif
        ON s.`id` = aif.`service_id` INNER JOIN users u
        ON s.`service_provider_id` = u.`id`

您想限制2种服务(我不知道您是否需要所有服务，我猜只是那些具有匹配用户和正在飞行的机场的服务)，因此您需要将限制放在正确的表上.

You want to limit to 2 services (I don't know if you want all services, I'm guessing just the ones with matching users and airports in flight), so you need to put the limit on the right table.

在这种情况下，右表是services表.为了使事情变得简单，让我们从简化的版本开始:

In this case, the right table is the services table. To make things simple lets start with a simplified version of what you have:

SELECT s.*, aif.`airport` FROM 
        services as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

我将假设每种服务都有一个正在飞行的机场(我们以后可以添加这种复杂性).

I'm going to make an assumption that there is an airport in flight row for every service (we can add that complexity later).

要列出前两个服务，我们希望限制服务，而不是整个查询，所以应该是:

To list the first 2 services, we want the limit on the services, not the whole query, so it would be:

SELECT s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
        (select * from services limit 2) as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

注意，我用查询替换了表服务，现在可以限制了，这是子查询.如果现在只想查看有正在运行的机场的服务，则需要将该子查询更改为:

Notice I replaced the table services with a query, that I can now limit, this is the subquery. If we now want to only look at services that have an airport in flight, we need to change that subquery from:

select * from services limit 2

到

select ss.* from services ss 
inner join airports_in_flight aifs on ss.`in` = aifs.`service_id` limit 2

我已经将service和airport_in_flight表分别重命名为ss和aifs，以便它们不会与主查询中的名称发生冲突，并进行内部联接以将行限制为仅服务表，并限制为2，因此现在，子查询进入查询:

I've renamed the services and airports_in_flight tables ss and aifs so they don't collide with the names in the main query, put an inner join in to limit my rows to only service table, and limited by 2, so putting the subquery into the query we now get:

select s.*, u.`fname`, u.`lname`, aif.`airport` 
FROM 
  (select ss.* from services ss 
   inner join airports_in_flight aifs on ss.`in` = aifs.`service_id`
   limit 2) as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

然后您应该能够扩展子查询以为用户添加内部联接(从而将服务限制为仅包含airport_in_flight行和用户行的服务)，并将users表添加到主查询中.

You should be able to then expand the subquery to add the inner join for users (thus limiting services to only those that have airports_in_flight rows and users rows), and add the users table to the main query.

这篇关于SQL限制ID列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！