Почему я получаю повторяющиеся кортежи в своем запросе?
Я создаю базу данных библиотеки.
Я хочу написать запрос, который возвращает доступные книги в библиотеке (их номера, заголовки, имена авторов, имя паба, имя категории и количество доступных копий.
Мой запрос включает следующие отношения (выделены полужирным шрифтом первичные ключи):
Книга (ISBN, title,pubYear,numpages, pubName ) pubName FK для издателя
копии (ISBN, номер копии, полка ) ISBN FK для книги
берет взаймы (memberID, ISBN, copyNr, date_of_borrowing, date_of_return ) memberID FK для участника, ISBN FK для книг, (ISBN, номер копии) FK для копий
принадлежит_кому (ISBN, название категории) СБН ФК в Книгу, категорияНазвание ФК в категорию
написанный_by (ISBN, authID) ISBN FK для книги, authID FK для автора
автор (идентификатор авторизации, AFFirst, ALast, Aдата рождения )
Мой подход следующий:
SELECT b.isbn, b.title,a.ALast, a.ALast, b.pubName, be.categoryName , COUNT(b.isbn) as Number_of_copies_available
FROM copies as c
INNER JOIN book as b ON c.isbn = b.isbn
INNER JOIN belong_to as be ON b.isbn = be.isbn
INNER JOIN written_by as w ON w.isbn=b.isbn
INNER JOIN author as a ON a.authID = w.authID
WHERE (c.isbn,c.copyNr) NOT IN (SELECT isbn, copyNr FROM borrows)
GROUP BY b.isbn
ORDER BY be.categoryName
Проблема в том, что я получаю повторяющиеся кортежи в результате перед группировкой, что приводит к большему количеству элементов в группе.
Например, для некоторых книг я получаю значение атрибута Number_of_copies_available вдвое больше, чем ожидалось.
Если я не JOIN с отношениями «написано_автор» и «автор», то результат правильный. Однако я также хочу, чтобы в результате отображались имена авторов. В чем моя ошибка? Заранее спасибо!
Поскольку ваш запрос, скорее всего, неверен, я бы посоветовал прочитать Почему я должен предоставить минимально воспроизводимый пример для очень простого SQL-запроса? для предоставления примеров данных и сопоставления результатов с повторами.
Научитесь правильно использовать GROUP BY. Ваш запрос не будет выполняться ни в каких других СУБД.
Что с этим не так? Я хотел сгруппировать по каждому ISBN, чтобы увидеть количество копий для каждого.
Что делать, если авторов одной книги несколько? В этом случае присоединение к написанному_by может дублировать строки.
@RaymondNijland Я прочитал это ... Все еще немного запутался ... Не могли бы вы объяснить мне, где именно ошибка и как я могу это исправить?
Вам необходимо указать логику агрегирования для столбцов, не сгруппированных по столбцам. Вот что не так с вашей группой, насколько я могу судить.
Не просто присоединяйтесь ко всему, а затем смотрите, как пройти через это. Если вы объедините таблицы в ISBN, вы получите все комбинации авторов и категорий для книги. С тремя авторами и двумя категориями получается уже шесть строк результатов, каждая из которых объединяет одного из авторов с одной из категорий. Вряд ли это имеет смысл. Итак, начните с таблицы результатов. Как это должно выглядеть? Что предъявить в указанном случае? Произвольно выбран один автор и одна категория? Строка всех авторов и еще одна всех категорий?
Что касается GROUP BY: с GROUP BY b.isbn вы получаете одну строку результатов на каждый ISBN. Но затем вы выбираете be.categoryName. Какая из категорий книги? Вы забыли сказать СУБД. Первый в алфавите? Было бы MIN(be.categoryName). Вам нужны функции агрегации, чтобы сообщить СУБД, что выбрать.
Ответы 2
Still a bit confused..Could you explain me where the mistake exactly is and how could I fix this?
На самом деле это не было ответом, так как этот «комментарий» слишком велик.
Но более корректная перезаписи SQL 92 будет примерно такой, как показано ниже.
Но поскольку вы не предоставили примеры данных и ожидаемые результаты, я действительно догадываюсь, что вам нужно.
Запрос
SELECT
book.isbn
...
FROM
book
INNER JOIN (
SELECT
COUNT(book.isbn) AS Number_of_copies_available
FROM
book
INNER JOIN
copies
ON
book.isbn = copies.isbn
... # borrows table should also be needed here to be filterd?
) AS book_copies__count
ON
...
...
...
Я написал следующий запрос, который дает мне желаемый результат SELECT b.isbn, b.title, b.pubName, be.categoryName , COUNT(b.isbn) as Number_of_copies_available FROM copies as c INNER JOIN book as b ON c.isbn = b.isbn INNER JOIN belong_to as be ON b.isbn = be.isbn WHERE (c.isbn,c.copyNr) NOT IN (SELECT isbn, copyNr FROM borrows WHERE borrows.date_of_return IS NULL) GROUP BY b.isbn ORDER BY be.categoryName Однако я хочу добавить столбцы с именем и фамилией автора. Как я должен присоединиться сейчас, чтобы достичь этого?
Этот запрос все еще, скорее всего, все еще неверен @MJ13, он использует GROUP BY, все еще недействителен, см. Мой первый комментарий под вашим вопросом.
Но здесь я присоединяюсь только к трем отношениям, которые могут быть связаны однозначно, если я не ошибаюсь. Я получаю желаемый результат
@ MJ13 MJ13, что не так с другим ответом, который ты принял? Когда вы принимаете ответ, ваш вопрос в основном делается на этом сайте.
Я только что заметил, что не получаю правильных результатов. И не могу понять, что не так. Какова цель оператора date_of_return > current_date в последнем подзапросе?
Да, я... Он просто не ответил. Чего я не понимаю, так это для чего это утверждение? дата возврата всегда равна Null или меньше или равна текущей дате. В любом случае спасибо за помощь!
Не присоединяйте таблицы напрямую, если они не полностью связаны. Категория книги на самом деле не связана с автором книги. Имея двух авторов и две категории, какого автора вы бы объединили с какой категорией? Нет смысла их комбинировать (и, возможно, получить все комбинации). Итак, сначала агрегатируйте, а затем присоединяйтесь к агрегатам. Вот пример:
select
b.isbn,
b.title,
b.pubName,
aut.authors,
cat.categories,
cop.total - coalesce(bor.total, 0) as available
from book b
join
(
select w.isbn, group_concat(a.alast) as authors
from written_by w
join author a ON a.authID = w.authID
group by w.isbn
) aut ON aut.isbn = b.isbn
join
(
select isbn, group_concat(categoryname) as categories
from belong_to
group by isbn
) cat ON cat.isbn = b.isbn
join
(
select isbn, count(*) as total
from copies
group by isbn
) cop ON cop.isbn = b.isbn
left join
(
select isbn, count(*) as total
from borrows
where date_of_return > current_date
group by isbn
) bor ON bor.isbn = b.isbn
order by b.isbn;
Спасибо за ответ! Я получаю сообщение об ошибке: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.categories, cop.total - coalesce(bor.total, 0) as available from book b jo' at line 6
Ну, могут быть ошибки в запросе. Набирал с нуля без базы данных под рукой. Если вы изучите запрос и поймете его, вы, вероятно, быстро найдете ошибку. Вы можете сначала сократить запрос до авторов, затем расширить его до категорий и т. д. и посмотреть, когда появится ошибка. Подсказка: ошибка в предложении select. Найти не сложно :-)
Я не могу это исправить. Это проблема, что я запускаю запрос на MySQL 5.7.26?
Нет, после aut.authors не хватает запятой.
О, да! Я исправил его сейчас, и он работает. Однако он не возвращает желаемых результатов. Чтобы было более понятно, желаемые результаты — это книги, которые задаются следующим запросом: SELECT b.isbn, b.title, b.pubName, be.categoryName , COUNT(b.isbn) as Number_of_copies_available FROM copies as c INNER JOIN book as b ON c.isbn = b.isbn INNER JOIN belong_to as be ON b.isbn = be.isbn WHERE (c.isbn,c.copyNr) NOT IN (SELECT isbn, copyNr FROM borrows) GROUP BY b.isbn ORDER BY be.categoryName Это дает мне именно то, что я хочу. Я просто хочу добавить имена авторов. Как мне это сделать?
Я хочу показать книги, у которых есть одна или несколько копий, и сколько копий есть.
Я думаю, что проблема в утверждении: date_of_return > current_date... У меня есть значение по умолчанию null, если участник еще не вернул книгу... Это влияет на это?
@ MJ13 лучше всего всегда следовать этому примеру и выполнять агрегаты, такие как COUNT(*), в доставленной таблице, также известной как подзапросы, такие как ответ Торстена Кеттнера, таким образом, вы никогда не удивитесь, что соединение отношения «один ко многим» или «многие ко многим» умножает счет результаты, так как это очень распространенная ошибка.
@MJ13: Да, конечно. Вы можете хотеть (date_of_return > current_date or date_of_return is null). И если вы никогда не устанавливаете эту дату заранее, а только когда книга действительно будет возвращена, то достаточно использовать date_of_return is null, чтобы найти еще не возвращенные книги.
Не беспокойтесь о дубликатах, сначала убедитесь, что ваша GROUP BY верна. Поскольку, как правило, вы не должны использовать ГРУППА ПО. Ваш запрос может быть действительным только тогда, когда MySQL может использовать функционально зависимость