如何获取每个类别的前n条记录

Question

我来这里是为了根据类别获取记录。

我的表 foo 有字段 [id, name, class]。我的记录可以是这样的：

1, ram, 10
2, hari, 9
3, sita, 10
4, gita, 9
5, rita, 5
6, tina, 7
8, nita, 8
9, bita, 5
10,seta, 7

...以及更多...

现在我想获得来自不同类别的每条记录的结果..即类似于

1, ram, 10
2, hari, 9
5, rita, 5
6, tina, 7
8, nita, 8

每个类别的前 1 记录

Answer 1

对于 SQL Server 2005+ 和 Oracle 9i+，使用分析函数：

WITH summary AS (
  SELECT f.id,
         f.name,
         f.class,
         ROW_NUMBER() OVER (PARTITION BY f.class
                                ORDER BY f.name) AS rank
    FROM FOO f)
SELECT s.id,
       s.name,
       s.class
  FROM summary s
 WHERE s.rank = 1

这还使用公共表表达式 (CTE)，在 Oracle 中称为子查询分解...

MySQL 不支持分析函数，因此您必须使用：

SELECT x.id,
       x.name,
       x.class
  FROM (SELECT f.id,
               f.name,
               f.class,
               CASE 
                 WHEN @class = f.class THEN @rownum := @rownum + 1 
                 ELSE @rownum := 1
               END AS rank,
               @class := f.class
          FROM FOO f
          JOIN (SELECT @rownum := 0, @class := '') r
      ORDER BY f.class, f.name) x
 WHERE x.rank = 1

Answer 2

这应该是最简单的方法，它不涉及任何特定于数据库的选项：

select * 
  from foo 
 where id in (select min(id) 
                from foo 
               group by class);

upd：是的，当然，只有当您只需要每个类的一条记录时，这才有效。

upd2：只是为了好玩，提出一个查询，它会显示 TOP N，并且不涉及分析。看起来有点乱，但似乎有效:)

select newfoo.id, newfoo.name, newfoo.class
  from (select class, max(r) top, min(r) bottom
          from (select f.*, rownum r
                  from (select id, name, class from foo order by class, id asc) f)
         group by class) minmax,
       (select id, name, class, r
          from (select f.*, rownum r
                  from (select id, name, class from foo order by class, id asc) f)) newfoo
 where newfoo.class = minmax.class
   and newfoo.r between minmax.bottom and
       least(minmax.bottom + (TOP_N-1), minmax.top);

其中 TOP_N 是您需要获取的记录数量。

Answer 3

我在 sql 2008 中对此进行了测试并且对我有用，希望能以某种方式帮助您。

DECLARE @Class TABLE
(
    id INT
    ,Name NVARCHAR(120)
    ,Class INT

    PRIMARY KEY (id)
)

INSERT INTO @Class values (1, 'ram', 10)
INSERT INTO @Class values (2, 'hari', 9)
INSERT INTO @Class values (3, 'sita', 10)
INSERT INTO @Class values (4, 'gita', 9)
INSERT INTO @Class values (5, 'rita', 5)
INSERT INTO @Class values (6, 'tina', 7)
INSERT INTO @Class values (8, 'nita', 8)
INSERT INTO @Class values (9, 'bita', 5)
INSERT INTO @Class values (10, 'seta', 7)

SELECT A.id, A.Name, A.Class
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY Class ORDER BY ID) as Num, ID, Name, Class
    FROM @Class
) A
WHERE A.Num = 1
ORDER BY id

Answer 4

对于 SQL Server 或 Oracle（或实现该标准部分的任何其他引擎，包括免费引擎中的 PostgreSQL），OVER 子句中的“窗口函数”（例如，参见 此处有关 MS 的文档）使其变得简单；例如，在这个SO问题中，请参阅@Darrel的答案（他正在选择每个类别的前 10 名，您只想要前 1 名，变化应该很明显；-)。

在 MySql 或其他不符合 OVER 子句标准的引擎中，您可以使用 @Bill 的答案（对 MySql 有利，对其他人不利）或 @Matt 的答案（可能需要稍作调整，因为他正在回答SQL Server 等使用 SELECT TOP 10 ... - 在 MySql 中将是 SELECT ... LIMIT 10！-)。

Answer 5

这是另一种方法

    DECLARE @foo TABLE(ID INT,Name VARCHAR(20),Class INT)
INSERT INTO @foo
SELECT 1,'ram', 10 UNION ALL
SELECT 2, 'hari', 9 UNION ALL 
SELECT 3, 'sita', 10  UNION ALL
SELECT 4, 'gita', 9  UNION ALL
SELECT 5, 'rita', 5  UNION ALL
SELECT 6, 'tina', 7  UNION ALL
SELECT 8, 'nita', 8  UNION ALL
SELECT 9, 'bita', 5  UNION ALL
SELECT 10,'seta', 7

SELECT DISTINCT X.*
FROM @foo f
CROSS APPLY(SELECT TOP 1 * FROM @foo WHERE Class = f.Class) AS X