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

Откуда:
Сообщений: 11
Прошу помочь с решением данного вопроса. Есть две таблицы и данные для них

CREATE TABLE `main_table`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` TINYTEXT NOT NULL,
`sometext` TINYTEXT NOT NULL,
PRIMARY KEY(`id`)
) ENGINE = InnoDB;

CREATE TABLE `add_table`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name_id` INT UNSIGNED NOT NULL,
`distance` INT UNSIGNED NOT NULL,
PRIMARY KEY(`id`),
CONSTRAINT `addtable_ibfk_maintable` FOREIGN KEY(`name_id`) REFERENCES `main_table`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

INSERT INTO `main_table` (`name`, `sometext`) VALUES ('name1', 'text1'), ('name2', 'text2'), ('name3', 'text3'), ('name3', 'text4'), ('name3', 'text5');

INSERT INTO `add_table` (`name_id`, `distance`) VALUES (1, 100), (4, 200), (5, 300), (3, 400), (2, 500);

Как с помощью одного запроса на выходе получить:

name_id name distance
1 name1 100
4 name3 200
5 name2 500
15 ноя 20, 18:20    [22232420]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
Ну если Вы ещё постараетесь объяснить логику получения результата...

PS. fiddle
15 ноя 20, 19:24    [22232438]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
artyman
Member

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

Спасибо за фидл)

Логика следующая, в таблице main_table около 10 строк, в add_table тысячи, надо выбрать строки с минимальным distance из add_table чтобы main_table.name не повторялся, а add_table.id нужен для дальнейшей работы. Сгруппировать их только по add_table.name_id не получится, так как main_table.name может повторяться, но при этом main_table.sometext будет разным.
15 ноя 20, 19:54    [22232449]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
Бред какой-то...

artyman
Логика следующая, в таблице main_table около 10 строк, в add_table тысячи
Это, блин, статистика, а ни хрена не логика.

Смотрим на FK. Строим соотв. запрос:
SELECT *
FROM main_table m
JOIN add_table a ON m.id = a.name_id;

соответственно получаем
idnamesometextidname_iddistance
1name1text111100
2name2text252500
3name3text343400
4name3text424200
5name3text535300

Что дальше? Какова логика получения из этих данных показанного выше результата? Особенно - согласно какой логике сформирована третья строка результата? подробненько... а заодно - сразу объясняя, почему соответствующая логика не отражена с в структуре хранения.

Сообщение было отредактировано: 15 ноя 20, 21:12
15 ноя 20, 21:16    [22232478]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Arhat109
Member

Откуда: из СССР
Сообщений: 3399
artyman,

CONCAT(name, cometext) при группировке.
15 ноя 20, 21:19    [22232482]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
artyman
Member

Откуда:
Сообщений: 11
Akina


соответственно получаем
idnamesometextidname_iddistance
1name1text111100
2name2text252500
3name3text343400
4name3text424200
5name3text535300


В вашем результате name3 повторяется три раза, а надо чтобы была строка только с минимальным значением distance, то есть строка 4, значение 200. Но в тоже самое время мне необходимо знать, что это значению 200 соответсвует name_id = 4

Если подскажите как поправить структуру хранения, для получения нужного результата, указанного в первом посте - будет замечательно. Появилась проблема - ищу помощи с вариантом решения, был бы профессионал, вряд ли бы спрашивал.

Если отправить такой запрос, то результат нужный, но как к нему добавить еще add_table.name_id или main_table.id я не знаю.

SELECT (SELECT name FROM main_table WHERE id = name_id) as nname, MIN(distance) 
FROM add_table 
GROUP BY nname;

nname MIN(distance)
name1 100
name3 200
name2 500
15 ноя 20, 23:34    [22232541]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
artyman
В вашем результате name3 повторяется три раза

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

artyman
надо чтобы была строка только с минимальным значением distance

FAQ: Выборка первой/последней записи в группах
15 ноя 20, 23:39    [22232542]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
artyman
Member

Откуда:
Сообщений: 11
Akina,
Спасибо, работает! Буду выбирать более быстрый метод
16 ноя 20, 00:03    [22232546]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
artyman
Member

Откуда:
Сообщений: 11
Akina,
Всё таки поторопился, опять же в моей задаче не получается так как хотелось бы, потому что значение main_table.name не уникально.
И например такой запрос, выдает опять же не тот результат.

select u.id, u.name, p0.distance 
      from main_table u join add_table p0
        on p0.id = ( select p1.id 
                       from add_table p1 
                      where p1.name_id=u.id 
                      order by p1.distance, p1.id 
                      limit 1);


id name distance
1name1100
2name2500
3name3400
4name3200
5name3300


Не должно быть строк 3, 4 и 5, а только одна 4,name3,200 - потому что 200 это минимальное значение в этих трех строках.
16 ноя 20, 00:44    [22232554]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Arhat109
Member

Откуда: из СССР
Сообщений: 3399
artyman,

Чтобы оно стало уникальным, к нему надо добавить то поле, которое делает его уникальным, написал же CONCAT() должен помочь. Ну или делать группировку по паре полей, но тогда и дробление групп будет более мелким.
Как вариант, получив id минимальной записи можно обернув снаружи запрос получить по нему нужной name.

Смотря что надо. Не очень понял задачу..
16 ноя 20, 09:02    [22232601]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
artyman, у Вас, блин, группа - это что? это одинаковые `name`. А ни фига не `name_id`.
16 ноя 20, 09:57    [22232628]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
artyman
Member

Откуда:
Сообщений: 11
Arhat109,
Задача выбрать уникальное name, минимальное значение distance для него, а id этого name. Не получилось с помощью CONCAT сделать так.
16 ноя 20, 11:30    [22232709]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
artyman
Member

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

но как объединить по name, если одинаковые только name_id?
16 ноя 20, 11:39    [22232719]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
artyman
Задача выбрать уникальное name, минимальное значение distance для него
Группа по name и минимум по distance. Они в разных таблицах, JOIN.

artyman
а id этого name.
Если имеется в виду id из таблицы main_table - то одному name соответствует несколько id, и задача нерешаема.
Если имеется в виду name_id из таблицы add_table, то она соответствует выбранному distance.
16 ноя 20, 13:09    [22232828]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Arhat109
Member

Откуда: из СССР
Сообщений: 3399
artyman, если ещё не решилось, то как-то так:

SELECT m2.id AS id, m2.name, a2.distance 
FROM add_table a2
JOIN (
  SELECT m.name, MIN(a.distance) AS d
  FROM main_table m
  JOIN add_table a ON m.id = a.name_id
  GROUP BY m.name
) tmp ON tmp.d = a2.distance
JOIN main_table m2 ON m2.id = a2.name_id
;


Но, кмк, это можно упростить. За упрощением уже к Akina, сейчас не так часто Мускуль в работе..

Суть запроса:
Внутренним подселектом выбираем минимальное distance для имени.
Затем джойним снова на таблицу расстояний и получаем те записи, где расстояния минимальны. По внешнему ключу джойним основную табличку, получая тест имени и идент записи.

Сообщение было отредактировано: 16 ноя 20, 21:47
16 ноя 20, 21:50    [22233358]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
Dino_zavr
Member

Откуда:
Сообщений: 31
ну или как то так
SELECT `name_id`,`name`,`distance` FROM
  (SELECT a.name_id,m.`name`,a.distance, row_number()over(partition by `name` order by `distance` asc) as rn
  FROM main_table m cross join add_table a on m.id = a.name_id) t
  Where rn=1;
17 ноя 20, 14:52    [22233810]     Ответить | Цитировать Сообщить модератору
 Re: Выборка минимальных значений из одной таблицы с группировкой по имени из другой  [new]
artyman
Member

Откуда:
Сообщений: 11
Dino_zavr
ну или как то так
SELECT `name_id`,`name`,`distance` FROM
  (SELECT a.name_id,m.`name`,a.distance, row_number()over(partition by `name` order by `distance` asc) as rn
  FROM main_table m cross join add_table a on m.id = a.name_id) t
  Where rn=1;


А вот это реально сработало как надо, потому что еще отсекло строки с одинаковыми distance и name.

Всем огромное спасибо, а самому еще учиться и учиться)
19 ноя 20, 22:49    [22235783]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить