MySQL Group By 每种类型的前 N ​​个数字

本文介绍了MySQL Group By 每种类型的前 N ​​个数字的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一张这样的桌子:

<前>等级字母1 安2A3 乙4A5℃6A7 C8C9 乙10℃

我需要按升序排列的每个字母的前 2 个:

<前>等级字母1 安2A3 乙5℃7 C9 乙

我该怎么做?使用 GROUP BY 只获得前 1 名相当简单，但我似乎无法让它适用于多个条目

解决方案

select distinct rank, letter从表1 t2排名在哪里(选择前 2 名从表1 t2其中 t2.letter = t1.letter按等级排序)按字母排序，排名

(我的第一次尝试不适用于 MySql(Quassnoi 评论)，我将其修改为在 sql server 上工作例如)

第二次尝试:

选择t.letter, t.rank从表 1 t加入 (选择 t1.letter, min(t1.rank) m从表 1 t1加入 (选择 t0.letter, min(t0.rank) m, count(1) c从 table1 t0 group by t0.letter) t2在 t1.letter = t2.letter 和 ((t2.c = 1) 或 (t2.c > 1 and t1.rank > m))按 t1.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屋！