Форум пользователей MySQL

ghostvii · 01.10.2012 17:08:48

Есть небольшая БД:

CREATE TABLE Boat(

ID_Boat INT UNSIGNED NOT NULL AUTO_INCREMENT ,

Name VARCHAR( 25 ) NOT NULL ,

Type VARCHAR( 25 ) NOT NULL ,

PRIMARY KEY ( ID_Boat )

) ENGINE = INNODB;

CREATE TABLE Voyage(

ID_Voyage INT UNSIGNED NOT NULL AUTO_INCREMENT ,

ID_Boat INT UNSIGNED NOT NULL,

Date_Start DATE NOT NULL ,

Date_Finish DATE NOT NULL ,

PRIMARY KEY ( ID_Voyage ),

FOREIGN KEY (ID_Boat) REFERENCES Boat(ID_Boat)

ON UPDATE CASCADE

ON DELETE RESTRICT

) ENGINE = INNODB;

CREATE TABLE Fish(

ID_Fish INT UNSIGNED NOT NULL AUTO_INCREMENT ,

Name VARCHAR( 20 ) NOT NULL ,

PRIMARY KEY ( ID_Fish )

) ENGINE = INNODB;

CREATE TABLE Catch(

ID_Fish INT UNSIGNED NOT NULL ,

ID_Voyage INT UNSIGNED NOT NULL,

Weight INT UNSIGNED NOT NULL ,

INDEX ( Weight ),

PRIMARY KEY ( ID_Fish, ID_Voyage),

FOREIGN KEY (ID_Fish) REFERENCES Fish(ID_Fish)

ON UPDATE CASCADE

ON DELETE RESTRICT,

FOREIGN KEY (ID_Voyage) REFERENCES Voyage(ID_Voyage)

ON UPDATE CASCADE

ON DELETE CASCADE

) ENGINE = INNODB;

Задача: вывести для каждого сорта рыб (ID_Fish) список катеров (ID_Boat) с наибольшим уловом (Weight).

Вариант 1:

SELECT f.Name AS FName, b.Name AS BName, MAX( c.Weight ) AS Weight

FROM Voyage v

LEFT JOIN Boat b

USING ( ID_Boat ) , Catch c

LEFT JOIN Fish f

USING ( ID_Fish ) 

WHERE c.ID_Voyage = v.ID_Voyage

GROUP BY c.ID_Fish

ORDER BY FName, BName;

Вариант 2:

SELECT q2.FName, q2.BName, MAX(q2.Weight)

FROM

(SELECT f.Name AS FName, b.Name AS BName, c.Weight AS Weight

FROM Catch c

LEFT JOIN Fish f

USING ( ID_Fish ) , Voyage v

LEFT JOIN Boat b

USING ( ID_Boat ) 

WHERE c.ID_Voyage = v.ID_Voyage

ORDER BY FName, BName) q2

GROUP BY q2.FName, q2.BName;

Оба запроса работают, но если есть одинаковые значения улова (Weight) одного сорта рыбы(ID_Fish) для разных катеров (ID_Boat), выводят только первое из них. Возможно ли вывести оба значения?

vasya · 01.10.2012 17:29:05

Вы странно применяете left join.
Что должно быть в случае если какой-то сорт рыбы вообще никем не вылавливался?

В первом варианте у вас выбираются поля, по которым не проводится группировка и нет группирующей функции (f.Name AS FName, b.Name AS BName,)

ghostvii · 01.10.2012 17:35:25

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

Вероятно, правильней использовать ссылки типа v.ID_Boat=b.ID_Boat вместо JOIN?

Отредактированно ghostvii (01.10.2012 17:37:52)

vasya · 01.10.2012 17:53:11

Нет, просто вы неправильно применяете left join. Рекомендую посмотреть тему http://sqlinfo.ru/forum/viewtopic.php?id=3210

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

ghostvii · 01.10.2012 18:41:45

Вариант 1:

SELECT c.ID_Fish, v.ID_Boat, MAX( c.Weight )

FROM Voyage v, Catch c

WHERE c.ID_Voyage = v.ID_Voyage

GROUP BY c.ID_Fish

ORDER BY c.ID_Fish, v.ID_Boat;

Работает также, как и с JOIN (и простой подстановкой значений вместо айдишников).
Но проблема не в этом. Я не могу понять, как можно вывести одинаковые значения максимальных величин (Weight) для разных ID_Boat с одинаковыми ID_Fish. Вот наглядный скрин:

http://4.firepic.org/4/images/2012-10/01/cr5m5hzn5wsa.png

Отредактированно ghostvii (01.10.2012 18:57:04)

ghostvii · 01.10.2012 20:09:06

После продолжительных мучений гугла и своих мозгов нашел решение проблемы. Вот что получилось:

SELECT c.ID_Fish, v.ID_Boat, c.Weight

FROM Voyage v, Catch c

WHERE c.ID_Voyage = v.ID_Voyage

AND c.Weight IN 

(SELECT MAX( c2.Weight ) AS Weight

FROM Catch c2

GROUP BY c2.ID_Fish)

ORDER BY c.ID_Fish, v.ID_Boat;

vasya · 01.10.2012 21:20:39

Не нашли.
А если добавить вояж №6, в котором катер №1 поймал 350 единиц рыбы №3?

То получится, что катер №1 для третьей рыбы будет фигурировать дважды.

P.S. Если это не контрольная то учтите, что в текущей реализации MySQL подзапросы в части in работают очень медленно. Нужно или использовать MariaDB, или переписывать их через join.

ghostvii · 01.10.2012 22:08:54

Не контрольная, всего лишь лаба, сгодится и так. Но через JOIN попробую.

vasya написал:
Не нашли.
А если добавить вояж №6, в котором катер №1 поймал 350 единиц рыбы №3?
То получится, что катер №1 для третьей рыбы будет фигурировать дважды.

Тут только кусок БД, необходимый для этого запроса.
Итоговый вариант запроса такой:

SELECT f.Name AS FName, b.Name AS BName, v.Date_Finish AS VDate, c.Weight AS Weight

FROM Voyage v

LEFT JOIN Boat b

USING ( ID_Boat ) , Catch c

LEFT JOIN Fish f

USING ( ID_Fish )

WHERE c.ID_Voyage = v.ID_Voyage

AND v.Date_Finish

BETWEEN  '".$StartDate."'

AND  '".$FinishDate."'

AND c.Weight IN

(SELECT MAX( c2.Weight ) AS Weight

FROM Catch c2

GROUP BY c2.ID_Fish)

ORDER BY FName, BName;

Мне было важно вытащить несколько одинаковых максимальных значений. По условию вес указывается по сортам рыбы. Поэтому в одном рейсе не может быть двух одинаковых сортов. А так как в запросе есть поле VDate (возвращение из рейса), все нормально. Вот результат:

http://4.firepic.org/4/images/2012-10/01/16h2yxgdzs8x.png

Отредактированно ghostvii (01.10.2012 22:11:03)

vasya · 01.10.2012 22:28:32

ghostvii написал:
Мне было важно вытащить несколько одинаковых максимальных значений. По условию вес указывается по сортам рыбы. Поэтому в одном рейсе не может быть двух одинаковых сортов. А так как в запросе есть поле VDate (возвращение из рейса), все нормально.

Во-первых, условие
AND v.Date_Finish
BETWEEN '".$StartDate."'
AND '".$FinishDate."'
не согласуется с первоначальной постановкой задачи " вывести для каждого сорта рыб (ID_Fish) список катеров (ID_Boat) с наибольшим уловом (Weight)."
Во-вторых, оно всего лишь указывает диапазон даты возвращения из рейса. И в этот диапазон могут попасть несколько рейсов одно и того же катера, т.е. приходим к возможности ситуации, описанной мной в прошлом посте.

Даже если считать прошлый вариант запроса верным, то зачем в усложненной версии вы пишите, например, "Voyage v
LEFT JOIN Boat b"?
Разве у вас могут быть рейсы с катерами для которых нет соответствия в таблице Boat?

ghostvii · 01.10.2012 22:45:28

Извиняюсь, что не указал полное условие выборки.

vasya написал:
Во-вторых, оно всего лишь указывает диапазон даты возвращения из рейса. И в этот диапазон могут попасть несколько рейсов одно и того же катера, т.е. приходим к возможности ситуации, описанной мной в прошлом посте.

На скрине как раз такой момент (для одного катера 2 рейса и один сорт рыбы). А возвращение катера из 2 рейсов за один день попробую обойти условиями в php.

vasya написал:
Даже если считать прошлый вариант запроса верным, то зачем в усложненной версии вы пишите, например, "Voyage v LEFT JOIN Boat b"?
Разве у вас могут быть рейсы с катерами для которых нет соответствия в таблице Boat?

Нет, таких не может быть по условию внешнего ключа ID_Boat. Просто, пока экспериментировал с запросами, легче было держать ссылку через JOIN. Кстати, это критично? Что будет работать быстрее, JOIN или v.ID_Boat=b.ID_Boat?

vasya · 01.10.2012 22:59:33

ghostvii написал:
Что будет работать быстрее, JOIN или v.ID_Boat=b.ID_Boat?

Это одно и тоже. А вот join и left join разные вещи.
Почитайте http://sqlinfo.ru/forum/viewtopic.php?id=3210

ghostvii · 01.10.2012 23:21:44

Убрал LEFT у JOIN) Спасибо за помощь!

Форум пользователей MySQL

#1 01.10.2012 17:08:48

Поиск максимального значения

#2 01.10.2012 17:29:05

Re: Поиск максимального значения

#3 01.10.2012 17:35:25

Re: Поиск максимального значения

#4 01.10.2012 17:53:11

Re: Поиск максимального значения

#5 01.10.2012 18:41:45

Re: Поиск максимального значения

#6 01.10.2012 20:09:06

Re: Поиск максимального значения

#7 01.10.2012 21:20:39

Re: Поиск максимального значения

#8 01.10.2012 22:08:54

Re: Поиск максимального значения

vasya написал:

#9 01.10.2012 22:28:32

Re: Поиск максимального значения

ghostvii написал:

#10 01.10.2012 22:45:28

Re: Поиск максимального значения

vasya написал:

vasya написал:

#11 01.10.2012 22:59:33

Re: Поиск максимального значения

ghostvii написал:

#12 01.10.2012 23:21:44

Re: Поиск максимального значения

Board footer