本文是小编为大家收集整理的关于在嵌套查询中创建和使用临时表的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
我有此查询，其中对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 )
其他推荐答案
它不是正常的内在加入查询吗?
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
问题描述
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
其他推荐答案
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 )
其他推荐答案
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
更多相关问答
联系站长