SQL - 查询 - max(count())

Question

我正在为我的数据库系统考试(明天)学习，但在要求我编写查询的练习中遇到了麻烦。下面是一个例子:



我被要求写一个查询来回答以下问题:在年龄最小的作者中，谁写的书最多？

问题是我的老师禁止我在 FROM 子句中使用子查询，并禁止我使用 TOP .

我已经写了一个答案，但我知道一个是不正确的:

SELECT W.AName, COUNT(W.ID_B) AS NBooks
FROM Write W, Author A1
WHERE (A1.AName = W.AName) AND
      (A1.AAge = (SELECT MIN(A2.Age)
                  FROM Author A2))
GROUP BY W.AName
ORDER BY NBooks DESC

这个给了所有年龄较小的作者，以及他们各自写的书数(我希望......)。正确答案应该只是这个的第一行。

让我说清楚:

Table Author
AName    | AAge
---------------
John     | 25
Rick     | 30
Sean     | 26
Lena     | 25

Table Writes
AName    | ID_B
---------------
John     | 2
Lena     | 1
John     | 3
Lena     | 4
Rick     | 5
Rick     | 6
Lena     | 6
Rick     | 7
Rick     | 8

(请注意，Sean 没有写过任何书，第 6 本书有 2 个作者，而 Rick 是大多数书的作者 (4))

现在，我上面写的代码给出了这个结果(我猜):

AName    | NBooks
-----------------
Lena     | 3
John     | 2

(最低年龄是 25 岁，Lena 和 John 都是 25 岁)

问的是:

AName    | NBooks
-----------------
Lena     | 3

(Lena 是作者，在所有年龄最小(25)的作者中，写的书最多)

提前致谢

Answer 1

因为你是学生，我来回答一部分问题。这是一个答案，忽略最年轻的部分:

select a.AName, COUNT(*) as NumBooks
from Author a join
     Write w
     on a.AName = w.AName
group by a.AName
having count(*) >= all(select COUNT(*) as NumBooks
                       from write w
                       group by w.AName
                      )

我想你可以弄清楚如何修改它。

顺便说一下，对 limit 的限制和 top我希望，仅针对此示例。否则，你应该找另一位老师，因为这些都是非常重要的结构。

此外，您需要学习传统的连接语法，而不是 ,在 from条款。再一次，如果你的老师没有教现代语法(大约从 1988 年开始)，请找一位新老师。而且，我假设对子查询的限制也适用于 CTE。

我还想指出查询的“正确”版本:

select top 1 a.aname, count(*) as NumBooks
from Author a join
     Write w
     on a.AName = w.AName
group by author.name, author.Aage
order by author.Age asc, count(*) desc

这个查询几乎在任何维度上都比上面的查询好。它做了一个 join , 一 group by和一种。我的查询的完整版本做了两个 join s 明确，两个 join s 隐含(年龄条款)，和两个 group by s。前者将比后者具有更好的性能。

从可读性的角度来看，这个版本更短更简洁。我还认为教它正在做什么要容易得多，而不是第一个版本中的“不寻常”结构。大部分同学都会明白什么 top和 order by正在做并且可以效仿这一点。模仿发生的事情 having条款需要一些心理体操。

如果你想得到所有拥有最大计数的作者，首先要意识到前面的查询等价于:

select aname, NumBooks
from (select a.aname, count(*) as NumBooks,
             row_number() over (partition by author.Name order by a.aAge, count(*) desc) as seqnum
      from Author a join
           Write w
           on a.AName = w.AName
      group by author.name, author.Aage
     ) aw
where seqnum = 1

切换它以获取所有作者很容易:

select aname, NumBooks
from (select a.aname, count(*) as NumBooks,
             dense_rank() over (partition by author.Name order by a.aAge, count(*) desc) as seqnum
      from Author a join
           Write w
           on a.AName = w.AName
      group by author.name, author.Aage
     ) aw
where seqnum = 1

这也比回答问题的查询更有效。无法使用 top或 from 中的子查询条款就像跑三腿赛跑。是的，您可能可以到达那里，但是您可以用自己的两条腿跑得更快。