質問編集履歴
2
前提のテーブル構成のrankingカラムを0へ変更
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -132,13 +132,15 @@
|
|
|
132
132
|
|
|
133
133
|
VALUES
|
|
134
134
|
|
|
135
|
+
# ranking カラムは該当のソースコードを実行すると、次のコメントのようになる
|
|
136
|
+
|
|
135
|
-
(3, 1, 3, 4,
|
|
137
|
+
(3, 1, 3, 4, 0) # 合計点は 3-4=-1 であり、ranking は 2 にしたいが 1 となる
|
|
136
|
-
|
|
138
|
+
|
|
137
|
-
,(6, 5, 1, 1,
|
|
139
|
+
,(6, 5, 1, 1, 0) # 合計点は 1-1=0 であり、ranking は 2 となる
|
|
138
|
-
|
|
140
|
+
|
|
139
|
-
,(7, 5, 2, 0,
|
|
141
|
+
,(7, 5, 2, 0, 0) # 合計点は 2-0=2 であり、ranking は 1 となる
|
|
140
|
-
|
|
142
|
+
|
|
141
|
-
,(8, 1, 1, 2,
|
|
143
|
+
,(8, 1, 1, 2, 0) # 合計点は 1-2=-1 であり、ranking は 1 となる
|
|
142
144
|
|
|
143
145
|
;
|
|
144
146
|
|
1
該当のソースコードにPKを追加
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -60,7 +60,7 @@
|
|
|
60
60
|
|
|
61
61
|
CREATE TABLE p_supers
|
|
62
62
|
|
|
63
|
-
(`ID` int, `kind_id` int, `sentence` varchar(1000))
|
|
63
|
+
(`ID` int, `kind_id` int, `sentence` varchar(1000), primary key(`ID`))
|
|
64
64
|
|
|
65
65
|
;
|
|
66
66
|
|
|
@@ -98,7 +98,7 @@
|
|
|
98
98
|
|
|
99
99
|
CREATE TABLE p_threads
|
|
100
100
|
|
|
101
|
-
(`super_ID` int, `title` varchar(100))
|
|
101
|
+
(`super_ID` int, `title` varchar(100), primary key(`super_ID`))
|
|
102
102
|
|
|
103
103
|
;
|
|
104
104
|
|
|
@@ -122,7 +122,7 @@
|
|
|
122
122
|
|
|
123
123
|
CREATE TABLE p_comments
|
|
124
124
|
|
|
125
|
-
(`super_ID` int, `thread_ID` int, `up_count` int, `down_count` int, `ranking` int)
|
|
125
|
+
(`super_ID` int, `thread_ID` int, `up_count` int, `down_count` int, `ranking` int, primary key(`super_ID`))
|
|
126
126
|
|
|
127
127
|
;
|
|
128
128
|
|
|
@@ -148,7 +148,9 @@
|
|
|
148
148
|
|
|
149
149
|
CREATE TABLE a_up_comments
|
|
150
150
|
|
|
151
|
-
(`users_ID` int, `comment_ID` int, `created_at` datetime
|
|
151
|
+
(`users_ID` int, `comment_ID` int, `created_at` datetime
|
|
152
|
+
|
|
153
|
+
, foreign key (`comment_ID`) references p_comments(`super_ID`))
|
|
152
154
|
|
|
153
155
|
;
|
|
154
156
|
|
|
@@ -180,7 +182,9 @@
|
|
|
180
182
|
|
|
181
183
|
CREATE TABLE a_down_comments
|
|
182
184
|
|
|
183
|
-
(`users_ID` int, `comment_ID` int, `created_at` datetime
|
|
185
|
+
(`users_ID` int, `comment_ID` int, `created_at` datetime
|
|
186
|
+
|
|
187
|
+
, foreign key (`comment_ID`) references p_comments(`super_ID`))
|
|
184
188
|
|
|
185
189
|
;
|
|
186
190
|
|
|
@@ -206,6 +210,36 @@
|
|
|
206
210
|
|
|
207
211
|
;
|
|
208
212
|
|
|
213
|
+
|
|
214
|
+
|
|
215
|
+
# ranking を更新
|
|
216
|
+
|
|
217
|
+
update p_comments as t0
|
|
218
|
+
|
|
219
|
+
inner join (
|
|
220
|
+
|
|
221
|
+
# thread_ID のグループで ranking の順位を取得
|
|
222
|
+
|
|
223
|
+
select super_ID, up_count, down_count, (
|
|
224
|
+
|
|
225
|
+
select count(*) +1
|
|
226
|
+
|
|
227
|
+
from p_comments as t2
|
|
228
|
+
|
|
229
|
+
where 1
|
|
230
|
+
|
|
231
|
+
and ( t2.up_count - t2.down_count ) > ( t1.up_count - t1.down_count )
|
|
232
|
+
|
|
233
|
+
and t2.thread_ID = t1.thread_ID
|
|
234
|
+
|
|
235
|
+
) as ranking
|
|
236
|
+
|
|
237
|
+
from p_comments AS t1
|
|
238
|
+
|
|
239
|
+
) as t3 on t0.super_ID = t3.super_ID
|
|
240
|
+
|
|
241
|
+
set t0.ranking = t3.ranking;
|
|
242
|
+
|
|
209
243
|
```
|
|
210
244
|
|
|
211
245
|
|
|
@@ -214,7 +248,7 @@
|
|
|
214
248
|
|
|
215
249
|
こちらが連番でなく同位になるという問題の`UPDATE`になります。
|
|
216
250
|
|
|
217
|
-
実行する → [http://sqlfiddle.com/#!9/1
|
|
251
|
+
実行する → [http://sqlfiddle.com/#!9/b0b213/1](http://sqlfiddle.com/#!9/b0b213/1)
|
|
218
252
|
|
|
219
253
|
```SQL
|
|
220
254
|
|