我有兩個 SQL 查詢；第一個是：
with b as (select person_id from people where name='Ward Bond' and born=1903)
select title_id from b natural join crew;
這產生了正確的結果并且沒問題。另一種是：
with c as (select person_id from people where name='John Wayne' and born=1907)
select title_id from c natural join crew;
這也完全可以并產生正確的結果。一旦我嘗試使用以下查詢找到這兩個查詢的交集：
with b as (select person_id from people where name='Ward Bond' and born=1903) select title_id from b natural join crew
intersect
with c as (select person_id from people where name='John Wayne' and born=1907) select title_id from c natural join crew;
我Error: near "with": syntax error
在使用 SQLite3 時遇到錯誤。你能幫我找出問題嗎？我想要得到的東西很簡單；我想要這兩個臨時表的交集。
uj5u.com熱心網友回復：
這是 SQLite 的正確語法：
select * from (
with b as (
select person_id
from people
where name='Ward Bond' and born=1903
)
select title_id from b natural join crew
)
intersect
select * from (
with c as (
select person_id
from people
where name='John Wayne' and born=1907
)
select title_id from c natural join crew
);
另一種獲取相交行的方法：
with cte(name, born) as (values ('Ward Bond', 1903), ('John Wayne', 1907))
select c.title_id
from crew c natural join people p
where (p.name, p.born) in cte
group by c.title_id
having count(distinct p.person_id) = 2;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/365863.html