SQL限制SELECT但不限制JOIN [英] SQL limit SELECT but not JOIN

问题描述

我正在对BD执行分页.我的问题是当我想限制SELECT语句而不是JOIN时.例如，一个产品可以得到很多价格:

I'm implementing pagination on my BD. My problem is when I want limit the SELECT statement but not the JOIN. Example, a product can got many prices:

SELECT * FROM product
LEFT JOIN price ON product.id == price.id_product
LIMIT 20

但是我想获得20个产品，每个产品及其价格.如何限制SELECT语句，但不限制LEFT JOIN语句.

But I want to get 20 products with each one with their prices. How I can limit the statement SELECT, but not LEFT JOIN.

示例:

product             price.id  price.id_pruct  price.price
   1                   1            1               50
   2                   2            1               30
   3                   3            1               40
                       4            1               20
                       5            2               30

SELECT * FROM product
LEFT JOIN price ON product.id == price.id_product
LIMIT 3

返回:

product  price.id id_prodcut price
1          1           1      50
1          2           1      30
1          3           1      40

但是我想要

product  price.id id_prodcut price
1          1           1      50
1          2           1      30
1          3           1      40
1          4           1      20
2          5           2      30
3          .            .     .

三种产品(限量3种)

谢谢.我希望你能帮助我.

Thanks. I hope you can help me.

推荐答案

我将编写一个子查询来获得三个前三个产品(或您选择的任何条件)，如下所示:

I would write a subquery to get the three first products (or whatever condition you choose) like this:

SELECT id
FROM product
ORDER BY id
LIMIT 3;

有了这个，只要ID在该子查询中，我就可以从价格表中选择所有内容.您可以使用联接来做到这一点:

Once I have that, I can select everything from the price table as long as the id is in that subquery. You can do this using a join:

SELECT p.*
FROM price p
JOIN(
   SELECT id
   FROM product
   ORDER BY id
   LIMIT 3) tmp ON tmp.id = p.product_id;

这是一个使用示例数据的 SQL Fiddle 示例，我还添加了一个不会返回的行，因此您可以看到它起作用.

Here is an SQL Fiddle example using your sample data, and I also added a row that won't be returned so you can see that it works.

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