1
delete from match_score where match_id = "f44764f7-5cca-410d-b3b4-b7f59f9da3b1";

1
select
2
    master_match_schedule_id,
3
    organization_id,
4
    win_count
5
from (
6
    select
7
        tm.master_match_schedule_id,
8
        p.organization_id,
9
        count(*) as win_count,
10
        row_number() over (
11
          partition by tm.master_match_schedule_id
12
          order by count(*) desc
13
        ) as rn
14
    from team_event_matches tm
15
    join match_schedule ms on ms.match_schedule_id = tm.match_schedule_id
16
    join matches m on ms.match_id = m.match_id
17
    join teams t on t.team_id = m.winner_id
18
    join participants p on p.participant_id = t.participant_one_id
19
    where ms.division_id = 'div-03fd4f8e805edecc356a4b6c06cbb285'
20
    and ms.tournament_id = '257de693732eb1bdb4ab69aaada3a6e2'
21
    and tm.team_event_code = 'TEAM-CC24129B'
22
    group by
23
    tm.master_match_schedule_id,
24
    p.organization_id
25
) t
26
where rn = 1
27
order by master_match_schedule_id;

1
(MySQL):badminton_matches_scoring>desc team_event_matches;
2
+--------------------------+--------------+------+-----+-------------------+-----------------------------------------------+
3
| Field                    | Type         | Null | Key | Default           | Extra                                         |
4
+--------------------------+--------------+------+-----+-------------------+-----------------------------------------------+
5
| team_event_id            | varchar(255) | NO   | PRI | <null>            |                                               |
6
| group_id                 | varchar(255) | NO   |     | <null>            |                                               |
7
| organization_id          | varchar(255) | NO   |     | <null>            |                                               |
8
| team_score               | int          | NO   |     | <null>            |                                               |
9
| is_winner                | tinyint      | NO   |     | <null>            |                                               |
10
| team_event_code          | varchar(32)  | YES  |     | <null>            |                                               |
11
| master_match_schedule_id | varchar(48)  | YES  |     | <null>            |                                               |
12
| match_schedule_id        | varchar(48)  | YES  |     | <null>            |                                               |
13
| create_time              | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
14
| update_time              | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
15
+--------------------------+--------------+------+-----+-------------------+-----------------------------------------------+
16

17
10 rows in set
18
Time: 0.102s
19
mysql@110.40.192.228
20
(MySQL):badminton_matches_scoring>

1
select
2
    master_match_schedule_id,
3
    organization_id,
4
    team_match_signups_id,
5
    win_count
6
from (
7
    select
8
        tm.master_match_schedule_id,
9
        tm.team_match_signups_id,
10
        p.organization_id,
11
        count(*) as win_count,
12
        row_number() over (
13
          partition by tm.master_match_schedule_id
14
          order by count(*) desc
15
        ) as rn
16
    from team_event_matches tm
17
    join team_match_signups tms on tms.team_match_signups_id = tm.team_match_signups_id
18
    join match_schedule ms on ms.match_schedule_id = tm.match_schedule_id
19
    join matches m on ms.match_id = m.match_id
20
    join teams t on t.team_id = m.winner_id
21
    join participants p on p.participant_id = t.participant_one_id
22
    where ms.division_id = 'div-03fd4f8e805edecc356a4b6c06cbb285'
23
    and ms.tournament_id = '257de693732eb1bdb4ab69aaada3a6e2'
24
    and tm.team_event_code = 'TEAM-CC24129B'
25
    group by
26
    tm.master_match_schedule_id,
27
    tm.team_match_signups_id,
28
    p.organization_id
29
) t
30
where rn = 1
31
order by master_match_schedule_id;

1
select
2
    master_match_schedule_id,
3
    organization_id,
4
    team_match_signups_id,
5
    win_count
6
from (
7
    select
8
        tm.master_match_schedule_id,
9
        tm.team_match_signups_id,
10
        p.organization_id,
11
        count(*) as win_count,
12
        row_number() over (
13
          partition by tm.master_match_schedule_id
14
          order by count(*) desc
15
        ) as rn
16
    from team_event_matches tm
17
    join team_match_signups tms on tms.team_match_signups_id = tm.team_match_signups_id
18
    join match_schedule ms on ms.match_schedule_id = tm.match_schedule_id
19
    join matches m on ms.match_id = m.match_id
20
    join teams t on t.team_id = m.winner_id
21
    join participants p on p.participant_id = t.participant_one_id
22
    where ms.division_id = 'div-03fd4f8e805edecc356a4b6c06cbb285'
23
    and ms.tournament_id = '257de693732eb1bdb4ab69aaada3a6e2'
24
    and tm.team_event_code = 'TEAM-CC24129B'
25
    group by
26
    tm.master_match_schedule_id,
27
    tm.team_match_signups_id,
28
    p.organization_id
29
) t
30
where rn = 1
31
order by master_match_schedule_id;
32

33

34

35
-- 查找team_even_matches中记录多少个不同的master_match_schedule_id
36
SELECT DISTINCT team_match_signups_id,master_match_schedule_id FROM team_event_matches
37

38
-- 查询小组赛中各选手获胜场次
39
SELECT m.winner_id AS player_id,
40
               COUNT(*)    AS win_count
41
        FROM match_schedule r
42
                 JOIN matches m ON r.match_id = m.match_id
43
        WHERE r.tournament_id = "257de693732eb1bdb4ab69aaada3a6e2"
44
          AND r.division_id = "div-03fd4f8e805edecc356a4b6c06cbb285"
45
          AND r.event_type = "mens_singles"
46
          AND r.group_number = 1
47
          AND m.winner_id IS NOT NULL
48
        GROUP BY m.winner_id
49

50

51
-- 第一种情况：找出match_schedule中表示两个队伍的记录
52
SELECT
53
  tem.master_match_schedule_id,
54
  ms.side_one_player_one_id,
55
  ms.side_two_player_one_id,
56
  ms.division_id,
57
  ms.event_type,
58
  ms.team_event_code,
59
  ms.match_id,
60
  ms.group_number
61
FROM
62
  team_event_matches tem
63
  JOIN match_schedule ms ON tem.master_match_schedule_id = ms.match_schedule_id
64
-- 第二种情况：找出match_schedule中表示每个队伍内部的比赛（胜场数）
65
SELECT
66
--   tem.team_match_signups_id,
67
--   ms.match_id,
68
--   m.winner_id AS win_tiny_team_id,-- 此处的team指代对阵时的一方
69
  t.team_match_signups_id,
70
  COUNT(t.team_match_signups_id) AS win_count
71
FROM
72
  team_event_matches tem
73
  JOIN team_match_signups tms ON tem.team_match_signups_id = tms.team_match_signups_id
74
  JOIN match_schedule ms ON tem.match_schedule_id = ms.match_schedule_id
75
  JOIN matches m ON ms.match_id = m.match_id
76
  JOIN teams t ON m.winner_id = t.team_id
77
  JOIN round_robin_participants rrp ON tem.team_match_signups_id = rrp.participant_id_one
78
  WHERE tem.team_event_code = "TEAM-CC24129B"
79
    AND ms.division_id = "div-03fd4f8e805edecc356a4b6c06cbb285"
80
    AND ms.tournament_id = "257de693732eb1bdb4ab69aaada3a6e2"
81
    AND rrp.group_number = 1
82
  GROUP BY t.team_match_signups_id
83

84
-- ------------------------------规则 1.3.2 两人胜场相同 → 看交手-------------------------------------------------------------------------------------------------------------
85
-- 查询选手对阵结果
86
SELECT
87
  CASE
88
    WHEN t.participant_one_id = "p002_m07"
89
      OR t.participant_two_id = "p002_m07"
90
    THEN 'WIN'
91
    ELSE 'LOSE'
92
  END AS result
93
FROM matches m
94
JOIN teams t ON m.winner_id = t.team_id
95
WHERE m.match_id = "5846ad7d-9df2-4e5a-b12d-e1965570992d"
96
AND (t.participant_one_id = "p002_m07" OR t.participant_two_id = "p003_m01")
97

98
-- 查询tms队伍对阵结果（补全team_event_matches表is_winner字段）（胜次：即team_event_matches表中master_match_schedule字段记录的比赛胜利次数）
99
SELECT
100
  tem.team_match_signups_id,
101
  ms.match_schedule_id,
102
  ms.match_id,
103
  ms.match_schedule_name,
104
--   ms.division_id,
105
  t1.team_match_signups_id AS team_match_signups_one,
106
  t2.team_match_signups_id AS team_match_signups_two,
107
  CASE WHEN t.team_match_signups_id = tem.team_match_signups_id
108
    THEN 1
109
    ELSE 0
110
  END AS result
111
FROM
112
  team_event_matches tem
113
  JOIN match_schedule ms ON tem.match_schedule_id = ms.match_schedule_id
114
  JOIN matches m ON ms.match_id = m.match_id
115
  JOIN teams t ON m.winner_id = t.team_id
116
  JOIN teams t1 ON ms.team_one_id = t1.team_id
117
  JOIN teams t2 ON ms.team_two_id = t2.team_id
118
WHERE tem.team_event_code = "TEAM-CC24129B"
119
  AND ms.division_id = "div-03fd4f8e805edecc356a4b6c06cbb285"
120
--   AND ms.event_type = "mens_singles"
121
  AND t.is_team_event = 1
122

123
SELECT
124
  tem.team_event_id,
125
  t.team_id
126
FROM team_event_matches tem
127
  JOIN match_schedule ms ON tem.match_schedule_id = ms.match_schedule_id
128
  JOIN teams t ON ms.match_id = t.match_id
129

130
SELECT
131
    tem.team_event_id,
132
    tem.team_match_signups_id,
133
    t1.team_id AS team_one_id,
134
    t1.team_match_signups_id AS team_one_signups_id,
135
    t2.team_id AS team_two_id,
136
    t2.team_match_signups_id AS team_two_signups_id,
137
    ms.match_schedule_name
138
FROM team_event_matches tem
139
JOIN match_schedule ms
140
    ON tem.match_schedule_id = ms.match_schedule_id
141
JOIN teams t1
142
    ON ms.match_id = t1.match_id
143
    AND t1.is_team_event = 1
144
JOIN teams t2
145
    ON ms.match_id = t2.match_id
146
    AND t2.is_team_event = 1
147
    AND t1.team_id < t2.team_id
148
WHERE tem.team_event_code = 'TEAM-CC24129B';
149

150
-- 变式，查询两个team_match_signups队伍之间的比赛，判断这两个tms队伍在胜场数相同情况下的名次先后
151
SELECT
152
  tem.team_match_signups_id,
153
  ms.match_schedule_name,
154
  t1.team_match_signups_id AS team_match_signups_one,
155
  t2.team_match_signups_id AS team_match_signups_two,
156
  CASE WHEN t.team_match_signups_id = tem.team_match_signups_id
157
    THEN 1
158
    ELSE 0
159
  END AS result
160
FROM team_event_matches tem
161
  JOIN match_schedule ms ON tem.match_schedule_id = ms.match_schedule_id
162
  JOIN matches m ON ms.match_id = m.match_id
163
  JOIN teams t ON m.winner_id = t.team_id
164
  JOIN teams t1 ON ms.team_one_id = t1.team_id
165
  JOIN teams t2 ON ms.team_two_id = t2.team_id
166
WHERE tem.team_event_code = "TEAM-CC24129B"
167
  AND ms.division_id = "div-03fd4f8e805edecc356a4b6c06cbb285"
168
--   AND ms.event_type = "mens_singles"
169
  AND t.is_team_event = 1
170
  AND (
171
    (t1.team_match_signups_id = '3' AND t2.team_match_signups_id = '1')
172
    OR
173
    (t1.team_match_signups_id = '1' AND t2.team_match_signups_id = '3')
174
  );
175

176
  -- 再变式，贴合项目
177
  SELECT
178
  tem.team_match_signups_id,
179
--   ms.match_schedule_name,
180
--   t1.team_match_signups_id AS team_match_signups_one,
181
--   t2.team_match_signups_id AS team_match_signups_two,
182
  CASE WHEN t.team_match_signups_id = tem.team_match_signups_id
183
    THEN "WIN"
184
    ELSE "LOSE"
185
  END AS result
186
FROM team_event_matches tem
187
  JOIN match_schedule ms ON tem.match_schedule_id = ms.match_schedule_id
188
  JOIN matches m ON ms.match_id = m.match_id
189
  JOIN teams t ON m.winner_id = t.team_id
190
  JOIN teams t1 ON ms.team_one_id = t1.team_id
191
  JOIN teams t2 ON ms.team_two_id = t2.team_id
192
WHERE tem.team_event_code = "TEAM-CC24129B"
193
  AND ms.division_id = "div-03fd4f8e805edecc356a4b6c06cbb285"
194
--   AND ms.event_type = "mens_singles"
195
  AND t.is_team_event = 1
196
  AND (
197
    (t1.team_match_signups_id = '2' AND t2.team_match_signups_id = '1')
198
    OR
199
    (t1.team_match_signups_id = '1' AND t2.team_match_signups_id = '2')
200
  );
201

202
-- ------------------------------≥3队tms：规则 1.3.3 看净胜局数-------------------------------------------------------------------------------------------------------------
203
-- 单项赛
204
SELECT
205
    t.participant_one_id AS participant_id,
206
    SUM(CASE WHEN m.winner_id = t.team_id THEN 1 ELSE 0 END) AS win_count,
207
    SUM(CASE WHEN m.winner_id != t.team_id THEN 1 ELSE 0 END) AS lose_count,
208
    SUM(CASE WHEN m.winner_id = t.team_id THEN 1 ELSE -1 END) AS net_games
209
FROM teams t
210
  JOIN matches m ON t.match_id = m.match_id
211
WHERE t.participant_one_id = "p003_m01"
212
GROUP BY t.participant_one_id
213
-- 团体赛 定义：该队(tms)在所有单项比赛中，赢的局数总和，减去，输的局数总和
214
SELECT
215
  tem.team_match_signups_id,
216
  SUM(CASE WHEN m.winner_id = t.team_id THEN 1 ELSE 0 END) AS win_count,
217
  SUM(CASE WHEN m.winner_id != t.team_id THEN 1 ELSE 0 END) AS lose_count,
218
  SUM(CASE WHEN m.winner_id = t.team_id THEN 1 ELSE -1 END) AS net_games
219
FROM team_event_matches tem
220
 JOIN match_schedule ms ON tem.match_schedule_id = ms.match_schedule_id
221
 JOIN matches m ON ms.match_id = m.match_id
222
 JOIN teams t ON m.match_id = t.match_id
223
WHERE tem.team_match_signups_id = '1'
224
GROUP BY tem.team_match_signups_id
225
-- AI修改过的
226
SELECT
227
   tem.team_match_signups_id,
228
   SUM(CASE WHEN t.team_match_signups_id = tem.team_match_signups_id THEN 1 ELSE 0 END) AS win_count,
229
   SUM(CASE WHEN t.team_match_signups_id != tem.team_match_signups_id OR t.team_match_signups_id IS NULL THEN 1 ELSE 0 END) AS lose_count
230
FROM team_event_matches tem
231
 JOIN match_schedule ms ON tem.match_schedule_id = ms.match_schedule_id
232
 JOIN matches m ON ms.match_id = m.match_id
233
 LEFT JOIN teams t ON m.winner_id = t.team_id
234
WHERE tem.team_event_code = 'TEAM-CC24129B'
235
  AND tem.team_match_signups_id = '1'
236
GROUP BY tem.team_match_signups_id
237

238
  -- 每个tms输掉的比赛数
239
SELECT
240
  tem.team_match_signups_id,
241
  COUNT(*) AS lose_count
242
FROM team_event_matches tem
243
JOIN match_schedule ms ON tem.match_schedule_id = ms.match_schedule_id
244
JOIN matches m ON ms.match_id = m.match_id
245
JOIN teams t ON m.winner_id = t.team_id
246
WHERE t.team_match_signups_id != tem.team_match_signups_id
247
GROUP BY tem.team_match_signups_id;
248

249

250
-- ----------------------------------------------------------AI------------------------------------------------------------------
251

252
SELECT * FROM teams WHERE is_team_event = 1
253
SELECT * FROM match_schedule WHERE team_event_code IS NOT NULL
254

255

256
SELECT
257
    TABLE_SCHEMA AS 数据库名,
258
    TABLE_NAME AS 表名,
259
    COLUMN_NAME AS 字段名,
260
    DATA_TYPE AS 数据类型
261
FROM INFORMATION_SCHEMA.COLUMNS
262
WHERE COLUMN_NAME = 'password'        -- 替换为你的字段名
263
  AND TABLE_SCHEMA = 'badminton_matches_scoring';    -- 替换为你的数据库名（可选）
264

265

266

267
CREATE TABLE `auth_user` (
268
    `user_id` varchar(36) NOT NULL,
269
    `user_name` varchar(100) NOT NULL COMMENT '用户名',
270
    `password` varchar(100) NOT NULL COMMENT '密码，加密',
271
    `role_id` int(11) DEFAULT NULL COMMENT '角色ID',
272
    `account_locked` tinyint(1) DEFAULT '0' COMMENT '账户是否锁定：1-是，0-否',
273
    `is_enabled` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-是，0-否',
274
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
275
    `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
276
    PRIMARY KEY (`user_id`)
277
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
278

279
CREATE TABLE `auth_role` (
280
    `role_id` varchar(36) NOT NULL,
281
    `user_name` varchar(100) NOT NULL COMMENT '用户名',
282
    `password` varchar(100) NOT NULL COMMENT '密码，加密',
283
    `role_id` int(11) DEFAULT NULL COMMENT '角色ID',
284
    `account_locked` tinyint(1) DEFAULT '0' COMMENT '账户是否锁定：1-是，0-否',
285
    `is_enabled` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-是，0-否',
286
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
287
    `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
288
    PRIMARY KEY (`role_id`)
289
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';