我有以下兩個查詢，第一個查詢回傳一個externalId.
select e.externalId
from data_entity db
inner join assign_entity a on db.fk_assign = a.assignmentId
inner join exercise_entity e on a.fk_exercice = e.externalId;
第二個查詢回傳第一個查詢回傳的internalId每個externalId：
select me.internal_id
from entity me
inner join entity_roles r on me.externalid = r.entity_externalid
where r.roles = 'c9237a21-f6da-4ebe-81b0-ddecd51da86b';
如何將兩個查詢合并到一個查詢中，以像這樣為每個 externalId 回傳其 internalId？
uj5u.com熱心網友回復：
您可以使用 EXISTS：
select me.internal_id, me.externalId
from entity me
inner join entity_roles r on me.externalid = r.entity_externalid
WHERE r.roles = 'c9237a21-f6da-4ebe-81b0-ddecd51da86b'
and exists (
select * from data_entity db
inner join assign_entity a on db.fk_assign = a.assignmentId
inner join exercise_entity e on a.fk_exercice = e.externalId
where e.externalId = me.externalId
);
可能這個查詢比現在更簡單，但僅基于您的圖片和查詢就可以了。
PS：下次請提供你的資料表的好樣本。圖片對任何人都沒有多大幫助。
uj5u.com熱心網友回復：
這應該做我認為需要的。由于沒有小提琴/資料，我無法測驗它。
with external as
(select e.externalId as externalId from data_entity db
inner join assign_entity a on db.fk_assign = a.assignmentId
inner join exercise_entity e on a.fk_exercice = e.externalId)
select distinct e.externalId, me.internal_id from entity me
inner join entity_roles r on me.externalid = r.entity_externalid
inner join external e on r.roles = e.externalId;
uj5u.com熱心網友回復：
沒有一些資料或小提琴很難提供幫助，但我認為這應該可行：
SELECT me.internal_id,
me.externalid
FROM entity me
INNER JOIN entity_roles r
ON me.externalid = r.entity_externalid
WHERE r.roles = 'c9237a21-f6da-4ebe-81b0-ddecd51da86b'
AND EXISTS (SELECT *
FROM data_entity db
INNER JOIN exercise_entity e
ON a.fk_exercice = e.externalid
INNER JOIN assign_entity a
ON db.fk_assign = a.assignmentid
WHERE me.externalid = e.externalid);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/431103.html
標籤：sql PostgreSQL