如何使用左连接限制？

vsnjm48y 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(343)

我有两张table叫 categories 以及 products 在这种关系下： categories.category_id = products.product_category_id 我想显示他们的2个产品的所有类别！

[
    [category 1] =>
            [
                'category_id' => 1,
                'category_title' => 'category_title 1',
                [products] => [
                    '0' => [
                            'product_category_id' => 1,
                            'product_id' => 51,
                            'product_title' => 'product_title 1',
                        ],
                    '1' => [
                            'product_category_id' => 1,
                            'product_id' => 55,
                            'product_title' => 'product_title 2',
                        ]
                ]
            ],
    [category 2] =>
            [
                'category_id' => 2,
                'category_title' => 'category_title 2',
                [products] => [
                    '0' => [
                            'product_category_id' => 2,
                            'product_id' => 32,
                            'product_title' => 'product_title 3',
                        ],
                    '1' => [
                            'product_category_id' => 2,
                            'product_id' => 33,
                            'product_title' => 'product_title 4',
                        ]
                ]
            ],
    ...
]

我很能言善辩我可以用这样的话：

$categories = Category::with(['products' => function($q) {
    $q->limit(2)
}])->get();

但是我没有使用laravel，我需要纯sql代码！我试过这个代码：

SELECT
    CT.category_id,
    PT.product_category_id,
    CT.category_title,
    PT.product_id,
    PT.product_title
FROM
    categories CT
LEFT JOIN(
SELECT
    *
FROM
    products 
    LIMIT 2
) AS PT
ON
    CT.category_id = PT.product_category_id
WHERE
    CT.category_lang = 'en'

但是这个代码有问题！似乎mysql首先在 products 然后试着 LEFT JOIN 从 categories 去那两排！这个原因又回来了 null 产品的值（如果我删除 LIMIT 很好，但我需要 LIMIT )
我也测试过这个代码：（更新）

SELECT
    CT.category_id,
    PT.product_category_id,
    CT.category_title,
    PT.product_id,
    PT.product_title
FROM
    categories CT
LEFT JOIN(
    SELECT
        *
    FROM
        products
    WHERE
        product_category_id = CT.category_id
    LIMIT 5
) AS PT
ON
    CT.category_id = PT.product_category_id
WHERE
    CT.category_lang = 'en'

但我收到了一个错误：

1054-“where子句”中的未知列“ct.category\u id”

我无法访问 CT.category_id 在子查询中。
最好的解决方案是什么？

mysql mariadb eloquent relational-database orm

来源：https://stackoverflow.com/questions/53969692/how-to-use-limit-on-left-join

1条答案

按热度按时间

2j4z5cfb1#

在mysql中，需要使用变量。其基本思想是：

select p.*
from (select p.*,
             (@rn := if(@c = product_category_id, @rn + 1,
                        if(@c := product_category_id, 1, 1)
                       )
             ) as rn
      from (select p.* 
            from products p
            order by p.product_category_id
           ) p cross join
           (select @c := -1, @rn := 0) params
     ) p
where rn <= 2;

您可以将其用作子查询，然后加入类别信息的其余部分。
这是一个db<>小提琴如果这个代码工作。
在mysql 8+/mariadb 10.2+中，更容易编写为：

select p.*
from (select p.*,
             row_number() over (partition by product_category_id order by product_category_id) as seqnum
      from products p
     ) p
where seqnum <= 2;

注意：您可以通过调整 order by 两个查询中的子句。
编辑：
在mariadb 10.2中，显然不能使用子查询进行排序，因此适当的查询应该是：

select p.*
from (select p.*,
             (@rn := if(@c = product_category_id, @rn + 1,
                        if(@c := product_category_id, 1, 1)
                       )
             ) as rn
      from products p cross join
           (select @c := -1, @rn := 0) params
      order by p.product_category_id
     ) p
where rn <= 2;

这是db的小提琴。
也可以使用此方法：

select p.*
from products p
where (select count(*)
       from products p2
       where p2.product_category_id = p.product_category_id and p2.id <= p.id
      ) <= 2;

我并不推荐这样做，因为相关子查询的性能通常比 order by . 但是，如果每个类别没有太多的产品，那么就可以了（有正确的索引）。

赞(0）回复(0）举报 2021-06-17

我来回答

如何使用左连接限制？

1054-“where子句”中的未知列“ct.category\u id”

1条答案

相关问题

热门标签

最新问答