MySQL排球榜 [英] MySQL Volleyball Standings
本文介绍了MySQL排球榜的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个充满游戏结果的数据库表,想知道我是否可以计算以下内容:
I have a database table full of game by game results and want to know if I can calculate the following:
- GP(玩过的游戏)
- 胜利
- 损失
- 积分(每次胜利2积分,每次失败1积分)
这是我的表结构:
CREATE TABLE `results` (
`id` int(10) unsigned NOT NULL auto_increment,
`home_team_id` int(10) unsigned NOT NULL,
`home_score` int(3) unsigned NOT NULL,
`visit_team_id` int(10) unsigned NOT NULL,
`visit_score` int(3) unsigned NOT NULL,
`tcl_id` int(3) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
以及一些测试结果:
INSERT INTO `results` VALUES(1, 1, 21, 2, 25, 68);
INSERT INTO `results` VALUES(2, 3, 21, 4, 17, 68);
INSERT INTO `results` VALUES(3, 1, 25, 3, 9, 68);
INSERT INTO `results` VALUES(4, 2, 7, 4, 22, 68);
INSERT INTO `results` VALUES(5, 1, 19, 4, 20, 68);
INSERT INTO `results` VALUES(6, 2, 24, 3, 26, 68);
这就是决赛桌的样子(结果并不完全准确):
Here is what a final table would look something like this (results are not completely accurate):
+-------------------+----+------+-------+--------+
| Team Name | GP | Wins | Loses | Points |
+-------------------+----+------+-------+--------+
| Spikers | 4 | 4 | 0 | 8 |
| Leapers | 4 | 2 | 2 | 6 |
| Ground Control | 4 | 1 | 3 | 5 |
| Touch Guys | 4 | 0 | 4 | 4 |
+-------------------+----+------+-------+--------+
需要为
tcl_id
添加WHERE子句,如下所示:
like so:
结果在哪里.tcl_id= 68
WHERE results.tcl_id = 68
谢谢.
推荐答案
这应该做您想要的:
SELECT
team_id,
COUNT(*) AS GP,
SUM(is_win) AS Wins,
SUM(NOT is_win) AS Losses,
2 * SUM(is_win) + SUM(NOT is_win) AS Points
FROM
(
SELECT
home_team_id AS team_id,
home_score > visit_score AS is_win
FROM results
WHERE tcl_id = 68
UNION ALL
SELECT
visit_team_id AS team_id,
home_score < visit_score AS is_win
FROM results
WHERE tcl_id = 68
) T1
GROUP BY team_id
ORDER BY Points DESC
示例数据的输出:
4, 3, 2, 1, 5
3, 3, 2, 1, 5
1, 3, 1, 2, 4
2, 3, 1, 2, 4
注意:
- 您的示例数据似乎与您的预期输出不匹配-测试数据仅玩了6场比赛,但您的预期输出却有8场比赛.这就是为什么我的输出与您的输出不同的原因.
- 您尚未提供用于从团队ID中获取团队名称的表格.只需将表与团队名称一起加入即可以所需的格式获得结果.
这篇关于MySQL排球榜的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文