mysql - 在左连接中是否可以设置限制，仅用于基表的偏移量

Question

在左连接中是否可以设置限制，仅用于基表的偏移量

我正在使用带有 mysql/linux 的示例 db nwind

我正在尝试限制左连接中“基本”表（客户）的行

我知道我可以重写查询（正如许多其他人的回答）但我问是否有一个连接的解决方案我有一个自动进行分页和过滤的类。

无限制地运行查询，偏移量

select
customers.id,
customers.company,
customers.job_title,
orders.id,
orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.id = orders.customer_id
WHERE customers.job_title='Owner'
ORDER BY customers.id,orders.id

我得到了预期的结果

id      company job_title       id      order_date
1       Company A       Owner   44      2006-03-24 00:00:00
1       Company A       Owner   71      2006-05-24 00:00:00
2       Company B       Owner   NULL    NULL
5       Company E       Owner   NULL    NULL
7       Company G       Owner   41      2006-03-24 00:00:00
7       Company G       Owner   68      2006-05-24 00:00:00
17      Company Q       Owner   NULL    NULL
24      Company X       Owner   NULL    NULL

#example 1
select
customers.id,
customers.company,
customers.job_title,
orders.id,
orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.id = orders.customer_id
WHERE customers.job_title='Owner'
ORDER BY customers.id,orders.id
limit 3 offset 0

d       company job_title       id      order_date
1       Company A       Owner   44      2006-03-24 00:00:00
1       Company A       Owner   71      2006-05-24 00:00:00
2       Company B       Owner   NULL    NULL

#example 2
select
customers.id,
customers.company,
customers.job_title,
orders.id,
orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.id = orders.customer_id
WHERE customers.job_title='Owner'
ORDER BY customers.id,orders.id
limit 3 offset 3

id      company job_title       id      order_date
5       Company E       Owner   NULL    NULL
7       Company G       Owner   41      2006-03-24 00:00:00
7       Company G       Owner   68      2006-05-24 00:00:00

对于示例 1，我想得到

id      company job_title       id      order_date
1       Company A       Owner   44      2006-03-24 00:00:00
1       Company A       Owner   71      2006-05-24 00:00:00
s2       Company B       Owner   NULL    NULL
(limit 3

例如 2

id      company job_title       id      order_date
5       Company E       Owner   NULL    NULL
7       Company G       Owner   41      2006-03-24 00:00:00
7       Company G       Owner   68      2006-05-24 00:00:00
limit 3 offset 3

标签： mysql

Answer 1

我认为通过限制基表你的意思是左表你可以这样做

select * from (

select
id,
company,
job_title
FROM customers
LIMIT 3
OFFSET 0
) as customers
LEFT OUTER JOIN orders 
ON customers.id = orders.customer_id
WHERE customers.job_title='owner'
ORDER BY customers.id,orders.id

第一个括号现在用作您的基表，行数有限