teratail
回答率
85.47%
質問するログイン新規登録
トップSQLに関する質問SQL文をおしえてください

編集履歴

回答編集履歴

3

追記

2019/06/04 14:56

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -30,22 +30,28 @@
30
30
 
31
31
  ```SQL
32
32
 
33
- select ID from sample t1
33
+ select * from sample
34
34
 
35
- where exists(
35
+ where ID in (
36
36
 
37
+ select ID from sample t1
38
+
39
+ where exists(
40
+
37
- select 1 from sample where hiduke != 0 and ID=t1.ID
41
+ select 1 from sample where hiduke != 0 and ID=t1.ID
42
+
43
+ )
44
+
45
+ and exists(
46
+
47
+ select 1 from sample where hiduke = 0 and ID=t1.ID
48
+
49
+ )
50
+
51
+ group by ID
52
+
53
+ having count(*)=2
38
54
 
39
55
  )
40
56
 
41
- and exists(
42
-
43
- select 1 from sample where hiduke = 0 and ID=t1.ID
44
-
45
- )
46
-
47
- group by ID
48
-
49
- having count(*)=2
50
-
51
57
  ```

2

追記

2019/06/04 14:56

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -24,13 +24,13 @@
24
24
 
25
25
  --
26
26
 
27
- 0と0以外のhidukeがあるIDの抽出
27
+ 同一IDが2件で、0と0以外のhidukeがあるIDの抽出
28
28
 
29
29
 
30
30
 
31
31
  ```SQL
32
32
 
33
- select distinct ID from sample t1
33
+ select ID from sample t1
34
34
 
35
35
  where exists(
36
36
 
@@ -44,4 +44,8 @@
44
44
 
45
45
  )
46
46
 
47
+ group by ID
48
+
49
+ having count(*)=2
50
+
47
51
  ```

1

追記

2019/06/04 14:54

投稿

sazi
sazi

スコア25195

test CHANGED
@@ -17,3 +17,31 @@
17
17
  ```
18
18
 
19
19
  DBMSによってはこのパターン以外の記述方法が高速な場合もあります。
20
+
21
+
22
+
23
+ 追記
24
+
25
+ --
26
+
27
+ 0と0以外のhidukeがあるIDの抽出
28
+
29
+
30
+
31
+ ```SQL
32
+
33
+ select distinct ID from sample t1
34
+
35
+ where exists(
36
+
37
+ select 1 from sample where hiduke != 0 and ID=t1.ID
38
+
39
+ )
40
+
41
+ and exists(
42
+
43
+ select 1 from sample where hiduke = 0 and ID=t1.ID
44
+
45
+ )
46
+
47
+ ```