回答編集履歴
1
update
answer
CHANGED
|
@@ -1,9 +1,11 @@
|
|
|
1
|
-
|
|
1
|
+
※ランクカラムを設定する
|
|
2
|
+
```SQL
|
|
2
3
|
CREATE TABLE p_comments
|
|
3
4
|
(super_ID int unique,
|
|
4
|
-
|
|
5
|
+
thread_ID int,
|
|
5
6
|
up_count int,
|
|
6
|
-
down_count int
|
|
7
|
+
down_count int,
|
|
8
|
+
rank int null);
|
|
7
9
|
|
|
8
10
|
INSERT INTO p_comments
|
|
9
11
|
(super_ID, thread_ID, up_count, down_count)
|
|
@@ -11,4 +13,13 @@
|
|
|
11
13
|
(3, 1, 3, 4),
|
|
12
14
|
(6, 5, 1, 1),
|
|
13
15
|
(7, 5, 2, 0),
|
|
14
|
-
(8, 1, 1, 2);
|
|
16
|
+
(8, 1, 1, 2);
|
|
17
|
+
|
|
18
|
+
|
|
19
|
+
insert into p_comments(super_ID,rank) select super_ID,(
|
|
20
|
+
select count(*)+1 from p_comments
|
|
21
|
+
where up_count-down_count-t1.up_count+t1.down_count<0 or
|
|
22
|
+
(up_count-down_count-t1.up_count+t1.down_count=0 and super_ID<t1.super_ID) ) as rank
|
|
23
|
+
from p_comments as t1
|
|
24
|
+
on duplicate key update rank=values(rank)
|
|
25
|
+
```
|