Оператор выбора SQL с 3 таблицами объединений
У меня три стола blocksequencesblocksequencepartspartpositions
вот несколько примеров соответственно:
blocksequenceid P1 P2 P3 P4
E006-T001-S054-S036 E006 T001 S054 S036
blocksequenceid partid
E006-T001-S054-S036 E006
E006-T001-S054-S036 T001
E006-T001-S054-S036 S054
E006-T001-S054-S036 S036
partpositions
E006 P1
T001 P2
S054 P3
S054 P4
S036 P3
S036 P4
как вы можете видеть, S054 и S036 могут находиться в положениях P3 или P4 (это можно увидеть в разной последовательности блоков)
Я хочу создать эту таблицу:
blocksequenceid partid positionid
E006-T001-S054-S036 E006 P1
E006-T001-S054-S036 T001 P2
E006-T001-S054-S036 S054 P3
E006-T001-S054-S036 S036 P4
но вместо этого я получаю
blocksequenceid partid positionid
E006-T001-S054-S036 E006 P1
E006-T001-S054-S036 T001 P2
E006-T001-S054-S036 S054 P3
E006-T001-S054-S036 S054 P4
E006-T001-S054-S036 S036 P3
E006-T001-S054-S036 S036 P4
поэтому для этой конкретной последовательности блоков S054 находится в позиции P3, а S036 находится только в позиции P4, но я получаю все возможные позиции для этих частей независимо от последовательности блоков.
Это утверждение, которое я использовал:
create table test as(
select blocksequenceparts.blocksequenceid, blocksequenceparts.partid, partpositions.positionid
from blocksequences right outer join blocksequenceparts on blocksequences.blocksequenceid=blocksequenceparts.blocksequenceid left outer join partpositions on blocksequenceparts.partid=partpositions.partid
order by blocksequenceid);
Не знаю, что делать, чтобы получить желаемый результат. Я готов изменить свои таблицы, если это поможет.
Я думаю, что кто-то редактировал мой код ... в любом случае, идея в том, что некоторые части могут находиться в разных положениях в зависимости от последовательности блоков. Итак, что мне добавить в эти таблицы, чтобы можно было сделать правильный выбор?
Фундаментальный принцип базы данных, который вы упускаете из виду, заключается в том, что записи по своей сути не имеют позиции в таблице. Таким образом, когда S054 и S036 являются жизнеспособными для позиций 3 и 4, ничто не говорит основанной на наборах логике, какой из них должен быть. Теперь, если ваши последовательности блоков - прибытие и вы можете добавить метку времени, которую можно использовать для уточнения позиции.
что, если бы я добавил столбцы P1, P2, P3, P4 в мою таблицу последовательностей блоков, где P1 было бы E006, P2 было бы T001, P3 было бы S054 и P4 было бы S036 .. могу ли я использовать эту таблицу сейчас, чтобы выбрать правильный позиция для этой части?
Ответы 1
Как вы должны решить, какая часть в какой позиции? Данные, которые вы предоставили, представлены в шести строках. Реальное решение состоит в том, чтобы partpositions имел только одну строку для каждой части или для blocksequenceparts, чтобы включать позицию, а также часть.
Этот запрос не решает вашу проблему, но, по крайней мере, упрощает запрос:
select bs.blocksequenceid, bsp.partid, pp.positionid
from blocksequences bs left outer join
blocksequenceparts bsp
on bs.blocksequenceid = bsp.blocksequenceid left outer join
partpositions pp
on bsp.partid = pp.partid
order by bs.blocksequenceid;
Во-первых, обратите внимание, насколько проще писать и читать запрос при использовании псевдонимов таблиц.
Во-вторых, правила для внешних соединений.
- Вы почти всегда хотите
left join. Фактически, я почти никогда не используюright join. И я никогда смешиваюleft joinиright join, если только целью не является проиллюстрировать какое-то странное поведение. Left joinимеет большой смысл. . . сохранить все строки в первой таблице, даже если в таблицах подпоследовательностей нет совпадений.- Пункты (
where,group by,order by) обычно должны использовать столбцы из первой таблицы.
Вы можете получить одну строку для каждой последовательности / позиции, используя агрегацию:
select bs.blocksequenceid, max(bsp.partid), pp.positionid
from blocksequences bs left outer join
blocksequenceparts bsp
on bs.blocksequenceid = bsp.blocksequenceid left outer join
partpositions pp
on bsp.partid = pp.partid
group by bs.blocksequenceid, pp.positionid
order by bs.blocksequenceid;
Это дает вам небольшой контроль над выбранным параметром который.
Второй оператор помог с точки зрения удаления дубликатов, но теперь я получаю: S054 для P3 и P4, когда я хочу S036 для P4, в основном я убежден, что мне нужно добавить какой-то столбец в мои таблицы, некоторая информация отсутствует. как я могу сказать базе данных, что для этой последовательности блоков эта часть находится в этой позиции? я должен добавить столбцы P1 P2 P3 P4 в таблицу blocksequences или лучше просто добавить столбец positionid в таблицу blocksequenceparts? а как тогда выбрать?
Вам нужно добавить позицию в blocksequenceparts, чтобы соединение могло происходить по двум ключам.
В вашем примере только часть S054 имеет несколько позиций частей ... если верно, что только одна часть будет иметь несколько позиций частей, тогда решение представляет собой просто запутанный процесс исключения. Но если несколько частей могут находиться в одной и той же позиции, в данных недостаточно информации для решения. Например, если и S054, и E006 имеют партпозициониды 3 и 4, может существовать более одного решения.