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

本文介绍了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分组，但不是每组返回一个单一结果，它每个组最多返回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 简而言之，我想在一个GROUP BY中进行限制。我想要最简单的解决方案，并希望避免临时表。子查询很好。另请注意，我为此使用了SQLite。解决方案以下是一个相当便于查询的功能： SELECT * FROM table1 a WHERE a。ROWIDIN（ SELECT b。ROWID FROM table1 b WHERE b。Score> = 20 AND b。ROWID不是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不象 a。ROWID即使在 ANSI 模式，所以我不得不取消引用;然后它失败，使用这个版本的MySQL还不支持'LIMIT& IN / ALL / ANY / SOME子查询） ： http://sqlfiddle.com/#!2/e1f31/2 S QLite演示显示它在SQLite3命令行上工作得很好： http://pastebin.com/26n4NiUC 输出（PostgreSQL）： ------- + ----- + ----- + ------- + --------- 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行） 如果您想过滤特定 CID ，只需将和CID='C1'或其他任何内容添加到外部 WHERE 子句。 下面是一个与更详细的例子密切相关的答案： https://stackoverflow.com/a/13411138/398670 因为这最初标记为 SQL （没有SQLite）......为了完整性，在PostgreSQL或其他具有SQL标准窗口函数支持的数据库中，我可能会这样做： 选择ROWID，CID，PID，Score，SortKey FROM SELECT *，row_number（）OVER（PARTITION BYCIDORDER BYSortKey）AS n FROM table1 WHEREScore> = 20 ）x WHERE n< 3 ORDER BYCID，SortKey; 产生相同的结果。 SQLFiddle，包括额外的 C1 行来演示限制过滤器的实际工作原理： http://sqlfiddle.com/#!12/22829/1 如果您想过滤特定的 CID ，只需添加和CID='C1'或任何内容 WHERE 子句。 顺便说一句，您的测试数据不足，无论如何，对于任何得分> 20的任何CID，都不能超过两行。 I have a table named t1 with following fields: ROWID, CID, PID, Score, SortKeyit has the following data:1, C1, P1, 10, 12, C1, P2, 20, 23, C1, P3, 30, 34, C2, P4, 20, 35, C2, P5, 30, 26, C3, P6, 10, 17, C3, P7, 20, 2what query do I write so that it applies group by on CID, but instead of returning me 1 single result per group, it returns me a max of 2 results per group. also where condition is score >= 20 and I want the results ordered by CID and SortKey.If I had to run my query on above data, I would expect the following result:RESULTS FOR C1 - note: ROWID 1 is not considered as its score < 20C1, P2, 20, 2C1, P3, 30, 3RESULTS FOR C2 - note: ROWID 5 appears before ROWID 4 as ROWID 5 has lesser value SortKeyC2, P5, 30, 2C2, P4, 20, 3RESULTS FOR C3 - note: ROWID 6 does not appear as its score is less than 20 so only 1 record returned hereC3, P7, 20, 2IN SHORT, I WANT A LIMIT WITHIN A GROUP BY. I want the simplest solution and want to avoid temp tables. sub queries are fine. Also note I am using SQLite for this. 解决方案 Here's a fairly portable query to do what you want: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";The query uses a correlated subquery with a sort and limit to produce a list of ROWIDs that should appear in the final result. Because the correlated subquery is executed for every row, whether or not it's included in the result, it may not be as efficient as the window function version given below - but unlike that version it'll work on SQLite3, which doesn't support window functions.This query requires that ROWID is unique (can be used as a primary key).I tested the above in PostgreSQL 9.2 and in SQLite3 3.7.11 ; it works fine in both. It won't work on MySQL 5.5 or the latest 5.6 milestone because MySQL doesn't support LIMIT in a subquery used with IN.SQLFiddle demos:PostgreSQL (works fine): http://sqlfiddle.com/#!12/22829/3SQLite3 (works fine, same query text, but needed single-valued inserts due to apparent JDBC driver limitation): http://sqlfiddle.com/#!7/9ecd8/1MySQL 5.5 (fails two ways; MySQL doesn't like a."ROWID" quoting even in ANSI mode so I had to un-quote; then it fails with This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery): http://sqlfiddle.com/#!2/e1f31/2SQLite demo showing it works just fine on the SQLite3 command line: http://pastebin.com/26n4NiUCOutput (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)If you want to filter for a particular CID, just add AND "CID" = 'C1' or whatever to the outer WHERE clause.Here's a closely related answer with more detailed examples: https://stackoverflow.com/a/13411138/398670Since this was originally tagged just SQL (no SQLite)... just for completeness, in PostgreSQL or other DBs with SQL-standard window function support I'd probably do this:SELECT "ROWID", "CID", "PID", "Score", "SortKey"FROM ( SELECT *, row_number() OVER (PARTITION BY "CID" ORDER BY "SortKey") AS n FROM table1 WHERE "Score" >= 20) xWHERE n < 3ORDER BY "CID", "SortKey";which produces the same result. SQLFiddle, including extra C1 row to demonstrate that the limiting filter actually works: http://sqlfiddle.com/#!12/22829/1If you want to filter for a particular CID, just add AND "CID" = 'C1' or whatever to the inner WHERE clause.BTW, your test data is insufficient, since it can never have more than two rows for any CID with score > 20 anyway. 这篇关于SQL查询 - 如何在组内应用限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！