每个父ID的MySQL限制 [英] Mysql limit per parent id

问题描述

如何限制WHERE子句中每个id的结果? 我的查询是:

How can I limit result per each id in WHERE clause? My query is:

SELECT name 
FROM location_areas 
WHERE parent IN ("1,2,3") 
ORDER BY popularity,name    

父母不是唯一的

我需要在WHERE子句中为每个父级id获得10个结果.

I need to get 10 results for each parent id in WHERE clause.

例如表结构为:

id   name   parent
1    name    0
2    name    1
3    name    1
4    name    80
5    name    80
6    name    80
7    name    80
8    name    1

推荐答案

尝试一下:

SELECT
   T.name,
   T.popularity,
   T.parent,
   T.rank
FROM
(
SELECT
   L.name,
   L.popularity,
   L.parent,
   @rank := IF(@parent = parent, @rank + 1, 1) rank,
   @parent := parent
FROM location_areas L,
(SELECT @rank := 1, @parent := NULL) R
) T
WHERE T.rank <= 10

编辑

SELECT T.name, T.popularity, T.parent, T.level, T.rank
FROM (
         SELECT L.name, L.popularity,
             L.parent, L.level,
             @rank := IF(@parent = parent, @rank + 1, 1) rank,
             @parent := parent
         FROM location_areas L,
             (SELECT @rank := 1, @parent := NULL) R
         WHERE L.parent IN (".$ids.")
     ) T WHERE T.rank <= 10;

这篇关于每个父ID的MySQL限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！