在嵌套查询中创建和使用临时表

with
   ALL_DID as (
      select did from t3
      where price > 500000
   ),
   PAIRS as (
      select
         id, t3.did
      from t1
         left join ALL_DID t3
         on t1.did = t3.did
   )
select id from PAIRS
   group by id
   having count(did) = (
      select count(0) from ALL_DID
   )
minus
select id from PAIRS
   where did is null

fiddle

如果您只想多次重用同一查询,请考虑使用公共表表达式。或者,您可以考虑创建视图。这些方法可以快速“重用”同一个查询。

然而,我仍然不完全理解你想要的结果。你想知道t1中的数据是否与t3中的数据相同吗?基于did/eid字段?如果是这样的话,似乎可以使用LEFT JOIN来更容易地解决这个问题。类似这样的内容:

SELECT DISTINCT t1.id
FROM t1 
  LEFT JOIN t3 ON t1.did = t3.did AND t3.price > 500000
WHERE t3.id IS NULL

这将告诉你t1中是否有任何不在t3中的记录(相同,价格>500000)。

编辑-要查找所有销售价格超过500000的产品的人员的id,则此操作应该有效:

SELECT t1.id, COUNT(DISTINCT t1.did) productcnt
FROM t1
  JOIN t3 ON t1.did = t3.did
WHERE t3.Price > 500000
GROUP BY t1.id
HAVING COUNT(DISTINCT t1.did) = (
  SELECT COUNT(DISTINCT did) cnt
  FROM t3
  WHERE Price > 500000 )

SQL Fiddle Demo

这不是正常的内部联接查询吗?

SELECT t2.* 
from t1
INNER JOIN t3 as t3 on t1.did = t3.tid
INNER JOIN t1 as t2 on t2.did = t1.id
WHERE t3.price > 50000