回答編集履歴

1

修正したSQL

2020/02/03 11:38

投稿

takashikawai
takashikawai

スコア172

test CHANGED
@@ -1 +1,105 @@
1
1
  削除対象ではないチケットの添付ファイルと比較して、その中に重複している物理ファイルがないかどうかを調べないといけないですね。前回作成したのは、削除対象のチケットの中だけで重複しているかという処理になってしまっていたので、修正してみました。
2
+
3
+ ===> 修正したSQLです(MySQL)。
4
+
5
+
6
+
7
+ ```SQL
8
+
9
+ SELECT
10
+
11
+ db_redmine.issues.id AS チケットID
12
+
13
+ , db_redmine.attachments.disk_filename AS 物理ファイル名
14
+
15
+ , db_redmine.attachments.id AS 添付ファイルID
16
+
17
+ FROM
18
+
19
+ db_redmine.issues
20
+
21
+ INNER JOIN db_redmine.issue_statuses
22
+
23
+ ON db_redmine.issue_statuses.id = db_redmine.issues.status_id
24
+
25
+ LEFT JOIN db_redmine.attachments
26
+
27
+ ON db_redmine.issues.id = db_redmine.attachments.container_id
28
+
29
+ LEFT OUTER JOIN (
30
+
31
+ SELECT
32
+
33
+ db_redmine.issues.id
34
+
35
+ , db_redmine.attachments.disk_filename
36
+
37
+ FROM
38
+
39
+ db_redmine.issues
40
+
41
+ INNER JOIN db_redmine.issue_statuses
42
+
43
+ ON db_redmine.issue_statuses.id = db_redmine.issues.status_id
44
+
45
+ LEFT JOIN db_redmine.attachments
46
+
47
+ ON db_redmine.issues.id = db_redmine.attachments.container_id
48
+
49
+ WHERE
50
+
51
+ db_redmine.issue_statuses.is_closed = 0
52
+
53
+ AND db_redmine.issues.project_id = 3
54
+
55
+ UNION
56
+
57
+ SELECT
58
+
59
+ db_redmine.issues.id
60
+
61
+ , db_redmine.attachments.disk_filename
62
+
63
+ FROM
64
+
65
+ db_redmine.issues
66
+
67
+ INNER JOIN db_redmine.issue_statuses
68
+
69
+ ON db_redmine.issue_statuses.id = db_redmine.issues.status_id
70
+
71
+ LEFT JOIN db_redmine.attachments
72
+
73
+ ON db_redmine.issues.id = db_redmine.attachments.container_id
74
+
75
+ WHERE
76
+
77
+ db_redmine.issue_statuses.is_closed = 1
78
+
79
+ AND db_redmine.issues.project_id = 3
80
+
81
+ AND db_redmine.issues.closed_on >= (Now() - INTERVAL 6 MONTH)
82
+
83
+ ) Query1
84
+
85
+ ON db_redmine.attachments.disk_filename = Query1.disk_filename
86
+
87
+ WHERE
88
+
89
+ db_redmine.attachments.disk_filename IS NOT NULL
90
+
91
+ AND Query1.disk_filename IS NULL
92
+
93
+ AND db_redmine.issue_statuses.is_closed = 1
94
+
95
+ AND db_redmine.issues.project_id = 3
96
+
97
+ AND db_redmine.issues.closed_on < (Now() - INTERVAL 6 MONTH)
98
+
99
+ ORDER BY
100
+
101
+ db_redmine.issues.id
102
+
103
+ , db_redmine.attachments.disk_filename
104
+
105
+ ```