Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Другие варианты sql запроса  [new]
Sander11
Member

Откуда:
Сообщений: 1
Доброго времени суток, коллеги !
Сегодня ходил на собеседование на позицию Администратора БД и в числе различных запросов по администрированию меня попросили написать запрос для решения задачки:

"В таблицах Students (Id, FIO) хранятся данные о студентах, в таблице Marks (St_Id, Mark) хранятся данные об оценках студентов. Вывести ФИО и количество пятерок для тех студентов, у которых есть не менее 5 двоек в журнале."

Я достаточно давно не писал хитрых SQL запросов, да и делать это на листочке - достаточно не привычно. В итоге, как я сейчас понимаю, написал я этот запрос не правильно. Однако придя домой я решил составить этот запрос еще раз.. Написал запрос в двух вариантах, но чувствую, что все варианты какие-то кривоватые и это можно сделать оптимальней. Прошу сообщество выложить свои более оптимальные версии этого запроса.

Мои варианты:
---- Example 1
select Fio, count(*) as amount from Students s
	inner join Marks m on s.id = m.st_id
where Mark = 5
and s.id in (select St_id from (Select st_id, mark, count(*) as amount from Marks
								where mark = 2
								group by st_id, Mark
								having count(*) >= 5
					)x
		)
group by Fio

----- Example 2
select Fio, count(*) as amount from Students s
	inner join Marks m on s.id = m.st_id
where Mark = 5
        and s.id in (Select distinct st_id from Marks m
                        where mark = 2
	                         and (select count(*) from Marks m2
			                where mark = 2 and m2.st_id = m.st_id) >= 5
                       )
group by Fio
13 авг 15, 19:04    [18014936]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты sql запроса  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Крутые перцы пишут без подзапросов
13 авг 15, 19:19    [18014995]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты sql запроса  [new]
stdvb
Member

Откуда:
Сообщений: 38
Sander11,
Рано тут оптимизацией заниматься. Для начала напишите запрос, который будет возвращать то, что нужно в задаче.
Например, оба варианта не возвращают студентов с количеством пятерок = 0.
Порешайте задачи на sql-ex.ru - поможет.
13 авг 15, 20:17    [18015141]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты sql запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
select
 s.Fio, count(case when m.mark = 5 then 1 end)
from
 Students s
 inner join Marks m on s.id = m.st_id
group by
 s.Fio
having
 count(case when m.mark = 2 then 1 end) >= 5;
13 авг 15, 20:53    [18015222]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты sql запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
invm,

Новерное, везде sum вместо count (и else 0 в case). Это смотрелось бы логичнее, и не было бы варнинга про null values в агрегатах.

Сообщение было отредактировано: 13 авг 15, 21:04
13 авг 15, 21:03    [18015254]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты sql запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Гавриленко Сергей Алексеевич
Новерное, везде sum вместо count (и else 0 в case). Это смотрелось бы логичнее, и не было бы варнинга про null values в агрегатах.
Соглашусь.
Тем более, что обработка nulls в агрегатах ведет к дополнительным накладным расходам.
13 авг 15, 21:23    [18015328]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты sql запроса  [new]
stdvb
Member

Откуда:
Сообщений: 38
и добавить s.id в группировку что б разделить студентов с совпадающими ФИО
14 авг 15, 10:36    [18016674]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты sql запроса  [new]
Jaffar
Member

Откуда:
Сообщений: 633
select 
max(s.Name),
IsnULL(sum(case when mark = 5 then 1 end), 0)
from marks m
join Stud s on s.ID = m.ST_ID
where
		mark in (2, 5)
group by ST_ID
having sum(case when mark = 2 then 1 end) > 4



только непонятно зачем это админу?
14 авг 15, 10:44    [18016718]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты sql запроса  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1741
invm
select
 s.Fio, count(case when m.mark = 5 then 1 end)
from
 Students s
 inner join Marks m on s.id = m.st_id
group by
 s.Fio
having
 count(case when m.mark = 2 then 1 end) >= 5;


мне больше нравится вариант с sum:
select
s.id, s.Fio, sum(case when m.mark = 5 then 1 else 0 end)
from
 Students s
 inner join Marks m on s.id = m.st_id
group by
s.id,  s.Fio
having
 sum(case when m.mark = 2 then 1 else 0 end) >= 5;
14 авг 15, 10:51    [18016754]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить