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

Question

Делаю выборку из двух таблиц при помощи 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. Пожалуйста, помогите с этим вопросом. Спасибо.

Answer 1

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

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 используем.

Пример здесь

БЛОГ НА HUSL

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

Ответы (1 шт):