Выборка из двух таблиц с UNION ALL и с сортировкой по id первой таблицы, то есть, остается порядок первой таблицы

Рейтинг: 0Ответов: 1Опубликовано: 30.05.2023

Делаю выборку из двух таблиц при помощи UNION ALL. В первой таблице хранятся названия видов работ с ценами по умолчанию. Во второй - те же названия видов работ с ценами, но уже добавленные пользователями.

Таблицы:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `vid` text NOT NULL,
  `price` int(11) NOT NULL,
  `izmer` varchar(255) NOT NULL,
  `kategor` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table2` (
  `id` int(11) NOT NULL,
  `UserID` int(11) NOT NULL,
  `vid` text CHARACTER SET utf8 NOT NULL,
  `price` int(11) NOT NULL,
  `izmer` varchar(256) CHARACTER SET utf8 NOT NULL,
  `kategor` varchar(256) CHARACTER SET utf8 NOT NULL,
  `date` date NOT NULL,
  `DayuDobro` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Вот пример запроса:

$sql = "SELECT vid, AVG(price) price, izmer FROM (SELECT vid, price, izmer FROM table1 where kategor = 'kategor1'
               UNION ALL
               SELECT vid, price, izmer FROM table2 where kategor = 'kategor1') AS subquery GROUP BY vid";

На выходе получаю список работ со средней ценой по каждому виду работ. Проблема в том, что сортировка происходит по полю 'vid' в алфавитном порядке и это 'ломает' список видов работ. Я бы хотел сортировать по id первой таблицы, то есть, чтоб порядок записей оставался таким, как в table1. Виды работ в table2 создаются динамически и периодически повторяются, поэтому и не получается отсортировать по id. Пожалуйста, помогите с этим вопросом. Спасибо.

php mysql mysqli
Источник: Stack Overflow на русском

Ответы

▲ 2Принят

попробуйте так

SELECT vid, AVG(price) price, max(izmer) izmer 
FROM 
   (
     SELECT id, vid, price, izmer FROM table1 where kategor = 'kategor1'
     UNION ALL
     SELECT 0 as id, vid, price, izmer FROM table2 where kategor = 'kategor1'
   ) AS subquery 
GROUP BY vid 
ORDER BY max(id);

Я добавил в запрос max(izmer) izmer иначе будет ошибка группировки.

UPD1. Поясню на примере. Допустим такие исходные данные Table1

id vid price izmer kategor
21 Вид1 100 руб kategor1
11 Вид2 1200 руб kategor1

Table2

id UserID vid price izmer kategor date DayuDobro
121 1 Вид1 120 руб kategor1 2023-05-20 1
122 2 Вид1 140 руб kategor1 2023-05-30 1

Внутренний запрос

 SELECT id, vid, price, izmer FROM table1 where kategor = 'kategor1'
 UNION ALL
 SELECT 0 as id, vid, price, izmer FROM table2 where kategor = 'kategor1'

Даст такой результат

id vid price izmer
21 Вид1 100 руб
11 Вид2 1200 руб
0 Вид1 120 руб
0 Вид1 140 руб

Т.е. данные из table1 будут со значением id>0, данные из table2 будут со значением id=0. Выражением max(id) выбираем id из подзапроса. Он, очевидно будет значением из table1.

SELECT vid, AVG(price) price, max(izmer) izmer, max(id) table1_id
FROM 
   (
     SELECT id, vid, price, izmer FROM table1 where kategor = 'kategor1'
     UNION ALL
     SELECT 0 as id, vid, price, izmer FROM table2 where kategor = 'kategor1'
   ) AS subquery 
GROUP BY vid 
ORDER BY max(id);

Результат запроса

vid price izmer table1_id
Вид2 1200.0000 руб 11
Вид1 120.0000 руб 21

При этом в выходном результате поле max(id) table1_id - не нужно. Его можно убрать из FROM. Но в ORDER BY используем.

Пример здесь