回答編集履歴
3
goal_timeを日時と勘違いした時の間違いが残っていた
answer
CHANGED
|
@@ -41,29 +41,38 @@
|
|
|
41
41
|
|
|
42
42
|
```sql
|
|
43
43
|
SELECT
|
|
44
|
-
|
|
44
|
+
(
|
|
45
|
-
|
|
45
|
+
SELECT
|
|
46
|
-
|
|
46
|
+
COUNT(1) + 1
|
|
47
|
-
|
|
47
|
+
FROM
|
|
48
|
-
|
|
48
|
+
goals g
|
|
49
|
+
INNER JOIN pairings p
|
|
50
|
+
ON g.pairing_id = p.id
|
|
49
|
-
|
|
51
|
+
WHERE
|
|
50
|
-
|
|
52
|
+
goals.player_id = g.player_id
|
|
53
|
+
AND (
|
|
54
|
+
(
|
|
51
|
-
|
|
55
|
+
goals.goal_time > g.goal_time
|
|
56
|
+
AND pairings.kickoff = p.kickoff
|
|
57
|
+
) OR (
|
|
58
|
+
pairings.kickoff > p.kickoff
|
|
59
|
+
)
|
|
60
|
+
)
|
|
52
|
-
|
|
61
|
+
) AS 点数
|
|
53
|
-
|
|
62
|
+
, pairings.kickoff AS 試合日時
|
|
54
|
-
|
|
63
|
+
, countries.name AS 対戦相手
|
|
55
|
-
|
|
64
|
+
, goals.goal_time AS ゴールタイム
|
|
56
65
|
FROM
|
|
57
|
-
|
|
66
|
+
players
|
|
58
|
-
|
|
67
|
+
INNER JOIN goals
|
|
59
|
-
|
|
68
|
+
ON players.id = goals.player_id
|
|
60
|
-
|
|
69
|
+
INNER JOIN pairings
|
|
61
|
-
|
|
70
|
+
ON goals.pairing_id = pairings.id
|
|
62
|
-
|
|
71
|
+
INNER JOIN countries
|
|
63
|
-
|
|
72
|
+
ON pairings.enemy_country_id = countries.id
|
|
64
73
|
WHERE
|
|
65
|
-
|
|
74
|
+
players.id = 対象プレーヤーのID
|
|
66
|
-
ORDER BY
|
|
75
|
+
ORDER BY
|
|
67
|
-
|
|
76
|
+
pairings.kickoff
|
|
68
|
-
|
|
77
|
+
, goals.goal_time
|
|
69
78
|
```
|
2
goal_timeが日時時ではないための修正
answer
CHANGED
|
@@ -28,7 +28,8 @@
|
|
|
28
28
|
WHERE
|
|
29
29
|
tmp.player_id = 対象プレーヤーのID
|
|
30
30
|
ORDER BY
|
|
31
|
+
tmp.kickoff
|
|
31
|
-
tmp.goal_time
|
|
32
|
+
, tmp.goal_time
|
|
32
33
|
```
|
|
33
34
|
|
|
34
35
|
デーブルの関係など詳細が分からないのでもろもろエスパーですが、こんな感じでしょうか。
|
|
@@ -63,5 +64,6 @@
|
|
|
63
64
|
WHERE
|
|
64
65
|
players.id = 対象プレーヤーのID
|
|
65
66
|
ORDER BY
|
|
67
|
+
pairings.kickoff
|
|
66
|
-
goals.goal_time
|
|
68
|
+
, goals.goal_time
|
|
67
69
|
```
|
1
他のパターンの追記
answer
CHANGED
|
@@ -32,4 +32,36 @@
|
|
|
32
32
|
```
|
|
33
33
|
|
|
34
34
|
デーブルの関係など詳細が分からないのでもろもろエスパーですが、こんな感じでしょうか。
|
|
35
|
-
Window関数を使用しているので、MySQL8.0からしか動きません。
|
|
35
|
+
Window関数を使用しているので、MySQL8.0からしか動きません。
|
|
36
|
+
|
|
37
|
+
追記
|
|
38
|
+
上記の「点数」は「1試合内の何点目か」を出していました。
|
|
39
|
+
「該当選手の生涯で何点目か」であれば、下記のような感じです。
|
|
40
|
+
|
|
41
|
+
```sql
|
|
42
|
+
SELECT
|
|
43
|
+
(
|
|
44
|
+
SELECT
|
|
45
|
+
COUNT(1) + 1
|
|
46
|
+
FROM
|
|
47
|
+
goals g
|
|
48
|
+
WHERE
|
|
49
|
+
goals.player_id = g.player_id
|
|
50
|
+
AND goals.goal_time > g.goal_time
|
|
51
|
+
) AS 点数
|
|
52
|
+
, pairings.kickoff AS 試合日時
|
|
53
|
+
, countries.name AS 対戦相手
|
|
54
|
+
, goals.goal_time AS ゴールタイム
|
|
55
|
+
FROM
|
|
56
|
+
players
|
|
57
|
+
INNER JOIN goals
|
|
58
|
+
ON players.id = goals.player_id
|
|
59
|
+
INNER JOIN pairings
|
|
60
|
+
ON goals.pairing_id = pairings.id
|
|
61
|
+
INNER JOIN countries
|
|
62
|
+
ON pairings.enemy_country_id = countries.id
|
|
63
|
+
WHERE
|
|
64
|
+
players.id = 対象プレーヤーのID
|
|
65
|
+
ORDER BY
|
|
66
|
+
goals.goal_time
|
|
67
|
+
```
|