mysql - 不是那么简单的 SQL 查询

Question

所以我得到了任务，但有些任务即使在 Google 的帮助下我仍然无法解决。也许有人可以提出一些解决方案(是否必须使用 JOINS 来完成？等等)。

这是数据库:

CREATE TABLE Foto (
    ID int PRIMARY KEY,
    FileName varchar(100),
    FileSize int,
    Created int
) DEFAULT CHARSET=utf8;

CREATE TABLE Users (
    LoginName varchar(10) PRIMARY KEY,
    Surname varchar(20),
    Name varchar(20),
    Created date,
    LastLoginDate date
) DEFAULT CHARSET=utf8;

CREATE TABLE Scoring (
    ID int,
    LoginName varchar(10),
    Score int,
    ScoreDate date,
    FOREIGN KEY (LoginName) REFERENCES Users(LoginName),
    FOREIGN KEY (ID) REFERENCES Foto(ID),
    PRIMARY KEY(ID, LoginName)
) DEFAULT CHARSET=utf8;

“Foto”(照片)和 Users 表应该非常清楚。 “评分”表供对照片进行评分的用户使用。每个用户只能对每张照片评价一次。任务是:

1. 编写查询以返回 AVG 分数高于 9 的所有照片的照片 ID、文件名和 AVG(分数)，并创建索引以提高此查询的性能。

2. 编写查询，返回“LoginName”、“Name”、“Surname”和用户为特定用户(例如，名为“John”的用户)评分的照片的 AVG 分数).创建可提高此查询性能的索引。

我正在使用 MySQL 5.5.16，我对第一个查询的想法是这样的:

SELECT F.ID, F.FileName, AVG(SC.Score)
FROM Foto F, scoring SC
WHERE F.ID = SC.ID
AND AVG(SC.Score) > 9
GROUP BY SC.ID;

它返回“#1111 - 组函数的无效使用”，我从来都不喜欢聚合函数 :D

总的来说，我意识到我在 SQL 方面很烂，我想训练更多地编写查询，但是我在互联网上找到的大多数教程都有非常简单的示例，对解决这些问题没有多大帮助。也许你们中的一些人可以向我建议一个资源，该资源将具有更复杂，现成的数据库和任务(如果我遇到困难，也有答案)来编写查询？

Answer 1

WHERE clause is used for comparing values in the base table, whereas the HAVING clause can be used for filtering the results of aggregate functions in the result set of the query.

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

(1)您必须将 HAVING 添加到您的查询中。

SELECT F.ID, F.FileName, AVG(SC.Score)
FROM   Foto F INNER JOIN scoring S ON F.ID = S.ID
GROUP BY F.ID, F.FileName 
HAVING AVG(S.Score) > 9

(2)

SELECT U.LoginName, U.Name, U.Surname, AVG(S.Score)
FROM Scoring S INNER JOIN Users U ON S.LoginName = U.LoginName
GROUP BY S.LoginName

SQLFiddle Demo

希望这个答案能为您的下一次考试提供更好的背景 :)