MySQL Group By每个类别的前N个数字 [英] MySQL Group By with top N number of each kind

问题描述

我有这样一张表：

 
等级信
 1 A 
 2 A 
 3 B 
 4 A 
 5 C 
 6 A 
 7 C 
 8 C 
 9 B 
 10 C 
 
 
 
 
我需要按升序排列的每封信的前2个： 
 
 
 
等级信
 1 A 
 2 A 
 3 B 
 5 C 
 7 C 
 9 B 
 
我该怎么做？使用GROUP BY获得最上面的1是相当直接的，但我似乎无法让它在多个条目中工作
解决方案

解决方案

按等级排列）
按字母顺序排列，等级
  

编辑：（我的第一次尝试不会在MySql上工作（Quassnoi评论），我修改它在SQL服务器上工作的例子）

第二次尝试：

  select t.letter，t.rank 
 from table1 t 
 join（
 select t1 .letter，min（t1.rank）m 
 from table1 t1 
 join（从表1中选择t0.letter，min（t0.rank）m，count（1）c 
 t0 group by （t2.c = 1）或（t2.c> 1和t1.rank> m））
组t1乘以t1（t0.letter）t2 
 t1.letter = t2.letter .letter）t3 
在t.letter = t3.letter和t.rank <= t3.m 
  

I have a table like this:

Rank      Letter
1         A
2         A
3         B
4         A
5         C
6         A
7         C
8         C
9         B
10        C 

And I need the top 2 of each letter ordered by ascending rank:

Rank      Letter
1         A
2         A
3         B
5         C
7         C
9         B

How would I do it? It's fairly straightforward to get just the top 1 using GROUP BY, but I can't seem to get it working for multiple entries

解决方案

select distinct rank, letter
  from table1 t2
 where rank in 
         (select top 2 rank
            from table1 t2 
           where t2.letter = t1.letter 
           order by rank)
       order by letter, rank

EDIT: (my first try won't work on MySql (Quassnoi comment), I modified it to work on sql server for example)

second try:

select t.letter, t.rank
from table1 t
join (
    select t1.letter, min(t1.rank) m
    from table1 t1
    join (select t0.letter, min(t0.rank) m, count(1) c 
           from table1 t0 group by t0.letter) t2
    on t1.letter = t2.letter and ((t2.c = 1) or (t2.c > 1 and t1.rank > m))
    group by t1.letter) t3 
  on t.letter = t3.letter and t.rank <= t3.m

这篇关于MySQL Group By每个类别的前N个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！