sqlite – sql查询 – 如何在组中应用限制

它具有以下数据：

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

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

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

我写什么查询,以便它适用于CID上的组,而不是每组返回1个单个结果,它每组最多返回2个结果.还有条件是得分> = 20,我想要由CID和SortKey排序的结果.

如果我不得不在上面的数据上运行我的查询,我会期望以下结果：

C1的结果 – 注意：ROWID 1不被认为是其得分< 20

C1,2
C1,3

注意：ROWID 5出现在ROWID 4之前,因为ROWID 5的值较小
SORTKEY

C2,2
C2,3

C3的结果 – 注意：ROWID 6不显示,因为它的分数小于20,所以只有1条记录返回到这里

C3,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列表.因为相关的子查询是针对每一行执行的,无论是否包含在结果中,它可能不如下面给出的窗口函数版本那么有效 – 但不同于该版本,它将在SQLite3上工作,该窗体不支持窗口功能.

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

我在PostgreSQL 9.2和SQLite3 3.7.11中测试了以上内容两者都可以正常工作.它不会在MySQL 5.5或最新的5.6里程碑上工作,因为MySQL不支持使用IN的子查询中的LIMIT.

SQLFiddle演示：

> PostgreSQL(工作正常)：http://sqlfiddle.com/#!12/22829/3
> SQLite3(工作正常,相同的查询文本,但由于明显的JDBC驱动程序限制,需要单值插入)：http://sqlfiddle.com/#!7/9ecd8/1
> MySQL 5.5(失败了两种方式; MySQL不喜欢一个.“ROWID”引用甚至在ANSI模式,所以我不得不引用;然后失败与此版本的MySQL不支持“限制和输入/ ALL / ANY / SOME子查询)：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进行过滤,只需向外部WHERE子句添加AND“CID”=’C1′.

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

由于这是原来标记只是SQL(没有SQLite)…只是为了完整性,在PostgreSQL或其他DB与SQL标准窗口功能支持我可能会这样做：

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,只需向内部的WHERE子句添加AND“CID”=’C1′.

BTW,您的测试数据不足,因为任何具有得分>的CID不能有两行以上20反正

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!