从每种类型中选择X个项目 [英] Select X items from every type

问题描述

我有一个mysql数据库，其中有1个表. 该表包含"id"，名称"，类型".

I have a mysql database, in there I have 1 table. That table contains "id","Name","Type".

我想从每种类型中获取10行.例如:

I want to get 10 rows from every type. For example:

(在此示例中，我只想要1个)

( In this example I only want 1)

0 - test1 - a
1 - test2 - a
2 - test3 - b

为此，需要选择:

0 - test1 - a
2 - test3 - b

如何在1个SQL查询中执行此操作?还是不可能?

How can I do this in 1 SQL query? Or is that impossable?

推荐答案

尝试此查询-

SELECT id, name, type
  FROM (
  SELECT t1.*, COUNT(*) cnt FROM table t1
    LEFT JOIN table t2
      ON t2.type = t1.type AND t2.id <= t1.id
    GROUP BY
      t1.type, t1.id
  ) t
WHERE cnt <= 10;

这篇关于从每种类型中选择X个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！