VPF::Запрос на максимальное среднее значение

Plathon

Дата 8.10.2010, 00:54 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 17
Регистрация: 8.10.2010

Репутация: нет
Всего: нет

Теряюсь как правильно составить запрос.
Даны 2 таблицы связанные по id:
Students(id, name) и Results(id,subject,mark), содержащих данные о студентах

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

Akina

Дата 8.10.2010, 07:25 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(Plathon @ 8.10.2010, 01:54

)

выведет список студентов с максимальным средним баллом

Либо студента (одного) с макс. средним баллом, либо всех студентов в порядке убывания (ещё раз прочти последние два слова... идею понял?) среднего балла.

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

Deniz

Дата 8.10.2010, 09:48 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 6
Всего: 44

Цитата(Akina @ 8.10.2010, 09:25

)

Либо студента (одного) с макс. средним баллом

ну почему же одного? Есть же вероятность, что у 10 чел будут все пятерки.

Plathon, СУБД?

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

Akina

Дата 8.10.2010, 10:01 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Если СУБД поддерживает подзапросы - задача решается легко... если нет - у тебя проблемы.

--------------------

Plathon

Дата 8.10.2010, 10:56 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 17
Регистрация: 8.10.2010

Репутация: нет
Всего: нет

Цитата(Deniz @ 8.10.2010, 09:48)

Цитата(Akina @ 8.10.2010, 09:25

)

Либо студента (одного) с макс. средним баллом

ну почему же одного? Есть же вероятность, что у 10 чел будут все пятерки.

Plathon, СУБД?

MS SQL Server 2000

Akina

Дата 8.10.2010, 12:55 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Код


SELECT TOP 1 Results.id, Avg(Results.Mark) AS AvgMark
FROM Results
GROUP BY Results.id
ORDER BY Avg(Results.Mark) DESC;

Это работает в Аксессе (фича).
В MS SQL 2000 немного сложнее... (пока не было Инета - даже проверил, работает)

Код


ALTER FUNCTION dbo.GetBestStudents()
RETURNS TABLE
AS
RETURN 
(
SELECT
             dbo.Students.Name
FROM
             dbo.Students
INNER JOIN
             dbo.Results 
ON
             dbo.Students.id = dbo.Results.id 
CROSS JOIN   
(
   SELECT
   TOP 1
                dbo.Students.Name, 
                AVG(dbo.Results.mark) AS avgMark
   FROM
                dbo.Results 
   INNER JOIN
                dbo.Students 
   ON
                dbo.Results.id = dbo.Students.id
   GROUP BY
                dbo.Students.Name
) q
GROUP BY
             dbo.Students.Name,
             q.avgMark
HAVING       AVG(dbo.Results.mark) = q.avgMark
)

--------------------

Plathon

Дата 8.10.2010, 13:06 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 17
Регистрация: 8.10.2010

Репутация: нет
Всего: нет

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

Akina

Дата 8.10.2010, 13:10 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(Plathon @ 8.10.2010, 14:06

)

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

Ну добавь их в список выбираемых полей, блин!

--------------------

Plathon

Дата 8.10.2010, 13:14 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 17
Регистрация: 8.10.2010

Репутация: нет
Всего: нет

так просто? там надо подзапрос писать, для меня это не совсем просто

Akina

Дата 8.10.2010, 14:06 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Да, так просто. И никакой подзапрос писать не надо, уже всё посчитано.

--------------------

Deniz

Дата 8.10.2010, 14:28 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 6
Всего: 44

Цитата(Akina @ 8.10.2010, 14:55

)

Это работает в Аксессе (фича).

в SQL 2000 тоже работает

Код

select t1.id, avg(t1.Mark)
from Results t1
group by t1.id
having avg(t1.Mark) =
(select top 1 avg(t.Mark) from Results t group by t.id order by 1 desc)

примерно так, думаю join с Students и вывод Name'ов не составит труда?

Это сообщение отредактировал(а) Deniz - 8.10.2010, 14:29

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

Akina

Дата 8.10.2010, 14:35 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(Deniz @ 8.10.2010, 15:28

)

в SQL 2000 тоже работает

Нет. В MS Sql сказано - один, значит один. даже если первые несколько записей имеют одинаковое макс. значение ключа сортировки.

Цитата(Deniz @ 8.10.2010, 15:28

)

having avg(t1.Mark) =
(select ...

Ты хочешь сказать, что MS SQL Server 2000 согласится обработать ТАКОЕ?

--------------------

Plathon

Дата 8.10.2010, 14:36 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 17
Регистрация: 8.10.2010

Репутация: нет
Всего: нет

Цитата(Akina @ 8.10.2010, 14:06)

Да, так просто. И никакой подзапрос писать не надо, уже всё посчитано.

ну сначала же подсчитывает avg() в списке средний бал для каждого студента, а в конечном результате нужно получить список с максимальным баллом.
как жеж тут без подзапроса обойтись???

Zloxa

Дата 8.10.2010, 14:41 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

не знаю, прокатит ли.. и есть ли эта опция в 2к

Код


select top (1) WITH TIES t1.id, avg(t1.Mark),max(t1.mark)
from Results t1
group by t1.id
order by 2 desc

Добавлено @ 14:44

Цитата(Zloxa @ 8.10.2010, 14:41

)

есть ли эта опция в 2к

есть!

Это сообщение отредактировал(а) Zloxa - 8.10.2010, 14:44

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Akina

Дата 8.10.2010, 14:51 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(Zloxa @ 8.10.2010, 15:41

)

WITH TIES

Блин!!! всё время забываю про эту штукенцию :(

--------------------

Zloxa

Дата 8.10.2010, 14:54 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

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

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Plathon

Дата 8.10.2010, 17:32 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 17
Регистрация: 8.10.2010

Репутация: нет
Всего: нет

а если таки нужно сканировать 2 раза таблицу, как мне написать подзапрос, средний бал я то могу найти:

Код


SELECT TOP 1 WITH TIES name, AVG(mark) as avg_mark FROM students s JOIN results r ON (s.id = r.id) GROUP BY s.id ORDER BY avg_mark DESC

как среди них отобрать максимальный?

Akina

Дата 8.10.2010, 17:40 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(Plathon @ 8.10.2010, 18:32

)

как среди них отобрать максимальный?

он УЖЕ отобран!!!

--------------------

Plathon

Дата 8.10.2010, 17:46 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 17
Регистрация: 8.10.2010

Репутация: нет
Всего: нет

где???

Akina

Дата 8.10.2010, 17:53 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Скажи, если мы посчитали СРЕДНИЙ БАЛЛ, потом отсортировали ПО НЕМУ, причём по УБЫВАНИЮ, потом взяли ПЕРВЫЙ - как полагаешь, может он оказаться НЕ МАКСИМАЛЬНЫМ?

--------------------

Plathon

Дата 8.10.2010, 17:58 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 17
Регистрация: 8.10.2010

Репутация: нет
Всего: нет

Всё понятно, синтакис тока начинаю учить smile

with ties связывает все имена с макс баллом

Это сообщение отредактировал(а) Plathon - 8.10.2010, 18:10

Deniz

Дата 11.10.2010, 06:07 (ссылка)

(голосов:1)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 6
Всего: 44

Цитата(Akina @ 8.10.2010, 16:35

)

Ты хочешь сказать, что MS SQL Server 2000 согласится обработать ТАКОЕ?

прежде, чем писать в форум, я попробовал его на sql2000 (еще есть у нас такой). Проглотил и вроде результаты правильные выдавал.

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »