如何查询具有两个不同列的MAX子查询的最小值？

Question

我有一张这样的桌子：

+---------------+--------------+------+-----+----------+
| Field         | Type         | Null | Key | Default  |
+---------------+--------------+------+-----+----------+
| id            | smallint(6)  | NO   | PRI | NULL     |
| Book          | tinyint(4)   | NO   |     | NULL     |
| Chapter       | smallint(6)  | NO   |     | NULL     |
| Paragraph     | smallint(6)  | NO   |     | NULL     |
| Text          | text         | YES  |     | NULL     |
| RevisionNum   | mediumint(9) | NO   | PRI | NULL     |
+---------------+--------------+------+-----+----------+

MySQL>按id限制11从MyTable顺序中选择id、书籍、章节、段落、RevisionNum；

+-----+------+---------+-----------+-------------+
| id  | Book | Chapter | Paragraph | RevisionNum |
+-----+------+---------+-----------+-------------+
|   1 |    1 |       1 |         1 |           0 |
|   1 |    1 |       1 |         1 |           1 |
|   1 |    1 |       1 |         1 |           2 |
|   2 |    1 |       2 |         2 |           0 |
|   2 |    1 |       2 |         2 |           1 |
|   2 |    1 |       2 |         2 |           2 |
|   2 |    1 |       2 |         2 |           3 |
|   3 |    1 |       2 |         3 |           0 |
|   4 |    1 |       2 |         4 |           0 |
|   4 |    1 |       2 |         4 |           1 |
|   5 |    1 |       3 |         5 |           0 |
+-----+------+---------+-----------+-------------+

要找到没有未修改段落的书籍或章节，我希望查询该章节或书籍的所有不同id的最大值的最小值，或者以某种方式确定没有id保持未编辑（MAX（RevisionNum）为零）。

我的大多数约会尝试都以这样的错误告终：

SELECT DISTINCT Book,RecordNum FROM MyTable
    -> WHERE 0 < ALL (SELECT DISTINCT RecordNum,MAX(RevisionNum) 
                      FROM MyTable 
                      WHERE MAX(RevisionNum) > 0);
                      
ERROR 1111 (HY000): Invalid use of group function

...而我根本就没有使用“group by”函数！

下面的查询生成结果，但只是给出了所有ID，实际上并不按照请求显示唯一的账本记录集。怎么会这样？

按id限制5从MyTable组中选择DISTINCT Book，id，MAX(RevisionNum)；

+------+----+------------------+
| Book | id | MAX(RevisionNum) |
+------+----+------------------+
|    1 |  1 |               30 |
|    1 |  2 |               16 |
|    1 |  3 |               15 |
|    1 |  4 |               10 |
|    1 |  5 |                9 |
+------+----+------------------+

正确的查询是什么,才能给出更像这样的结果：

+------+-----+-----------------------+
| Book |  id | MIN(MAX(RevisionNum)) |
+------+-----+-----------------------+
|    1 |   5 |                     3 |
|    2 |  17 |                     1 |
|    3 |  33 |                     2 |
|    4 | 147 |                     0 |
|    5 | 225 |                     2 |
+------+-----+-----------------------+

Answer 1

你在寻找两个层次的聚合吗？

select id, book, min(max_revisionnum)
from (select id, book, chapter, paragraph, max(revisionnum) as max_revisionnum
      from mytable
      group by id, book, chapter, paragraph
     ) t
group by id, book;

编辑：

根据您的评论，您可以使用：

select *
from (select id, book, chapter, paragraph, max(revisionnum) as max_revisionnum,
             row_number() over (partition by book order by max(revisionnum) desc) as seqnum
      from mytable
      group by id, book, chapter, paragraph
     ) t
where seqnum = 1;

这是一个dB<>小提琴。