質問編集履歴
2
前提のテーブル構成のrankingカラムを0へ変更
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -65,10 +65,11 @@
|
|
|
65
65
|
INSERT INTO p_comments
|
|
66
66
|
(`super_ID`, `thread_ID`, `up_count`, `down_count`, `ranking`)
|
|
67
67
|
VALUES
|
|
68
|
+
# ranking カラムは該当のソースコードを実行すると、次のコメントのようになる
|
|
68
|
-
(3, 1, 3, 4,
|
|
69
|
+
(3, 1, 3, 4, 0) # 合計点は 3-4=-1 であり、ranking は 2 にしたいが 1 となる
|
|
69
|
-
,(6, 5, 1, 1,
|
|
70
|
+
,(6, 5, 1, 1, 0) # 合計点は 1-1=0 であり、ranking は 2 となる
|
|
70
|
-
,(7, 5, 2, 0,
|
|
71
|
+
,(7, 5, 2, 0, 0) # 合計点は 2-0=2 であり、ranking は 1 となる
|
|
71
|
-
,(8, 1, 1, 2,
|
|
72
|
+
,(8, 1, 1, 2, 0) # 合計点は 1-2=-1 であり、ranking は 1 となる
|
|
72
73
|
;
|
|
73
74
|
|
|
74
75
|
-- 評価を上げる
|
1
該当のソースコードにPKを追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -29,7 +29,7 @@
|
|
|
29
29
|
```sql
|
|
30
30
|
-- 投稿 (スーパータイプ)
|
|
31
31
|
CREATE TABLE p_supers
|
|
32
|
-
(`ID` int, `kind_id` int, `sentence` varchar(1000))
|
|
32
|
+
(`ID` int, `kind_id` int, `sentence` varchar(1000), primary key(`ID`))
|
|
33
33
|
;
|
|
34
34
|
INSERT INTO p_supers
|
|
35
35
|
(`ID`, `kind_id`, `sentence`)
|
|
@@ -48,7 +48,7 @@
|
|
|
48
48
|
|
|
49
49
|
-- 投稿 (サブタイプ/スレッド)
|
|
50
50
|
CREATE TABLE p_threads
|
|
51
|
-
(`super_ID` int, `title` varchar(100))
|
|
51
|
+
(`super_ID` int, `title` varchar(100), primary key(`super_ID`))
|
|
52
52
|
;
|
|
53
53
|
INSERT INTO p_threads
|
|
54
54
|
(`super_ID`, `title`)
|
|
@@ -60,7 +60,7 @@
|
|
|
60
60
|
|
|
61
61
|
-- 投稿 (サブタイプ/コメント)
|
|
62
62
|
CREATE TABLE p_comments
|
|
63
|
-
(`super_ID` int, `thread_ID` int, `up_count` int, `down_count` int, `ranking` int)
|
|
63
|
+
(`super_ID` int, `thread_ID` int, `up_count` int, `down_count` int, `ranking` int, primary key(`super_ID`))
|
|
64
64
|
;
|
|
65
65
|
INSERT INTO p_comments
|
|
66
66
|
(`super_ID`, `thread_ID`, `up_count`, `down_count`, `ranking`)
|
|
@@ -73,7 +73,8 @@
|
|
|
73
73
|
|
|
74
74
|
-- 評価を上げる
|
|
75
75
|
CREATE TABLE a_up_comments
|
|
76
|
-
(`users_ID` int, `comment_ID` int, `created_at` datetime
|
|
76
|
+
(`users_ID` int, `comment_ID` int, `created_at` datetime
|
|
77
|
+
, foreign key (`comment_ID`) references p_comments(`super_ID`))
|
|
77
78
|
;
|
|
78
79
|
INSERT INTO a_up_comments
|
|
79
80
|
(`users_ID`, `comment_ID`, `created_at`)
|
|
@@ -89,7 +90,8 @@
|
|
|
89
90
|
|
|
90
91
|
-- 評価を下げる
|
|
91
92
|
CREATE TABLE a_down_comments
|
|
92
|
-
(`users_ID` int, `comment_ID` int, `created_at` datetime
|
|
93
|
+
(`users_ID` int, `comment_ID` int, `created_at` datetime
|
|
94
|
+
, foreign key (`comment_ID`) references p_comments(`super_ID`))
|
|
93
95
|
;
|
|
94
96
|
INSERT INTO a_down_comments
|
|
95
97
|
(`users_ID`, `comment_ID`, `created_at`)
|
|
@@ -102,11 +104,26 @@
|
|
|
102
104
|
,(1, 8, '2020-12-02 12:00:00.000006')
|
|
103
105
|
,(2, 6, '2020-12-02 12:00:00.000007')
|
|
104
106
|
;
|
|
107
|
+
|
|
108
|
+
# ranking を更新
|
|
109
|
+
update p_comments as t0
|
|
110
|
+
inner join (
|
|
111
|
+
# thread_ID のグループで ranking の順位を取得
|
|
112
|
+
select super_ID, up_count, down_count, (
|
|
113
|
+
select count(*) +1
|
|
114
|
+
from p_comments as t2
|
|
115
|
+
where 1
|
|
116
|
+
and ( t2.up_count - t2.down_count ) > ( t1.up_count - t1.down_count )
|
|
117
|
+
and t2.thread_ID = t1.thread_ID
|
|
118
|
+
) as ranking
|
|
119
|
+
from p_comments AS t1
|
|
120
|
+
) as t3 on t0.super_ID = t3.super_ID
|
|
121
|
+
set t0.ranking = t3.ranking;
|
|
105
122
|
```
|
|
106
123
|
|
|
107
124
|
###該当のソースコード
|
|
108
125
|
こちらが連番でなく同位になるという問題の`UPDATE`になります。
|
|
109
|
-
実行する → [http://sqlfiddle.com/#!9/
|
|
126
|
+
実行する → [http://sqlfiddle.com/#!9/b0b213/1](http://sqlfiddle.com/#!9/b0b213/1)
|
|
110
127
|
```SQL
|
|
111
128
|
# ranking を更新
|
|
112
129
|
update p_comments as t0
|