Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > SQL. Помощь по запросам для лабораторной работы

Автор: JEEN 10.3.2012, 19:51

Язык SQL, какой конкретно - не сказано, но личном мне ближе MySQL. Есть 3 таблицы: KN – книги, KL – клиенты, VD – выдача книг как-то так Код CREATE TABLE KN ( KKN    integer(5) NOT NULL, N    varchar(100) NOT NULL, A    varchar(100) NOT NULL PRIMARY KEY(KKN)) CREATE TABLE KL ( KK    integer(5) NOT NULL, F    varchar(100) NOT NULL PRIMARY KEY(KK)) CREATE TABLE VD ( K    integer(5) NOT NULL, KKN    integer(5) NOT NULL, KK    integer(5) NOT NULL, DT    DATETIME, SR    integer(3) NULL PRIMARY KEY(K)) Цитата 1. Список клиентов с полем KOL, содержащим количество выдач книг данному клиенту. Если клиенту книги не выдавались, значение этого поля должно быть не определено (использовать внешнее соединение) я сделал так, но не уверен: Код SELECT *, count(vd.k) as kol FROM kl FULL OUTER JOIN vd ON kl.kk = vd.kk Цитата 2. Список книг с указанием, сколько раз она выдавалась (поле KOL) и среднего срока выдачи (поле SR). Если книга не выдавалась, она должна присутствовать в списке, KOL=0, SR=0; здесь, возможно, union надо использовать? Цитата 3. Список клиентов, бравших одну и ту же книгу более 1 раза. В списке отобразить название книги и сколько раз она бралась. 4. Список книг, которые брались более 10 раз на срок не менее 30 дней. 5. Удалить из БД информацию о клиентах, которые ни разу не брали книги буду благодарен за любую помощь.

Автор: JEEN 11.3.2012, 07:11

неужели никто не знает хотя бы какие функции здесь используются?

Автор: Zloxa 11.3.2012, 09:22

Цитата(JEEN @  10.3.2012,  19:51 ) SELECT *, count(vd.k) as kol FROM kl FULL OUTER JOIN vd ON kl.kk = vd.kk 1) Не выдержано условие Цитата(JEEN @  10.3.2012,  19:51 ) Если клиенту книги не выдавались, значение этого поля должно быть не определено Ваш запрос вернет значение 0 вместо неопределенности, если клиенту книги не выдавались 2)  Цитата(JEEN @  10.3.2012,  19:51 ) FULL OUTER JOIN 2.1) Не реализован в MySQL 2.2) Не точно соответствует указанному в постановке задачи "Список клиентов" Цитата(JEEN @  10.3.2012,  19:51 ) десь, возможно, union надо использовать? Пальцем в небо? Второе задание аналогично первому, если вы первую задачу решили самостоятельно(или хотя бы поняли принцип ее решения), совершенно не понятно ваше недоумевание над способом решения второй.   Цитата(JEEN @  10.3.2012,  19:51 ) буду благодарен за любую помощь. 1)http://segfault.kiev.ua/smart-questions-ru.html#homework 2)Для помощи лентяям существует http://forum.vingrad.ru/forum/Vingrad-help-center.html 3)Если вы не лентяй, попробуйте опубликовать свои версии решений. Оценим, подскажем, обсудим, но не решим за вас.

Автор: JEEN 11.3.2012, 09:43

Цитата Пальцем в небо? Второе задание аналогично первому, если вы первую задачу решили самостоятельно(или хотя бы поняли принцип ее решения), совершенно не понятно ваше недоумевание над способом решения второй.   Я сам написал первую задачу, только нашел функцию FULL OUTER JOIN по запросу "внешнее соединение sql", почему то думал, что вернет именно null, а не ноль. А вот со второй не могу разобраться, потому что не знаю как из null сделать 0 и наоборот. вот некоторые задачи, которые уже решил Список книг авторов «Иванов», «Петров» и «Андреев», упорядоченный сначала по убыванию по авторам, затем по возрастанию по названиям; Код SELECT * FROM KN WHERE A = 'Иванов' OR A = 'Петров' OR A = 'Сидоров' ORDER BY A DESC, N ASC Список клиентов, фамилии которых заканчиваются на «ов»; Код SELECT F FROM KL WHERE F like '%ов'  Список кодов книг, которые выдавались (без повторов); Код SELECT DISTINCT KKN FROM VD  Список клиентов, которым выдавались книги с указанием количества выдач; Код SELECT * FROM KL LEFT JOIN VD ON KL.KK = VD.KK WHERE VD.SR IS NOT NULL последний запрос работает, только без указания количества выдач. Пытаюсь сделать так: Код SELECT *, count(VD.K) as count FROM KL LEFT JOIN VD ON KL.KK = VD.KK WHERE VD.SR IS NOT NULL GROUP BY VD.K но всем книгам присваивается по одной выдаче. Почему так? Список книг, которые не выдавались; Код SELECT * FROM KN LEFT JOIN VD ON KN.KKN = VD.KKN WHERE VD.KKN IS NULL

Автор: Zloxa 11.3.2012, 09:57

Цитата(JEEN @  11.3.2012,  09:43 ) почему то думал, что вернет именно null, а не ноль В результате соединения, в случае отсутствия совпадения по критериям соединения, для соединяемых столбцов действительно вернется null. Но только вот количество у вас подсчитывается функцией count, которая не определенного значения не возвращает. Цитата(JEEN @  11.3.2012,  09:43 )  FULL OUTER JOIN по запросу "внешнее соединение sql" Гуглите еще. Внешние соединения бывают трех типов. Впрочем, полное внешнее соединение в этом сулчае, при условии согласованности данных таки решает поставленную задачу. Но его применение все равно для решения задачи в этой постановке - не оправданно. Цитата(JEEN @  11.3.2012,  09:43 ) WHERE VD.SR IS NOT NULL vd.sr может принимать значение null. Фильтруясь по этому критерию вы можете упустить некоторые "выдачи" к тому же фильтроваться вам не надо, вам все равно навдо вывести клиентов, которым ничего не выдавалось. Цитата(JEEN @  11.3.2012,  09:43 ) Почему так?  смотрите по чем группируетесь.

Автор: JEEN 11.3.2012, 10:02

Цитата смотрите по чем группируетесь. да это уже просто так поставил, до этого группировал по VD.KK (код клиента), все равно каждому по одному ставит. Группировал по книгам, это не удовлетворяет услвию задачи, но хоть не единицы стояли. вот кстати таблица "выдача" Цитата vd.sr может принимать значение null. Фильтруясь по этому критерию вы можете упустить некоторые "выдачи" я видимо вчера не так прочитал задачу, думал "с указанием срока выдачи". Спасибо за замечание

Автор: Zloxa 11.3.2012, 10:06

Цитата(JEEN @  11.3.2012,  10:02 ) до этого группировал по VD.KK (код клиента), все равно каждому по одному ставит. Цитата(JEEN @  11.3.2012,  10:02 ) вот кстати таблица "выдача" Ну а теперь смотрите на свои данные, почему должно быть иначе. Только клиенту 3 была выдача более одного раза, но ее вы зачем то отфильтровали по "sr is not null" Ну и опять же, группироватсья по vd.kk - не правильно. Те клиенты, которым не было выдано ни одной книги, попадут в одну группу.

Автор: JEEN 11.3.2012, 10:12

Цитата Ну а теперь смотрите на свои данные, почему должно быть иначе. Только клиенту 3 была выдача более одного раза, но ее вы зачем то отфильтровали по "sr is not null" точно... невнимательность. Сделал так, теперь все как надо выводит Код SELECT *, count(VD.K) as cnt FROM KL LEFT JOIN VD ON KL.KK = VD.KK WHERE VD.KK IS NOT NULL GROUP BY VD.KK Цитата Ну и опять же, группироватсья по vd.kk - не правильно. Те клиенты, которым не было выдано ни одной книги, попадут в одну группу. нам же они вообще не нужны. В условии "клиенты, которым выдавались книги". Или я не так понял опять?

Автор: Zloxa 11.3.2012, 10:21

Цитата(JEEN @  11.3.2012,  10:12 )  В условии "клиенты, которым выдавались книги" Где у нас это условие? Я исхожу из условия в топикстарте: Цитата(JEEN @  10.3.2012,  19:51 ) Если клиенту книги не выдавались, значение этого поля должно быть не определено По этому условию нам таки нужны в результате клиенты, которым книги не выдавались. Если же нам не нужны эти клиенты, делать внешнее соединение, а потом, с помощью условия " WHERE VD.KK IS NOT NULL" сводить результат к внутреннему - тоже нелепость.

Автор: JEEN 11.3.2012, 10:28

Цитата Список клиентов, бравших книги более 5 раз; делаю так: Код SELECT *, count(VD.K) as cnt  FROM KL  LEFT JOIN VD ON KL.KK = VD.KK  WHERE cnt > 1 GROUP BY VD.KK все точно так же как и в предыдущем случае, но возникает ошибка. Разве cnt нельзя использовать в запросе? цифру 1 я использовал вместо 5 временно, потом поправлю. Добавлено через 1 минуту и 29 секунд Цитата Где у нас это условие? Я исхожу из условия в топикстарте: мы немного о разных задачах говорим. я вот про эту: Цитата Список клиентов, которым выдавались книги с указанием количества выдач; SELECT * FROM KL LEFT JOIN VD ON KL.KK = VD.KK WHERE VD.SR IS NOT NULL

Автор: Zloxa 11.3.2012, 10:32

Цитата(JEEN @  11.3.2012,  10:28 )  Разве cnt нельзя использовать в запросе? Да, нельзя. Условия where отрабатывают до выполнения группировки, а потому не могут опираться на значения, полученные в ее результате rtfm having Добавлено @ 10:36 Цитата(JEEN @  11.3.2012,  10:28 ) мы немного о разных задачах говорим. я вот про эту: Цитата Список клиентов, которым выдавались книги с указанием количества выдач; SELECT * FROM KL LEFT JOIN VD ON KL.KK = VD.KK WHERE VD.SR IS NOT NULL Внешнее соединение здесь не уместно.   Благодаря условию в where оно деградирует до внутреннего(я об этом уже упоминал), фильтрация по VD.SR постановкой задачи не обусловлена. Добавлено через 8 минут и 18 секунд Цитата(JEEN @  11.3.2012,  10:28 ) мы немного о разных задачах говорим.  Вобще, по этой самой причине, чтобы все говорили об одном и том же, обычно на один вопрос открыают один топик.

Автор: JEEN 11.3.2012, 10:42

Zloxa, огромное спасибо за помощь. Сейчас дошел до сложных задач, попытаюсь еще раз сам сделать, может выйдет что. Список клиентов, бравших книги более 5 раз; Код SELECT * FROM KL  LEFT JOIN VD ON KL.KK = VD.KK  WHERE VD.KK IS NOT NULL  GROUP BY VD.KK HAVING COUNT(VD.K) > 5

Автор: Zloxa 11.3.2012, 10:48

Замечания все те же 1) Цитата(JEEN @  11.3.2012,  10:42 ) LEFT JOIN VD ON KL.KK = VD.KK  WHERE VD.KK IS NOT NULL эквивалентно внутреннему соединению, внешнее соединение - не уместно 2)  Цитата(JEEN @  11.3.2012,  10:42 ) GROUP BY VD.KK При использовании внешнего соединения, в одну группу попадут все клиенты, для которых не было выдач.  Вообще запрос работать будет. Но написан он стилистически криво.

Автор: JEEN 11.3.2012, 10:59

сейчас напишу что узнал, потом попробую разобраться с замечаниями. в MySQL, как я понял, нет полного внешнего объединения их заменяет левое и правое внешнее объединение (left join, right join), собственно их я и использовал, поэтому запрос для задачи: Цитата Список клиентов с полем KOL, содержащим количество выдач книг данному клиенту. Если клиенту книги не выдавались, значение этого поля должно быть не определено (использовать внешнее соединение) будет выглядеть примерно так. Код SELECT *, count(VD.K) as KOL FROM KL LEFT JOIN VD ON KL.KK = VD.KK GROUP BY VD.KK если бы не "значение этого поля должно быть не определено" выходит, что надо использовать не count, а что-то другое?

Автор: Zloxa 11.3.2012, 11:09

Цитата(JEEN @  11.3.2012,  10:59 ) в MySQL, как я понял, нет полного внешнего объединения Для решения конкретно этой задачи полного внешнего не требуется, а левое оно тоже вполне себе внешнее.    Цитата(JEEN @  11.3.2012,  10:59 ) если бы не "значение этого поля должно быть не определено" выходит, что надо использовать не count, а что-то другое?  Да, нелепое какое-то условие. Такое ощущение, что оно добавлено преподом от какой то другой задачи. Чтобы выполнит это условие, я бы использовал выражение case, но выглядело бы это как-то кастыльно. Цитата(JEEN @  11.3.2012,  10:59 ) GROUP BY VD.KK    Цитата(Zloxa @  11.3.2012,  10:48 )  в одну группу попадут все клиенты, для которых не было выдач.

Автор: JEEN 11.3.2012, 11:21

Цитата Внешнее соединение здесь не уместно.   Благодаря условию в where оно деградирует до внутреннего(я об этом уже упоминал), фильтрация по VD.SR постановкой задачи не обусловлена. я не понимал разницу между всеми join'ами, поэтому везде исползовал left join. Сейчас прочитал еще раз внимательно, дошло. Запрос еще проще выглядит. фильтрация по VD.SR случайно попала, я просто скопировал со старого решения. на счет группировки аналогично, мне казалось я все испробовал, только VD.KK работало. Сейчас сгруппировал по KL.KK, получилось. В общем, задача: Цитата Список клиентов, которым выдавались книги с указанием количества выдач старое решение Код SELECT *, count(VD.K) as cnt  FROM KL  LEFT JOIN VD ON KL.KK = VD.KK  WHERE VD.KK IS NOT NULL  GROUP BY VD.KK  новое решение Код SELECT *, count(VD.K) as cnt  FROM KL  INNER JOIN VD ON KL.KK = VD.KK  GROUP BY KL.KK

Автор: JEEN 11.3.2012, 11:43

Цитата Список книг с указанием, сколько раз она выдавалась (поле KOL) и среднего срока выдачи (поле SR). Если книга не выдавалась, она должна присутствовать в списке, KOL=0, SR=0; Код SELECT *, count(VD.K) as KOL, avg(VD.SR) as SR FROM KN LEFT JOIN VD ON KN.KKN = VD.KKN GROUP BY KN.KKN только функция avg не выдает 0, если книгу не брали..

Автор: Zloxa 11.3.2012, 11:53

Цитата(JEEN @  11.3.2012,  11:21 ) я не понимал разницу между всеми join'ами, поэтому везде исползовал left join. Я именно так и понял, потому и был так настойчив  Цитата(JEEN @  11.3.2012,  11:21 ) дошло Чисто чтоб закрепить - я как-то http://forum.vingrad.ru/index.php?showtopic=343660&view=findpost&p=2438437. Мне кажется достаточно доходчиво должно бы быть.  Цитата(JEEN @  11.3.2012,  11:21 ) новое решение    Осталось одно только замечание. Это будет работать только на MySQL. Стандарт SQL запрещает в списке полей select использовать поля, не перечисленные в group by без применения к ним аггрегатных функций. MySQL единственный из известных мне движков не реализует этого запрета. Разработчики отписываются, мол это для оптимизации, и ограничивают область использования этой фичи в документации.  Соответственно, чтобы оторваться от диалекта MySQL вам осталось лишь выдержать это ограничение заменив  Код SELECT *, count(VD.K) as cnt  на Код select KL.KK,max(kl.F) as F,  count(VD.K) as cnt  Добавлено через 4 минуты и 39 секунд Цитата(JEEN @  11.3.2012,  11:43 ) только функция avg не выдает 0, если книгу не брали..  Код coalesce(avg(VD.SR),0)  as avg_SR Тут, кстати, тонкий момент. VD.SR может принимать неопределенное значение, и если оно используется, функция avg его не будет использовать при расчете. avg([1,null]) = 1, в то время, как avg([1,0]) = 0.5. Но в постановке задачи этот ньюанс не огооврен, думаю, можно забить(но держать в уме, я бы такой вопрос поднял бы на защите)

Автор: JEEN 11.3.2012, 12:48

Цитата Чисто чтоб закрепить - я как-то рисовал картинку. Мне кажется достаточно доходчиво должно бы быть.  хорошие картинки, как-то давно натыкался на подобное, только там круги были. Тогда не сохранил и забыл где видел, а вашу картинку сохранил на комп) Цитата max(kl.F) as F я так понял здесь без разницы что max, что min использовать? лишь бы была какая-то функция? Цитата Но в постановке задачи этот ньюанс не огооврен, думаю, можно забить(но держать в уме, я бы такой вопрос поднял бы на защите) получилось) только у преподавателя даже нет возможности спросить/проконсультироваться. На дистанционке учусь. вот еще пару запросов сделал, может есть какие-то замечания.. Цитата Добавить в БД: книга «Колобок», с кодом 234, автор не задан Код INSERT INTO KN  SET KKN = ‘234’, N = ‘Колобок’ Цитата Удалить из БД информацию о клиентах, которые ни разу не брали книги Код DELETE KL FROM KL  LEFT JOIN VD ON KL.KK=VD.KK  WHERE VD.KK IS NULL остались 2 задачи, которые никак не получаются. Цитата 1 задача: Список книг, которые брались более 10 раз на срок не менее 30 дней. сделал вот так: Код SELECT * FROM KN INNER JOIN VD ON KN.KKN = VD.KKN WHERE VD.SR > 30 GROUP BY KN.KKN HAVING COUNT(VD.K) > 10 но здесь мешает HAVING COUNT, потому что он считает, что должно быть больше 10 записей, которые брались больше чем на 30 дней. Т.е. сначала выполняется WHERE, отсеивает часть, потом по оставшимся уже проходится HAVING COUNT. А надо как-то, чтобы они вместе работали, например WHERE VD.SR > 30 and count(VD.K) > 10, но тогда GROUP BY мешает. Цитата Задача 2. Список клиентов, бравших одну и ту же книгу более 1 раза. В списке отобразить название книги и сколько раз она бралась эм.. тут я даже не представляю как должен вывод выглядеть, речь о 2х разных списках идет. Что-то такого что ли: - Иванов, Петров, Сидоров - Горе от ума (3) - Иванов, Козлов - Евгений Онегин (2) и т.д. разве такой список можно сделать только одним запросом? в общем, я бы, наверное, прошелся по списку книг, у каждой проверял кто ее брал, если ее брали > 1 человека, то выводить ее..

Автор: Zloxa 11.3.2012, 13:33

Цитата(JEEN @  11.3.2012,  12:48 ) я так понял здесь без разницы что max, что min использовать? лишь бы была какая-то функция?   т.к. у нас группировка по первичному ключу. Цитата(JEEN @  11.3.2012,  12:48 ) DELETE KL FROM KL  LEFT JOIN VD ON KL.KK=VD.KK  WHERE VD.KK IS NULL тут не знаю. Я пишу на оракле. Оракл не умеет делать жойн для делита. not in или not exists с моей точки зрения выглядило бы как более универсальное решение. C not in - ньюанс: применимо только к not nullable набору данных. Цитата(JEEN @  11.3.2012,  12:48 )  более 10 раз на срок не менее 30 дней. Здесь возможна неоднозначность трактовки задачи 1) Книга бралась более 10 раз и всякий раз на срок не менее 30 дней, тогда ваш подход - правильный 2) Книга бралась более 10 раз на общий срок не менее 30 дней, тогда суммируем сроки и отсекаем в havind Мне кажется первый вариант самый близкий к исходной задаче, и вы соврешенно правильно его реализовали. Хотя второй вариант тоже похож на правду. Цитата(JEEN @  11.3.2012,  12:48 ) эм.. тут я даже не представляю как должен вывод выглядеть В принципе - то же самое. Группироваться по паре (id_клиент,id_книга)

Автор: JEEN 11.3.2012, 14:07

Цитата В принципе - то же самое. Группироваться по паре (id_клиент,id_книга) у меня серьезные проблемы с пониманием условий %) перечитал 200 раз и до меня дошло, что "Список клиентов, бравших одну и ту же книгу" эту фразу я не так понимаю, думал что нужно найти общие интересы клиентов)) в общем сделал вот так Код SELECT *, count(VD.K) as KOL, max(KL.F) as F, max(KN.N) as N FROM KL INNER JOIN VD ON KL.KK = VD.KK  INNER JOIN KN ON VD.KKN = KN.KKN  GROUP BY KL.KK, VD.KKN HAVING COUNT(VD.K) > 1 все работает как надо. Zloxa, спасибо вам огромное. Без вас бы не разобрался. Поставил бы плюсик к каждому посту, но пока 100 сообщений не набрал, нельзя плюсики ставить.

Автор: Zloxa 11.3.2012, 14:20

Цитата(JEEN @  11.3.2012,  14:07 ) спасибо вам огромное Ну и тебе спасибо, что ты не лентяй, не ждал готовых решений, готов был разбираться и думать.    Побольше бы таких начинающих.

Автор: Zloxa 11.3.2012, 14:56

Цитата(JEEN @  11.3.2012,  14:07 ) найти общие интересы клиентов Так то это тоже просто.  Селфджойн: Код select * from vd vd1 inner join vd vd2 on vd1.KKN= vd2.KKN and vd1.kk != vd1.kk получаем список общих интересов

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)