mysql - 如何在 LEFT JOIN 上使用 LIMIT?
问题描述
我有 2 个表被调用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',
]
]
],
...
]
我 Laravel 雄辩,我可以使用这样的东西:
$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
表中获得 2 行,然后尝试LEFT JOIN
从那categories
2 行开始!这会导致产品返回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_id'
我无法CT.category_id
在子查询中访问。
什么是最好的解决方案?
解决方案
在 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<>fiddle。
在 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
注意:您可以通过调整两个查询中的子句来指定您想要的 2 个产品(例如,按字母顺序排列的前两个) 。
编辑:
在 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<>fiddle 。
您也可以使用此方法:
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
. 但是,如果每个类别没有太多产品,那么这会很好(使用正确的索引)。
推荐阅读
- flutter - 保存当前路线并在 Flutter 中弹出前一条
- amazon-web-services - 从没有哈希和范围键的 DynamoDB 中找到一行并将其删除
- php - 如何指定与 Doctrine 固定装置的数据库连接?
- c - 我的功能之一是不小心从列表中删除元素
- c++ - 指针成员未在复制构造函数中初始化
- google-cloud-platform - GCP:“计算引擎未准备好”
- javascript - 计算经过的时间并将其放入 JavaScript 中的 Days:Hours:Minutes:Seconds 格式,其中经过的时间每秒更新一次
- elasticsearch - 使用弹性搜索 py 批量更新无类型
- php - MYSLI_QUERY 返回 false 但 MYSQLI_ERROR 从 5.6 更新 php 7.2 后未显示错误
- java - 如何实现一个持续活跃的循环来检查变化(布尔值)?