blob: d247ee007ee15d06b3abe80a18789266d153bf95 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
SELECT
:plr_1_name AS plr_1_name,
:plr_1_text_color AS plr_1_text_color,
:plr_2_name AS plr_2_name,
:plr_2_text_color AS plr_2_text_color,
sum(CASE
WHEN plr_X_name = :plr_1_name
AND plr_X_color_int = :plr_1_color_int
AND victor_name = :plr_1_name
AND victor_color_int = :plr_1_color_int
THEN 1 ELSE 0 END) AS plr_1_X_wins,
sum(CASE
WHEN plr_O_name = :plr_1_name
AND plr_O_color_int = :plr_1_color_int
AND victor_name = :plr_1_name
AND victor_color_int = :plr_1_color_int
THEN 1 ELSE 0 END) AS plr_1_O_wins,
sum(CASE
WHEN plr_X_name = :plr_2_name
AND plr_X_color_int = :plr_2_color_int
AND victor_name = :plr_2_name
AND victor_color_int = :plr_2_color_int
THEN 1 ELSE 0 END) AS plr_2_X_wins,
sum(CASE
WHEN plr_O_name = :plr_2_name
AND plr_O_color_int = :plr_2_color_int
AND victor_name = :plr_2_name
AND victor_color_int = :plr_2_color_int
THEN 1 ELSE 0 END) AS plr_2_O_wins,
sum(CASE
WHEN plr_X_name = :plr_1_name
AND plr_X_color_int = :plr_1_color_int
AND plr_O_name = :plr_2_name
AND plr_O_color_int = :plr_2_color_int
AND victor_name = ''
AND victor_color_int = 0
THEN 1 ELSE 0 END) AS game_X_O_draws,
sum(CASE
WHEN plr_X_name = :plr_2_name
AND plr_X_color_int = :plr_2_color_int
AND plr_O_name = :plr_1_name
AND plr_O_color_int = :plr_1_color_int
AND victor_name = ''
AND victor_color_int = 0
THEN 1 ELSE 0 END) AS game_O_X_draws
FROM game_log
WHERE
(
plr_X_name = :plr_1_name
AND plr_X_color_int = :plr_1_color_int
AND plr_O_name = :plr_2_name
AND plr_O_color_int = :plr_2_color_int
) OR (
plr_X_name = :plr_2_name
AND plr_X_color_int = :plr_2_color_int
AND plr_O_name = :plr_1_name
AND plr_O_color_int = :plr_1_color_int
)
;
|
