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

发布于 2024-08-07 06:59:47 字数 355 浏览 0 评论 0原文

我有一个像这样的表格：

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

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

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

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅暮の光 2024-08-14 06:59:47

SELECT  mo.Letter, md.Rank
FROM    (
        SELECT  DISTINCT letter
        FROM    mytable
        ) mo
JOIN    mytable md
ON      md.Letter >= mo.Letter
        AND md.Letter <= mo.Letter
        AND Rank <=
        COALESCE
                (
                (
                SELECT  Rank
                FROM    mytable mi
                WHERE   mi.letter = mo.letter
                ORDER BY
                        Rank
                LIMIT 1, 1
                ),
                0xFFFFFFFF
                )

您需要在 (Letter, Rank) （按此顺序）上有一个复合索引，

请注意此构造：

md.Letter >= mo.Letter
AND md.Letter <= mo.Letter

而不是仅仅 md.Letter = mo.Letter

它强制 对每条记录进行范围检查，这样效率更高。

请参阅我的博客中的这篇文章：

高级行采样

了解更多详细信息。

SELECT  mo.Letter, md.Rank
FROM    (
        SELECT  DISTINCT letter
        FROM    mytable
        ) mo
JOIN    mytable md
ON      md.Letter >= mo.Letter
        AND md.Letter <= mo.Letter
        AND Rank <=
        COALESCE
                (
                (
                SELECT  Rank
                FROM    mytable mi
                WHERE   mi.letter = mo.letter
                ORDER BY
                        Rank
                LIMIT 1, 1
                ),
                0xFFFFFFFF
                )

You need to have a composite index on (Letter, Rank) (in this order)

Note this construct:

md.Letter >= mo.Letter
AND md.Letter <= mo.Letter

instead of mere md.Letter = mo.Letter

It forces Range checked for each record which is more efficient.

See this article in my blog:

Advanced row sampling

for more details on this.

回复收藏 0 原文

找回味觉 2024-08-14 06:59:47

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

编辑：（我的第一次尝试在 MySql 上不起作用（Quassnoi 评论），我将其修改为在 sql server 上工作）

第二次尝试：

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

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

回复收藏 0 原文

~没有更多了~