#sql #interbase
Есть три таблицы
main (id, f1, f2, f3, ...)
detail (id, main_id)
sub_detail (id, param, val)
Связь main -> detail один ко многим, detail -> sub_detail один к одному (по полям id)
На выходе нужно получить таблицу с полями
main_id, f1, f2, f3, value1, value2, value3
где valueN это значение поля val из таблицы sub_detail для записи с param = N. Не
все valueN могут присутствовать в базе.
Базовый запрос выглядит так
SELECT
m.id AS main_id,
m.f1,
m.f2,
m.f3,
s1.val AS value1,
s2.val AS value2,
s3.val AS value3
FROM
main m
LEFT JOIN detail d ON (
d.main_id = m.id
)
LEFT JOIN sub_detail s1 ON (
s1.id = d.id AND
s1.param = 1
)
LEFT JOIN sub_detail s2 ON (
s2.id = d.id AND
s2.param = 2
)
LEFT JOIN sub_detail s3 ON (
s3.id = d.id AND
s3.param = 3
)
WHERE
m.id = 1;
Но он вместо одной записи возвращает столько записей, сколько есть дочерних записей
в detail.
Поставить группировку по main.id и вытаскивать MAX(s.val) я не могу, т.к. в запросе
есть еще поля f1, f2
Есть вариант на основании detail и sub_detail построить view
CREATE VIEW vw_detail (
main_id,
param,
val
) AS
SELECT
d.main_id,
s.param,
s.val
FROM
detail d
JOIN sub_detail s ON (
d.id = s.id
)
и вызывать ее в основном запросе
SELECT
m.id AS main_id,
m.f1,
m.f2,
m.f3,
s1.val AS value1,
s2.val AS value2,
s3.val AS value3
FROM
main m
LEFT JOIN vw_detail s1 ON (
s1.main_id = m.id AND
s1.param = 1
)
LEFT JOIN vw_detail s2 ON (
s2.main_id = m.id AND
s2.param = 2
)
LEFT JOIN vw_detail s3 ON (
s3.main_id = m.id AND
s3.param = 3
)
WHERE
m.id = 1;
но в Interbase есть старый баг из-за которого при вызове LEFT JOIN view начинают
игнорироваться индексы и происходит полное сканирование таблицы. А писать JOIN vw_detail
нельзя, потому что некоторые (или даже все) параметры могут отсутствовать.
Пробовал писать так
CREATE VIEW vw_values (
main_id,
value1,
value2,
value3
) AS
SELECT
m.id,
MAX(s1.val),
MAX(s2.val),
MAX(s3.val),
FROM
main m
LEFT JOIN detail d ON (
d.main_id = m.id
)
LEFT JOIN sub_detail s1 ON (
s1.id = d.id AND
s1.param = 1
)
LEFT JOIN sub_detail s2 ON (
s2.id = d.id AND
s2.param = 2
)
LEFT JOIN sub_detail s3 ON (
s3.id = d.id AND
s3.param = 3
)
GROUP BY
m.id;
и потом в основном запросе
SELECT
m.id AS main_id,
m.f1,
m.f2,
m.f3,
v.value1,
v.value2,
v.value3
FROM
main m
JOIN vw_values v ON (
v.main_id = m.id
)
WHERE
m.id = 1;
но тогда вначале идет построение VIEW с группировкой, а потом накладывание условия
v.main_id = m.id. План получается дикий.
Вариант заменить vw_values селективной процедурой тоже не подходит. Т.к. основной
запрос выполняется в другой view, а синтаксис Interbase не позволяет внутри view использовать
процедуры
P.S. Вариант перейти на Firebird не предлагать
Ссылка на DB Fiddle.
Ответы
Ответ 1
SELECT m.id AS main_id, m.f1, m.f2, m.f3, MAX(s1.val) AS value1, MAX(s2.val) AS value2, MAX(s3.val) AS value3 FROM main m LEFT JOIN detail d ON ( d.main_id = m.id ) LEFT JOIN sub_detail s1 ON ( s1.id = d.id AND s1.param = 1 ) LEFT JOIN sub_detail s2 ON ( s2.id = d.id AND s2.param = 2 ) LEFT JOIN sub_detail s3 ON ( s3.id = d.id AND s3.param = 3 ) WHERE m.id = 1 GROUP BY m.id, m.f1, m.f2, m.f3 fiddleОтвет 2
Денормализовал таблицу detail и скопировал туда поле param. После чего запрос переписался в такой вид SELECT m.id AS main_id, m.f1, m.f2, m.f3, s1.val AS value1, s2.val AS value2, s3.val AS value3 FROM main m LEFT JOIN detail d1 ON ( d1.main_id = m.id AND d1.param = 1 ) LEFT JOIN sub_detail s1 ON ( s1.id = d1.id ) LEFT JOIN detail d2 ON ( d2.main_id = m.id AND d2.param = 2 ) LEFT JOIN sub_detail s2 ON ( s2.id = d2.id ) LEFT JOIN detail d3 ON ( d3.main_id = m.id AND d3.param = 3 ) LEFT JOIN sub_detail s3 ON ( s3.id = d3.id ) WHERE m.id = 1; Если кто подскажет решение без денормализации - буду рад Update Причем поля detail.main_id и detail.param должны входить в общий индекс. Если завести два отдельных, то хоть и план строится с использованием индексов, но выполнение очень долгое
