SQL — сопоставитель таблиц базы данных — вставка в таблицу на основе выбора и соединений
Это жизненный пример вопроса, чтобы вы могли лучше понять, что нам нужно.
ПРИМЕР
У нас есть 3 стола
cars
*id
*description
car_spec
*id
*car_id
*spec_id
*amount
specs
*id
*name
Для каждого элемента автомобиля мы хотим сохранить эти данные:
*id
*description
И 3 значения «spec», расположенные в таблице «specs» на основе таблицы «car_spec»:
doors
pistons
hp
Мы хотим объединить все необходимые данные в одну таблицу, подобную этой.
car_db
*id
*description
*original_car_id
*doors
*pistons
*hp
Образец данных
стол с машинами
id | description
1 | 2020 car 1
2 | 2020 car 2
3 | 2020 car 3
таблица car_spec
id | car_id | spec_id | amount
1 | 1 | 1 | 2
2 | 1 | 2 | 12
3 | 1 | 3 | 550
4 | 2 | 1 | 4
5 | 2 | 2 | 4
6 | 2 | 3 | 250
таблица спецификаций
id | name
1 | doors
2 | pistons
3 | hp
примерная таблица результатов
id | description | original_car_id | doors | pistons | hp
1 | 2020 car 1 | 1 | 2 | 12 | 550
2 | 2020 car 2 | 2 | 4 | 4 | 250
3 | 2020 car 3 | 3 | 4 | 8 | 400
То, что нам нужно
Нам нужно экспортировать новую таблицу с требуемыми данными.
Можем ли мы сделать это в sql? Если нет, какие-либо предложения о том, как мы можем это сделать?
@GordonLinoff Я добавил образцы данных. Взгляни
Ответы 2
Обычно вы используете условную агрегацию для поворота спецификаций. Следующий синтаксис должен работать практически во всех базах данных:
select c.id,
max(case when s.name = 'doors' then cs.amount end) as doors,
max(case when s.name = 'pistons' then cs.amount end) as pistons,
max(case when s.name = 'hp' then cs.amount end) as hp
from cars c
inner join car_spec cs on cs.car_id = c.id
inner join specs s on s.id = cs.spec_id
group by c.id
Спасибо за Ваш ответ! Я изменил раздел «Что нам нужно». Посмотрите и обновите свой ответ, если это необходимо! Еще раз спасибо!
@jon: запрос выглядит нормально. Вы можете добавить c.description к предложению select, если хотите.
Хорошо, еще раз спасибо! Пожалуйста, объясните, зачем нам для этого нужна функция max? Насколько я понимаю, нам нужен только «кейс», поэтому мы можем получить то, что нам нужно, используя «как».
@jon: нам нужна одна строка для каждой машины, поэтому нам нужна агрегация. Затем MAX() просто выбирает соответствующее значение в каждой группе. Мы могли бы использовать MIN(), результат был бы таким же.
Как насчет производительности при использовании этого на 7,5-метровых рядах?
если таблица "specs" исправлена, вы можете использовать такие подзапросы:
select c.id, c.description, c.id as original_car_id,
(select d.amount from car_spec d where d.car_id = c.id and d.spec_id = 1) as doors,
(select d.amount from car_spec d where d.car_id = c.id and d.spec_id = 2) as pistons,
(select d.amount from car_spec d where d.car_id = c.id and d.spec_id = 3) as hp
from cars c;
Я думаю, что образцы данных и желаемые результаты помогут, как и соответствующий тег базы данных.