SQL - 每种类型只给我 3 次点击

Question

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

我有一个表，其中一列名为 type。我想为该列中的每种类型选择 3 条记录。这可能吗？

另请注意，我使用的是 MySQL 和 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 条记录）：

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

Answer 1

当表很大并且集合更加不可预测时，需要在内部查询中按类型对行编号进行排序，以使副作用变量发挥作用。

select id, title, type
from (select id, title, type,
        @r := CASE WHEN @g = type THEN @r+1 ELSE 1 END r,
        @g := type
      from tbl
      order by type, title) as x
where row_number <= 3
# order by type, title

如果没有两条记录在（标题、类型、id）上完全相同，则另一种不使用副作用变量的方法如下所示。这仅使用标准 ANSI SQL92 SQL。但它可能比上面的慢。

select A.id, A.title, A.type
from tbl A
left join tbl B on
   A.title = B.title and
   (A.type < B.type or
   (A.type = B.type and A.id < A.id))
group by A.id, A.title, A.type
having count(B.title) <= 2

Answer 2

查看

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+

查询：

select type, variety, price
from fruits
where (
   select count(*) from fruits as f
   where f.type = fruits.type and f.price < fruits.price
) <= 2;

Answer 3

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

（使用另一篇文章 与 Martin Wickman 的答案在同一网站上！）

Answer 4

如果您在 (type, title) 上有索引，并且您知道 type 的可能值，我相信动态 SQL 是（一次）用于最佳表现。

对于 type 的每个可能值，为该特定类型添加 union all 和 select。最终查询将类似于以下查询：

(select * from t1 where type = 'string1' order by title limit 3)
  union all
(select * from t1 where type = 'string2' order by title limit 3)
  union all
(select * from t1 where type = 'string3' order by title limit 3);

它在包含 1,000,000 行的表上执行不到 1 秒，而其他解决方案（Martins 和 Cyber​​kiwis）大约需要 11 秒。

区别在于，上面的联合查询可以获取每种类型的前三个标题条目，然后停止，而模拟分析函数必须扫描整个表。