SQL查询 - 如何在组内应用限制

我有一个名为t1的表,其中包含以下字段:ROWID,CID,PID,Score,SortKey

它有以下数据:

1, C1, P1, 10, 1
2, C1, P2, 20, 2
3, C1, P3, 30, 3

4, C2, P4, 20, 3
5, C2, P5, 30, 2

6, C3, P6, 10, 1
7, C3, P7, 20, 2

我写了什么查询,以便它在CID上应用group by,但不是每组返回1个单个结果,而是每组返回最多2个结果.还有条件得分> = 20,我希望结果由CID和SortKey排序.

如果我必须对上面的数据运行我的查询,我会期望以下结果:

结果为C1 - 注意:ROWID 1不被视为其得分<20

C1, P2, 20, 2
C1, P3, 30, 3

结果为C2 - 注意:ROWID 5出现在ROWID 4之前,因为ROWID 5的值较小SortKey

C2, P5, 30, 2
C2, P4, 20, 3

C3的结果 - 注意:ROWID 6没有出现,因为它的分数小于20,所以这里只返回1条记录

C3, P7, 20, 2

简而言之,我想在一个分组旁边限制.我想要最简单的解决方案,并希望避免临时表.子查询很好.另请注意,我正在使用SQLite.

这是一个相当便携的查询来做你想要的:

SELECT *
FROM table1 a 
WHERE a."ROWID" IN (
    SELECT b."ROWID" 
    FROM table1 b 
    WHERE b."Score" >= 20 
      AND b."ROWID" IS NOT NULL 
      AND a."CID" = b."CID" 
    ORDER BY b."CID", b."SortKey" 
    LIMIT 2
)
ORDER BY a."CID", a."SortKey";

该查询使用具有排序和限制的相关子查询来生成ROWID应出现在最终结果中的s 列表.因为相关子查询是针对每一行执行的,无论它是否包含在结果中,它可能不如下面给出的窗口函数版本那样高效 - 但与该版本不同,它将适用于SQLite3,它不支持窗口功能.

此查询要求ROWID唯一(可用作主键).

我在PostgreSQL 9.2和SQLite3 3.7.11中测试了上述内容; 它在两者都很好.它不适用于MySQL 5.5或最新的5.6里程碑,因为MySQL不支持LIMIT使用的子查询IN.

SQLFiddle演示:

PostgreSQL(工作正常):http://sqlfiddle.com/#!12/22829/3

SQLite3(工作正常,查询文本相同,但由于明显的JDBC驱动程序限制,需要单值插入):http://sqlfiddle.com/#!7/9ecd8/1

MySQL 5.5(两种方式失败; MySQL不喜欢a."ROWID"在ANSI模式中引用,所以我不得不引用;然后它失败了This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery):http://sqlfiddle.com/#!2/e1f31/2

SQLite演示显示它在SQLite3命令行上运行得很好:http://pastebin.com/26n4NiUC

输出(PostgreSQL):

 ROWID | CID | PID | Score | SortKey 
-------+-----+-----+-------+---------
     2 | C1  | P2  |    20 |       2
     3 | C1  | P3  |    30 |       3
     5 | C2  | P5  |    30 |       2
     4 | C2  | P4  |    20 |       3
     7 | C3  | P7  |    20 |       2
(5 rows)

如果要筛选特定的CID,只需添加AND "CID" = 'C1'或者外部 WHERE子句.

这是一个密切相关的答案,更详细的例子:https://stackoverflow.com/a/13411138/398670

由于这最初被标记SQL(没有SQLite)...只是为了完整性,在PostgreSQL或其他具有SQL标准窗口函数支持的DB中我可能会这样做:

SELECT "ROWID", "CID", "PID", "Score", "SortKey"
FROM (
  SELECT *, row_number() OVER (PARTITION BY "CID" ORDER BY "SortKey") AS n
  FROM table1
  WHERE "Score" >= 20
) x
WHERE n < 3
ORDER BY "CID", "SortKey";

产生相同的结果.SQLFiddle,包括额外的C1行来演示限制过滤器实际工作:http://sqlfiddle.com/#!12/22829/1

如果您要过滤特定CID,只需添加AND "CID" = 'C1'或任何的内部 WHERE条款.

顺便说一句,您的测试数据不足,因为对于任何得分> 20的CID,它永远不会超过两行.