在嵌套查询中创建和使用临时表[英] Create and use temp table in nested queries

本文是小编为大家收集整理的关于在嵌套查询中创建和使用临时表的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

本文来自:IT宝库(https://www.itbaoku.cn)

我有此查询,其中对MINUS运算符的两个操作数进行了两次计算. 如何重写查询,以便仅创建一次?

(目的是检查两组是否相等)

SELECT DISTINCT t1.id
FROM t1
WHERE NOT EXISTS (SELECT t2.did FROM t1 AS t2
              WHERE t2.id = t1.id
              MINUS
              SELECT t3.did FROM t3
              WHERE t3.price > 500000)
AND NOT EXISTS (SELECT t3.did FROM t3
            WHERE t3.price > 500000
            MINUS
            SELECT t2.did FROM t1 AS t2
            WHERE t2.id = t1.id);

要澄清问题,以下两个[sub]查询在查询中重复两次:

SELECT t2.did FROM t1 AS t2
WHERE t2.id = t1.id

SELECT t3.did FROM t3
WHERE t3.price > 500000

推荐答案

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

小提琴

其他推荐答案

如果您只想多次重复使用相同的查询,请考虑使用常见表表达式.或者,您可以考虑创建视图.这些方式可以快速"重复使用"相同的查询.

但是,我仍然不完全理解您的期望结果.您是否想知道T1中的数据是否与DID/EID字段相同?如果是这种情况,似乎您可以使用左联接来更轻松地弄清楚这一点.这样的东西:

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小提琴演示

其他推荐答案

它不是正常的内在加入查询吗?

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

本文地址:https://www.itbaoku.cn/post/1714980.html

问题描述

I have this query where the two operands of the MINUS operators are computed twice. How do rewrite the query so that they are only created once?

(The objective is to check that the two sets are equal)

SELECT DISTINCT t1.id
FROM t1
WHERE NOT EXISTS (SELECT t2.did FROM t1 AS t2
              WHERE t2.id = t1.id
              MINUS
              SELECT t3.did FROM t3
              WHERE t3.price > 500000)
AND NOT EXISTS (SELECT t3.did FROM t3
            WHERE t3.price > 500000
            MINUS
            SELECT t2.did FROM t1 AS t2
            WHERE t2.id = t1.id);

To clarify things, the following two [sub]queries are repeated twice in the query:

SELECT t2.did FROM t1 AS t2
WHERE t2.id = t1.id

and

SELECT t3.did FROM t3
WHERE t3.price > 500000

推荐答案

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

其他推荐答案

Look into using Common Table Expressions if you just want to be able to reuse the same query multiple times. Or you can look into creating views. Those ways you can "reuse" the same query quickly.

However, I still don't completely understand what your desired results would be. Are you wanting to know if the data in t1 is the same as the data in t3, based on the did/eid fields? If that is the case, it seems you could use a LEFT JOIN to figure that out much more easily. Something like this:

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

This will tell you if there are any records in t1 that aren't in t3 (with the same did and price > 500000).

EDIT -- To find the id of all persons who sell all products that have prices greater than 500000, then this should work:

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

其他推荐答案

Isnt it normal inner join query?

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