teratail
回答率
85.37%
teratail 10th-anniversary
teratail 10th-anniversary
質問するログイン新規登録
トップMySQLに関する質問１対多の関係にあるテーブル間で複数の検索条件に一致して重複してしまうレコードの抽出

編集履歴

回答編集履歴

9

修正

2018/11/13 02:54

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -82,11 +82,13 @@
82
82
 
83
83
  ```SQL
84
84
 
85
+ SELECT d.*
86
+
85
- SELECT d.*, CASE WEHN vc.user_id='002' then vc.user_id end AS user_id
87
+ , case when exists(select 1 from view_continue where user_id='002' and id=d.id)
88
+
89
+ then '002' end as user_id
86
90
 
87
91
  FROM document as d
88
-
89
- left JOIN view_continue AS vc ON d.id = vc.document_id
90
92
 
91
93
  WHERE d.update_date='2018-11-12'
92
94
 

8

推敲

2018/11/13 02:54

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -86,7 +86,7 @@
86
86
 
87
87
  FROM document as d
88
88
 
89
- INNER JOIN view_continue AS vc ON d.id = vc.document_id
89
+ left JOIN view_continue AS vc ON d.id = vc.document_id
90
90
 
91
91
  WHERE d.update_date='2018-11-12'
92
92
 

7

追記

2018/11/13 02:08

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -78,18 +78,26 @@
78
78
 
79
79
  --
80
80
 
81
- 低速かもしれないけど、記述がシンプル版
81
+ UNION クエリー版
82
82
 
83
83
  ```SQL
84
84
 
85
- SELECT distinct d.id, d.title, d.report, d.update_date
85
+ SELECT d.*, CASE WEHN vc.user_id='002' then vc.user_id end AS user_id
86
86
 
87
- , case when vc.user_id='002' then vc.user_id end as user_id
87
+ FROM document as d
88
88
 
89
- FROM document as d left join view_continue as vc on d.id=vc.document_id
89
+ INNER JOIN view_continue AS vc ON d.id = vc.document_id
90
90
 
91
- where d.update_date= '2018-11-12'
91
+ WHERE d.update_date='2018-11-12'
92
92
 
93
+ UNION ALL
94
+
95
+ SELECT d.*, vc.user_id
96
+
97
+ FROM document as d
98
+
99
+ INNER JOIN view_continue AS vc ON d.id = vc.document_id
100
+
93
- or vc.user_id='002'
101
+ WHERE d.update_date<>'2018-11-12' and vc.user_id = '002'
94
102
 
95
103
  ```

6

追記

2018/11/13 02:06

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -73,3 +73,23 @@
73
73
  or user_exists
74
74
 
75
75
  ```
76
+
77
+ 追記２
78
+
79
+ --
80
+
81
+ 低速かもしれないけど、記述がシンプル版
82
+
83
+ ```SQL
84
+
85
+ SELECT distinct d.id, d.title, d.report, d.update_date
86
+
87
+ , case when vc.user_id='002' then vc.user_id end as user_id
88
+
89
+ FROM document as d left join view_continue as vc on d.id=vc.document_id
90
+
91
+ where d.update_date= '2018-11-12'
92
+
93
+ or vc.user_id='002'
94
+
95
+ ```

5

推敲

2018/11/13 00:53

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -52,7 +52,7 @@
52
52
 
53
53
  ```
54
54
 
55
- または、
55
+ または、（見た目はすっきりするけど、前のよりは遅いと思われる)
56
56
 
57
57
  ```SQL
58
58
 

4

追記

2018/11/12 11:57

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -51,3 +51,25 @@
51
51
  or exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)
52
52
 
53
53
  ```
54
+
55
+ または、
56
+
57
+ ```SQL
58
+
59
+ SELECT *,case when user_exists then [ユーザーid] end as user_id
60
+
61
+ FROM (
62
+
63
+ SELECT id, title, report, update_date
64
+
65
+ ,exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id) as user_exists
66
+
67
+ from document
68
+
69
+ ) as d
70
+
71
+ where update_date=[日付]
72
+
73
+ or user_exists
74
+
75
+ ```

3

修正

2018/11/12 11:55

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -36,22 +36,18 @@
36
36
 
37
37
  ログインしたユーザーでなければ、user_idを取得しないという解釈で。
38
38
 
39
- それぞれを条件で絞り込んだものにしてお気に入りは外部結合にする。
40
-
41
39
  ```SQL
42
40
 
43
- SELECT d.id, d.title, d.report, d.update_date, vc.user_id
41
+ SELECT id, title, report, update_date
44
42
 
45
- FROM (
43
+ , case when exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)
46
44
 
47
- select * from document
45
+ then [ユーザーid] end as user_id
48
46
 
49
- where d.update_date= '2018-11-12'
47
+ FROM document d
50
48
 
51
- ) as d left join (
49
+ where update_date=[日付]
52
50
 
53
- select * from view_continue where user_id='002'
51
+ or exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)
54
-
55
- ) as vc on d.id=vc.document_id
56
52
 
57
53
  ```

2

追記

2018/11/12 11:48

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -29,3 +29,29 @@
29
29
  and exists(select 1 from view_continue where user_id=[ユーザーid] and id=d.id)
30
30
 
31
31
  ```
32
+
33
+ 追記
34
+
35
+ --
36
+
37
+ ログインしたユーザーでなければ、user_idを取得しないという解釈で。
38
+
39
+ それぞれを条件で絞り込んだものにしてお気に入りは外部結合にする。
40
+
41
+ ```SQL
42
+
43
+ SELECT d.id, d.title, d.report, d.update_date, vc.user_id
44
+
45
+ FROM (
46
+
47
+ select * from document
48
+
49
+ where d.update_date= '2018-11-12'
50
+
51
+ ) as d left join (
52
+
53
+ select * from view_continue where user_id='002'
54
+
55
+ ) as vc on d.id=vc.document_id
56
+
57
+ ```

1

推敲

2018/11/12 11:44

投稿

sazi
sazi

スコア25300

test CHANGED
@@ -6,9 +6,9 @@
6
6
 
7
7
  FROM document as d inner join view_continue as vc on d.id=vc.document_id
8
8
 
9
- where update_date= '2018-11-12'
9
+ where d.update_date= '2018-11-12'
10
10
 
11
- and user_id='002'
11
+ and vc.user_id='002'
12
12
 
13
13
  ```
14
14