SQL-仅针对每种类型给我3个匹配

问题描述

我有某种不可能的要求:).

I have some kind of impossible request :).

我有一张表，其中的一列被命名为type.我想为该列中的每种类型选择3条记录.有可能吗?

I have a table where one of the columns is named type. I would like to SELECT 3 records for each type in that column. Is that possible?

还请注意，我正在使用MySQL和Sphinx.

Note also that I'm using MySQL and Sphinx.

更新:表格结构

id       title        type
1        AAAA         string1
2        CCCC         string2
3        EEEE         string2
4        DDDD         string2
5        FFFF         string2
6        BBBB         string2
6        BBBB         string2

我希望我的MySQL返回的是(按标题排序的每种类型最多3条记录):

What I want my MySQL to return is (up to 3 records for each type ordered by title):

id       title        type
1        AAAA         string1
6        BBBB         string2
2        CCCC         string2
4        DDDD         string2

推荐答案

select id, title, type
from   (select id, title, type,
               @num := if(@group = type, @num + 1, 1) as row_number,
               @group := type as dummy
        from   your_table
        order by type, title) as x
where  row_number <= 3

(使用不同文章与马丁·威克曼的答案！)

这篇关于SQL-仅针对每种类型给我3个匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！