我有一个表product_category,包含字段id,name,parent_id和level.
例如,类别Agriculture具有id = 75,level = 1和parent_id = NULL.子类别Corn,Wheat,Rye等具有level = 2和parent_id = 75.
在我的网站中,我想显示顶级类别,每个类别下方只有5个子类别.但是进行查询以检索它们比我想象的更难.
如果我执行以下查询:
SELECT a.name, a.parent_id FROM product_category a
WHERE (
SELECT b.level
FROM product_category b
WHERE b.id = a.parent_id
LIMIT 1
) = 1
我检索所有顶级类别和子类别,但是有数千个子类别,所以当我只想要每个子类别中的前5个时,它将非常不必要地昂贵.
如果我做以下一个:
SELECT a.name, a.parent_id FROM product_category a
WHERE (
SELECT b.level
FROM product_category b
WHERE b.id = a.parent_id
LIMIT 1
) = 1
LIMIT 5
它只检索5个子类别,而不是每个顶级类别的5个子类别.
然后我想到了以下方式:
(
SELECT a.name, a.parent_id FROM product_category a
WHERE parent_id = 12
LIMIT 5
) UNION (
SELECT a.name, a.parent_id FROM product_category a
WHERE parent_id = 21
LIMIT 5
) UNION (
SELECT a.name, a.parent_id FROM product_category a
WHERE parent_id = 75
LIMIT 5
) UNION (
.
.
.
这看起来非常脏和硬编码,但它是我现在能想到的唯一方式.还有其他解决方案吗?
谢谢!
解决方法:
以下是每个根类别最多返回两个子类别的示例:
select parent.name as Category
, child.name as SubCategory
from (
select name
, parent_id
, @rn := if(@cur = parent_id, @rn+1, 1) as rn
, @cur := parent_id
from product_category pc
join (select @rn := 0, @cur := '') i
where level = 2
order by
parent_id
, id
) as child
join product_category as parent
on child.parent_id = parent.id
where child.rn < 3
标签:sql,mysql,greatest-n-per-group
来源: https://codeday.me/bug/20190901/1782019.html