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

Откуда:
Сообщений: 1725
В голову приходит что-то подобное:

Select group.id, avg(student.mark) join student ....
group by  group.id
having avg(student.mark) = 5.0


Подскажите есть ли вариант попроще?
4 фев 19, 17:33    [21801470]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
aleks222
Member

Откуда:
Сообщений: 733
where not exists( select * from student as x where student.groupid = x.groupid and x.mark < 5.0 )
4 фев 19, 17:39    [21801477]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
felix_ff
Member

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

+

есть. ходить на лекции
4 фев 19, 17:40    [21801479]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

Откуда:
Сообщений: 1725
aleks222
where not exists( select * from student as x where student.groupid = x.groupid and x.mark < 5.0 )

О, кореллированный подзапрос)
4 фев 19, 18:02    [21801496]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

Откуда:
Сообщений: 1725
felix_ff
questioner,

+

есть. ходить на лекции


Ну если каждый день не писать подобные запросы, то поверь - забываются эти приёмчики.

P.S. Последний раз был на лекции в 2013 если что. И это были не базы данных
4 фев 19, 18:04    [21801498]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
aleks222
Member

Откуда:
Сообщений: 733
questioner
aleks222
where not exists( select * from student as x where student.groupid = x.groupid and x.mark < 5.0 )

О, кореллированный подзапрос)

Ваще то нет.
Не коррелированный.
4 фев 19, 18:09    [21801501]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

Откуда:
Сообщений: 1725
aleks222
questioner
пропущено...

О, кореллированный подзапрос)

Ваще то нет.
Не коррелированный.


А какой?

один студен student внешнего , другой x из внутреннего
4 фев 19, 18:18    [21801504]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6698
questioner,

коррелированный что-то должен вернуть. А это - фильтр.
4 фев 19, 18:50    [21801523]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

Откуда:
Сообщений: 1725
Владислав Колосов
questioner,

коррелированный что-то должен вернуть. А это - фильтр.


ну я как бы хз, ребят, но

https://www.sql.ru/articles/mssql/2005/032101usingcorrelatedsubquery.shtml
Что такое - Коррелированный Подзапрос?

Коррелированный подзапрос - это оператор SELECT, вложенный в другой оператор T-SQL, и ссылающийся на один или несколько столбцов внешнего запроса


Выглядит будто корреллированный подзапрос может быть фильтром
4 фев 19, 18:57    [21801527]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
invm
Member

Откуда: Москва
Сообщений: 8744
questioner,

Коррелированный подзапрос зависит от внешнего. Чего он там возвращает и где расположен - не важно.
4 фев 19, 19:07    [21801535]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

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

Коррелированный подзапрос зависит от внешнего. Чего он там возвращает и где расположен - не важно.


Вот и я про это же. Путают меня местные умники)
4 фев 19, 19:39    [21801543]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Агнец за бортом
Member

Откуда:
Сообщений: 1312
questioner
В голову приходит что-то подобное:

Select group.id, avg(student.mark) join student ....
group by  group.id
having avg(student.mark) = 5.0



Подскажите есть ли вариант попроще?


Куда уж проще.
4 фев 19, 21:16    [21801576]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

Откуда:
Сообщений: 1725
Агнец за бортом
questioner
В голову приходит что-то подобное:

Select group.id, avg(student.mark) join student ....
group by  group.id
having avg(student.mark) = 5.0



Подскажите есть ли вариант попроще?


Куда уж проще.


Это решение плохо масштабируется если например шкала десятибальная, а я хочу найти препода у которого у всех студенты получили 8 балллов ровно.
4 фев 19, 21:55    [21801595]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

Откуда:
Сообщений: 1725
questioner
Агнец за бортом
пропущено...


Куда уж проще.


Это решение плохо масштабируется если например шкала десятибальная, а я хочу найти препода у которого у всех студенты получили 8 балллов ровно.


Это решение плохо масштабируется если например шкала десятибальная, а я хочу найти группы в которых у всех студентов 8 балллов ровно.
4 фев 19, 22:04    [21801599]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
felix_ff
Member

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

как вариант тогда еще так:

select gr from @t group by gr having (sum(m%5)=0 and count(1) > 1) or (sum(m)=5 and count(1) = 1)


вариант aleks222 думаю будет быстрее
5 фев 19, 02:47    [21801641]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
aleks222
Member

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

Коррелированный подзапрос зависит от внешнего. Чего он там возвращает и где расположен - не важно.


Вот и я про это же. Путают меня местные умники)

Это тя главный умник путает.
По факту и сути exists реализуется как (inner/outer)join.
5 фев 19, 08:26    [21801692]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Агнец за бортом
Member

Откуда:
Сообщений: 1312
Накатал было телегу, но потом увидел, что ты и есть автор.

Так ты реши, тебе попроще? Или надо найти группы, где все студенты получили максимальный балл, но выше среднего, исключая отсутствующих и совсем безнадёжных.
5 фев 19, 09:31    [21801709]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
questioner
Select group.id, avg(student.mark) join student ....
group by  group.id
having avg(student.mark) = 5.0

Подскажите есть ли вариант попроще?

Не AVG(), а MIN().

Ну и avg(student.mark) join student - это как-то неэскуэльно.
5 фев 19, 09:59    [21801725]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
questioner
хочу найти группы в которых у всех студентов 8 балллов ровно.

0 = SUM(CASE mark WHEN 8 THEN 0 ELSE 1 END)
-- или там 
0 = COUNT(CASE WHEN mark != 8 THEN 1 END)
5 фев 19, 10:01    [21801728]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
aleks222
Member

Откуда:
Сообщений: 733
Akina
questioner
хочу найти группы в которых у всех студентов 8 балллов ровно.

0 = SUM(CASE mark WHEN 8 THEN 0 ELSE 1 END)
-- или там 
0 = COUNT(CASE WHEN mark != 8 THEN 1 END)

Ну что ты зациклился на группировке?
exists/not exists быстрее.
5 фев 19, 10:10    [21801739]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
aleks222
exists/not exists быстрее.
Это да. Но в данной предметной области, где в таблицах от силы несколько тысяч записей, а сама база вся целиком легко помещается в память, это неактуально. К тому же из таблицы будет выбираться distinct group.id, но с проверкой по всем записям группы, что не особо и отличается от явной группировки.
А такие конструкции, как я привёл, более понятны на начальных этапах освоения sql.
5 фев 19, 10:37    [21801763]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 283
По мне так для новичка будет понятнее
Having Min(student.mark) = 5 and Max(student.mark) = 5
5 фев 19, 11:05    [21801784]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
invm
Member

Откуда: Москва
Сообщений: 8744
Akina
aleks222
exists/not exists быстрее.
Это да.
Легко соглашаетесь.
Наш "дарагуля" очень не любит изучать планы выполнения. Иначе он был бы в курсе, что его любимый exists/not exists может компилироваться в агрегирование с (о ужас!) группировками.
5 фев 19, 11:11    [21801791]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
invm
Легко соглашаетесь.
Да мне ж не жалко...
5 фев 19, 11:31    [21801804]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

Откуда:
Сообщений: 1725
Akina
questioner
хочу найти группы в которых у всех студентов 8 балллов ровно.

0 = SUM(CASE mark WHEN 8 THEN 0 ELSE 1 END)
-- или там 
0 = COUNT(CASE WHEN mark != 8 THEN 1 END)


Всегда интересовал вопрос, а нолик в начале, чтобы читать неудобнее было?
5 фев 19, 15:36    [21802002]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
982183
Member

Откуда: VL
Сообщений: 3104
questioner
а я хочу найти препода у которого у всех студенты получили 8 балллов ровно.


Ну так поменять немного

Select group.id, avg(student.mark) join student ....
group by  group.id
having min(student.mark) = 5 and max(student.mark) = 8
5 фев 19, 15:42    [21802010]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
982183
Member

Откуда: VL
Сообщений: 3104
Select group.id, avg(student.mark) join student ....
group by  group.id
having min(student.mark) = 8 and max(student.mark) = 8
5 фев 19, 15:43    [21802011]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
982183
Member

Откуда: VL
Сообщений: 3104
туплю

Select group.id, min(student.mark), max(student.mark) join student ....
group by  group.id
having min(student.mark) = 8 and max(student.mark) = 8
5 фев 19, 15:44    [21802012]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
questioner
а нолик в начале, чтобы читать неудобнее было?
А какая разница-то? проверка на равенство - штука симметричная. Что же до неудобства - при полном форматировании выравнивают много чего, в т.ч. и операторы сравнения.
5 фев 19, 16:23    [21802042]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
982183, а зачем групповые функции в выходной набор-то пихать? тем более что с учётом условия отбора значения этих полей очевидны...
5 фев 19, 16:24    [21802044]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
questioner
Member

Откуда:
Сообщений: 1725
Akina
questioner
а нолик в начале, чтобы читать неудобнее было?
А какая разница-то? проверка на равенство - штука симметричная. Что же до неудобства - при полном форматировании выравнивают много чего, в т.ч. и операторы сравнения.


Читать неудобно
5 фев 19, 17:28    [21802106]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6698
questioner,

по моей гипотезе так левши пишут, равно как и зеркальные смайлики.
5 фев 19, 23:25    [21802251]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
982183
Member

Откуда: VL
Сообщений: 3104
Akina
982183, а зачем групповые функции в выходной набор-то пихать? тем более что с учётом условия отбора значения этих полей очевидны...

Наверное зря.
Поддался на первоначальный текст.
Практики нет лет 20.
навыки уходят.
Но так точно работать будет.
6 фев 19, 00:38    [21802269]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
982183
Member

Откуда: VL
Сообщений: 3104
Простите. всё уже было.

Kopelly
По мне так для новичка будет понятнее
Having Min(student.mark) = 5 and Max(student.mark) = 5
6 фев 19, 00:40    [21802270]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
andrey odegov
Member

Откуда:
Сообщений: 460
questioner
Akina
пропущено...

0 = SUM(CASE mark WHEN 8 THEN 0 ELSE 1 END)
-- или там 
0 = COUNT(CASE WHEN mark != 8 THEN 1 END)


Всегда интересовал вопрос, а нолик в начале, чтобы читать неудобнее было?

Тяжелое наследие C/C++ :)
В операторе сравнения '==' забывается второй знак '=' и при такой записи компилятор выдает ошибку.
6 фев 19, 10:26    [21802426]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Разумная кровать
Member

Откуда: Неризиносква
Сообщений: 2591
а вариант с ALL не подойдёт ?
6 фев 19, 15:39    [21802810]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1032
Разумная кровать,

подойдет. кстати вариант алекса не очень универсален если расширять шкалу оценок

declare @t table (
id int identity(1,1), 
gr int,
mark int,
index ix (gr, mark)
)

insert into @t values ( 1, 5), ( 1, 3), (2, 5), (3, 4), (3, 2), (4, 5), (4,5), (6, 8), (7,8), (7,10)
set statistics io, time on
print 'ALL:'
select gr from @t t0 where 8 = all(select mark from @t t where t.gr = t0.gr) group by gr
print 'MIN+MAX:'
select gr from @t t0 group by gr having min(mark) = 8 and max(mark) = 8
print 'SUM+COUNT:'
select gr from @t group by gr having (sum(mark%8)=0 and count(1) > 1) or (sum(mark)=8 and count(1) = 1)
print 'SUM+CASE'
select gr from @t t0 group by gr having 0 = SUM(CASE mark WHEN 8 THEN 0 ELSE 1 END)
print 'ALEX:'
select gr from @t t0 where not exists( select * from @t t where t.gr = t0.gr and t.mark < 8 )
set statistics io, time off


+

ALL:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#ADE7C60A'. Scan count 11, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
MIN+MAX:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#ADE7C60A'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SUM+COUNT:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#ADE7C60A'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SUM+CASE

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#ADE7C60A'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
ALEX:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#ADE7C60A'. Scan count 11, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

6 фев 19, 16:57    [21802899]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать группы в которых все студенты получили отлично  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 283
having sum(mark%@ИскомаяОценка)=0

Условие некорректное: при @ИскомаяОценка = 2 истина для всех четных оценок. При @ИскомаяОценка = 1 - для всех целых.
7 фев 19, 05:02    [21803177]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить