SQL基础问题求帮助（多图）

问题：显示撰写最多书籍的作者姓名。如果有多位作者的著作数量最多，请将他们全部显示出来。您的查询应该显示名称。
这是三个表连在一起的 book——written_by——author

我目前只会写SELECT a.FIRSTNAME,a.LASTNAME,count(b.BOOKDESCID) as number_of_book
FROM author as a
join written_by as w
on a.AUTHORID = w.AUTHORID
join book as b
on w.BOOKDESCID = b.BOOKDESCID
Group by a.AUTHORID,a.FIRSTNAME,a.LASTNAME

求各位指点！

在这里我给你点建议，撰写书籍数量如果是一个经常使用的指标，你可以考虑在作者表中添加一个列来存储该数量，每次插入、更新或删除书籍关系时更新该列，就不用楼上的聚合操作了。


WITH Cte AS (
    SELECT a.AUTHORID, a.FIRSTNAME, a.LASTNAME, COUNT(b.BOOKDESCID) AS number_of_books,
           RANK() OVER (ORDER BY COUNT(b.BOOKDESCID) DESC) AS rank
    FROM author AS a
    JOIN written_by AS w ON a.AUTHORID = w.AUTHORID
    JOIN book AS b ON w.BOOKDESCID = b.BOOKDESCID
    GROUP BY a.AUTHORID, a.FIRSTNAME, a.LASTNAME
)
SELECT FIRSTNAME, LASTNAME, number_of_books
FROM Cte
WHERE rank = 1

#second方法
CREATE INDEX idx_author_authorid ON author (AUTHORID);
CREATE INDEX idx_written_by_authorid ON written_by (AUTHORID);
CREATE INDEX idx_written_by_bookdescid ON written_by (BOOKDESCID);
CREATE INDEX idx_book_bookdescid ON book (BOOKDESCID);
ALTER TABLE author ADD COLUMN number_of_books INT DEFAULT 0;

UPDATE author AS a
SET number_of_books = (
    SELECT COUNT(*)
    FROM written_by AS w
    WHERE w.AUTHORID = a.AUTHORID
);

SELECT FIRSTNAME, LASTNAME, number_of_books
FROM author
WHERE number_of_books = (
    SELECT MAX(number_of_books)
    FROM author
);

SELECT au.firstname, au.middlename, au.lastname
FROM author au
JOIN (
    SELECT authorid, COUNT(*) as book_count
    FROM written_by
    GROUP BY authorid
    ORDER BY book_count DESC
) wb ON au.authorid = wb.authorid
WHERE wb.book_count = (
    SELECT MAX(book_count)
    FROM (
        SELECT COUNT(*) as book_count
        FROM written_by
        GROUP BY authorid
    ) subquery
)


WITH author_book_count AS (
    SELECT authorid, COUNT(*) as book_count
    FROM written_by
    GROUP BY authorid
)
SELECT au.firstname, au.middlename, au.lastname
FROM author au
JOIN author_book_count wb ON au.authorid = wb.authorid
WHERE wb.book_count = (
    SELECT MAX(book_count)
    FROM author_book_count
)

这条语句性能不错也符合

如果保留多条最多得数据，用窗口函数 DENSE_RANK() 最为合适

select * from
(SELECT a.FIRSTNAME,a.LASTNAME, 
DENSE_RANK() OVER(PARTITION BY BOOKDESCID ORDER BY BOOKDESCID desc) AS rn1
FROM author as a
join written_by as w
on a.AUTHORID = w.AUTHORID
join book as b
on w.BOOKDESCID = b.BOOKDESCID) aa
where aa.rn1=1

在实际应用中，如果需要频繁查询作者撰写书籍的数量，可以在作者表中添加一个名为"number_of_books"的列来存储该数量，并通过触发器或定时任务等机制定期更新该列的值。这样，在查询作者信息时，就可以直接使用该列的值，而不需要进行聚合计算或子查询。
以下小小示例：

ALTER TABLE author ADD COLUMN number_of_books INT DEFAULT 0;  
  
CREATE TRIGGER update_author_books  
AFTER INSERT ON written_by  
FOR EACH ROW  
UPDATE author SET number_of_books = (SELECT COUNT(*) FROM written_by WHERE author_id = NEW.author_id) WHERE author_id = NEW.author_id;

你写的SQL其实差不多满足结果了，只要加一个having子句，过滤出书籍超过3本的作者数据就行啦，

SELECT 
    a.FIRSTNAME,a.LASTNAME,count(b.BOOKDESCID) as number_of_book
FROM author as a
join written_by as w
on a.AUTHORID = w.AUTHORID
join book as b
on w.BOOKDESCID = b.BOOKDESCID
Group by a.AUTHORID,a.FIRSTNAME,a.LASTNAME
having count(b.BOOKDESCID) > 3

你看下在GROUP BY子句中，按照作者的名字进行分组，而不是作者的ID。尝试修改下看下是否可行

参考newbing
你可以使用以下SQL查询来找到撰写最多书籍的作者姓名：

SELECT author.name
FROM author
JOIN written_by ON author.author_id = written_by.author_id
JOIN book ON written_by.book_id = book.book_id
GROUP BY author.name
HAVING COUNT(*) = (
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM written_by
        GROUP BY author_id
    ) AS t
)

这个查询首先连接了author、written_by和book三个表。然后，使用GROUP BY和COUNT(*)来计算每位作者撰写的书籍数量。接下来，使用子查询找到撰写书籍数量最多的数量。最后，使用HAVING子句来筛选出撰写书籍数量等于最大数量的作者。

分组，然后统计数量，排序取最大值

GROUP BY分组统计

SQL是一种用于管理关系型数据库的编程语言。它具有结构化查询语句的能力，可以用来创建、修改和查询数据库中的表、数据、关系和其他元素。在本文中，我们将回答一些SQL基础问题，并提供详细的解释和示例。

什么是SQL？

SQL，全称为Structured Query Language，结构化查询语言。它是一种专门用于管理关系型数据库的编程语言，可以用于创建、修改和查询数据库的表、数据、关系和其他元素。SQL是一种标准的语言，被广泛用于各种数据库管理系统中。

SQL语言有哪些类别？

SQL语言可以分为以下几类：

(1) 数据定义语言（DDL）：用于定义和管理数据库对象，如表、视图、索引、存储过程等。

(2) 数据操作语言（DML）：用于对数据库中的数据进行操作，如插入、更新、删除等。

(3) 数据查询语言（DQL）：用于查询数据库中的数据，如SELECT语句。

(4) 数据控制语言（DCL）：用于管理数据库的安全性，如GRANT、REVOKE等。

SQL中如何创建一个数据库？

可以使用CREATE DATABASE语句来创建一个数据库。示例代码如下：

CREATE DATABASE mydatabase;

上述代码将创建一个名为“mydatabase”的数据库。

SQL中如何创建一个表？

可以使用CREATE TABLE语句来创建一个表。示例代码如下：

CREATE TABLE customers (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   age INT,
   email VARCHAR(50)
);

上述代码将创建一个名为“customers”的表，包含四个列，分别为id、name、age和email。

SQL中如何插入数据到表中？

可以使用INSERT INTO语句来插入数据到表中。示例代码如下：

INSERT INTO customers (id, name, age, email)
VALUES (1, 'John Doe', 30, 'john.doe@email.com');

上述代码将向“customers”表中插入一条数据，包含id、name、age和email四个列的值。

SQL中如何更新表中的数据？

可以使用UPDATE语句来更新表中的数据。示例代码如下：

UPDATE customers
SET age = 31
WHERE id = 1;

上述代码将更新“customers”表中id为1的数据，将age列的值从30改为31。

SQL中如何删除表中的数据？

可以使用DELETE FROM语句来删除表中的数据。示例代码如下：

DELETE FROM customers
WHERE id = 1;

上述代码将删除“customers”表中id为1的数据。

SQL中如何查询表中的数据？

可以使用SELECT语句来查询表中的数据。示例代码如下：

SELECT * FROM customers;

上述代码将返回“customers”表中的所有数据。

SQL中如何对数据进行排序？

可以使用ORDER BY子句来对数据进行排序。示例代码如下：

SELECT * FROM customers
ORDER BY age ASC;

上述代码将返回“customers”表中的所有数据，并按照age列进行升序排序。

SQL中如何对数据进行分组和聚合？

可以使用GROUP BY和聚合函数来对数据进行分组和聚合。示例代码如下：

SELECT age, COUNT(*) as count
FROM customers
GROUP BY age;

上述代码将返回“customers”表中按照age列分组后的计数结果。

以上是SQL基础问题的回答和详细解释和示例，希望对您有所帮助。