Mysql limit per parent 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    

Parent is not unique.

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

for example table structure is:

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

回答1:

Try this:

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

EDIT

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;

回答2:

You can simply do it lie this

SET @level = 0;
SET @group = '';

SELECT 
    name
FROM (
    SELECT 
        name ,
        parent
        @level := IF(@group = parent, @level+1, 1) AS level, 
        @group := parent as EGroup 
    FROM test
    WHERE parent IN ("1,2,3") 
    ORDER BY parent 
) rs
WHERE level < 11