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

### 问题描述

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

```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);
```

```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
```

## 其他推荐答案

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

```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
```

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
```