回答編集履歴
1
update
test
CHANGED
|
@@ -1,14 +1,18 @@
|
|
|
1
|
-
|
|
1
|
+
※ランクカラムを設定する
|
|
2
|
+
|
|
3
|
+
```SQL
|
|
2
4
|
|
|
3
5
|
CREATE TABLE p_comments
|
|
4
6
|
|
|
5
7
|
(super_ID int unique,
|
|
6
8
|
|
|
7
|
-
|
|
9
|
+
thread_ID int,
|
|
8
10
|
|
|
9
11
|
up_count int,
|
|
10
12
|
|
|
11
|
-
down_count int
|
|
13
|
+
down_count int,
|
|
14
|
+
|
|
15
|
+
rank int null);
|
|
12
16
|
|
|
13
17
|
|
|
14
18
|
|
|
@@ -25,3 +29,21 @@
|
|
|
25
29
|
(7, 5, 2, 0),
|
|
26
30
|
|
|
27
31
|
(8, 1, 1, 2);
|
|
32
|
+
|
|
33
|
+
|
|
34
|
+
|
|
35
|
+
|
|
36
|
+
|
|
37
|
+
insert into p_comments(super_ID,rank) select super_ID,(
|
|
38
|
+
|
|
39
|
+
select count(*)+1 from p_comments
|
|
40
|
+
|
|
41
|
+
where up_count-down_count-t1.up_count+t1.down_count<0 or
|
|
42
|
+
|
|
43
|
+
(up_count-down_count-t1.up_count+t1.down_count=0 and super_ID<t1.super_ID) ) as rank
|
|
44
|
+
|
|
45
|
+
from p_comments as t1
|
|
46
|
+
|
|
47
|
+
on duplicate key update rank=values(rank)
|
|
48
|
+
|
|
49
|
+
```
|