回答編集履歴
2
推敲
answer
CHANGED
|
@@ -1,3 +1,4 @@
|
|
|
1
|
+
1,3を1 以外を9に置き換え、加算した結果が２になるものを抽出
|
|
1
2
|
```SQL
|
|
2
3
|
SELECT *
|
|
3
4
|
FROM address_type t left outer join address a on t.address_id = a.id
|
1
訂正
answer
CHANGED
|
@@ -5,22 +5,7 @@
|
|
|
5
5
|
AND t.address_id in (
|
|
6
6
|
select address_id
|
|
7
7
|
from address_type
|
|
8
|
-
where type_id in (1,3)
|
|
9
8
|
group by address_id
|
|
10
|
-
having
|
|
9
|
+
having sum(case when type_id in (1,3) then 1 else 9 end)=2
|
|
11
10
|
)
|
|
12
|
-
```
|
|
13
|
-
name に関する条件による件数が少ないなら以下の方が、高速かもしれません。
|
|
14
|
-
```SQL
|
|
15
|
-
SELECT *
|
|
16
|
-
FROM address_type t left outer join address a on t.address_id = a.id
|
|
17
|
-
WHERE a.name IN ('a1@d1','a4@d1','a5@d1','a6@d1')
|
|
18
|
-
AND exists (
|
|
19
|
-
select 1 from address_type
|
|
20
|
-
where type_id = 1 and address_id=t.address_id
|
|
21
|
-
)
|
|
22
|
-
AND exists (
|
|
23
|
-
select 1 from address_type
|
|
24
|
-
where type_id = 3 and address_id=t.address_id
|
|
25
|
-
)
|
|
26
11
|
```
|