Не так просто SQL-запросы

Итак, у меня есть задачи, и есть некоторые, которые я до сих пор не могу решить даже с помощью 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;

Таблица "Фото" (Фото) и "Пользователи" должна быть довольно понятной. Таблица "Скоринг" предназначена для пользователей, которые оценивают фотографии. Каждый пользователь может оценивать каждую фотографию только один раз. Задача состояла в следующем:

  • Напишите запрос, который будет возвращать идентификатор фотографии, имя_файла и AVG (оценка) всех фотографий, оценка AVG которых превышает 9, и создать индекс, который увеличит производительность этого запроса.

  • Напишите запрос, который будет возвращать "LoginName", "Name", "Surname" и оценку AVG, которую пользователь дал фотографиям, которые он оценил для определенного пользователя (например, пользователь с именем "John" ). Создайте индекс, который увеличит производительность этого запроса.

Я использую 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, и мне хотелось бы тренироваться, чтобы писать запросы больше, но большинство учебных пособий, которые я нахожу в Интернете, имеют довольно простые примеры, которые не помогают в решении этих проблем. Возможно, некоторые из вас могут предложить мне ресурс, который будет иметь более сложную, готовую базу данных и задачи (у которых также есть ответы, если я застрял) для написания запросов?

3 ответа

Предложение WHERE используется для сравнения значений в базовой таблице, тогда как предложение HAVING может использоваться для фильтрации результатов совокупности функции в результирующем наборе запроса.

Предложение HAVING было добавлено в SQL, потому что ключевое слово WHERE не могло использоваться с совокупными функциями.

(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

Надеюсь, что ответ даст вам лучший опыт для следующего экзамена:)


Одна вещь, которая может помочь, - научиться использовать явный синтаксис JOIN (используя предложение ON) вместо старомодного неявного синтаксиса (используя предложение WHERE). Это должно помочь сделать вас более ясным для вас в будущем.

Ваша ошибка заключается в том, что вам нужно использовать предложение HAVING для подмножества результата агрегатных функций. Вы не можете использовать агрегатные функции в предложении WHERE.

Итак, попробуйте это вместо:

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

Удачи!

EDIT: лучшее место для изучения любой базы данных - это страницы документации для конкретной базы данных, которую вы используете. В вашем случае смотрите здесь.


Все в элементе должен находиться в функции GROUP BY или агрегате.

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

Любая колонка, в которую вы собираетесь входить или выполняете поиск, может извлечь выгоду из индекса.

licensed under cc by-sa 3.0 with attribution.