回答編集履歴

2

推敲

2020/06/10 03:55

投稿

sazi
sazi

スコア25327

test CHANGED
@@ -1,3 +1,5 @@
1
+ 1,3を1 以外を9に置き換え、加算した結果が2になるものを抽出
2
+
1
3
  ```SQL
2
4
 
3
5
  SELECT *

1

訂正

2020/06/10 03:55

投稿

sazi
sazi

スコア25327

test CHANGED
@@ -12,40 +12,10 @@
12
12
 
13
13
  from address_type
14
14
 
15
- where type_id in (1,3)
16
-
17
15
  group by address_id
18
16
 
19
- having count(*)=2
17
+ having sum(case when type_id in (1,3) then 1 else 9 end)=2
20
18
 
21
19
  )
22
20
 
23
21
  ```
24
-
25
- name に関する条件による件数が少ないなら以下の方が、高速かもしれません。
26
-
27
- ```SQL
28
-
29
- SELECT *
30
-
31
- FROM address_type t left outer join address a on t.address_id = a.id
32
-
33
- WHERE a.name IN ('a1@d1','a4@d1','a5@d1','a6@d1')
34
-
35
- AND exists (
36
-
37
- select 1 from address_type
38
-
39
- where type_id = 1 and address_id=t.address_id
40
-
41
- )
42
-
43
- AND exists (
44
-
45
- select 1 from address_type
46
-
47
- where type_id = 3 and address_id=t.address_id
48
-
49
- )
50
-
51
- ```