mysql - MySQL 从两个表中除法计算
问题描述
对于我的页面,我必须从两个表中划分值。
我从表一(游戏)中得到计算的第一个值,其中两个值相加。从表二 (game_stats) 我得到发票的第二个值。现在应该将Rounds和Kills值划分并保存或更新为表game_stats_more中的值。
我目前的问题是,当我计算值时,我得到一个错误,即AS值无法识别。我从哪里得到选择错误?
SELECT SUM(game.match_score_team_1 + game.match_score_team_2) AS Rounds, game_stats.match_stats_kills AS Kills, Rounds / Kills AS KPR FROM game INNER JOIN game_stats WHERE game.match_id = 1 AND game_stats.user_id = 1
错误信息:
#1054 - Unknown table field 'Rounds' in field list
示例架构:
CREATE TABLE `game` (
`match_id` int(3) NOT NULL,
`team_id` int(3) NOT NULL,
`match_team_2` varchar(255) COLLATE utf8_german2_ci NOT NULL,
`match_score_team_1` int(2) NOT NULL,
`match_score_team_2` int(2) NOT NULL,
`match_score_role_id` int(3) NOT NULL,
`match_role_id` int(3) NOT NULL,
`date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;
INSERT INTO `game` (`match_id`, `team_id`, `match_team_2`, `match_score_team_1`, `match_score_team_2`, `match_score_role_id`, `match_role_id`, `date`) VALUES
(1, 7, 'TestGegner', 7, 2, 1, 1, '2020-06-10'),
CREATE TABLE `game_stats` (
`match_stats_id` int(3) NOT NULL,
`match_id` int(3) NOT NULL,
`user_id` int(3) NOT NULL,
`match_stats_kills` int(2) NOT NULL,
`match_stats_deaths` int(2) NOT NULL,
`match_stats_entry_kill` int(2) NOT NULL,
`match_stats_entry_death` int(2) NOT NULL,
`match_stats_clutch` int(2) NOT NULL,
`match_stats_plants` int(2) NOT NULL,
`match_stats_hs` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;
INSERT INTO `game_stats` (`match_stats_id`, `match_id`, `user_id`, `match_stats_kills`, `match_stats_deaths`, `match_stats_entry_kill`, `match_stats_entry_death`, `match_stats_clutch`, `match_stats_plants`, `match_stats_hs`) VALUES
(1, 1, 1, 7, 5, 1, 3, 1, 4, 4),
(2, 1, 2, 6, 6, 2, 2, 0, 1, 3);
CREATE TABLE `game_stats_more` (
`game_stats_id` int(3) NOT NULL,
`game_id` int(3) NOT NULL,
`game_stats_more_kpr` int(3) NOT NULL,
`game_stats_more_hsp` int(3) NOT NULL,
`game_stats_more_kd` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
INSERT INTO `game_stats_more` (`game_stats_id`, `game_id`, `game_stats_more_kpr`, `game_stats_more_hsp`, `game_stats_more_kd`) VALUES
(1, 1, 0, 0, 0);
解决方案
您不能在同一选择语句中使用为字段定义的别名
SELECT (game.match_score_team_1 + game.match_score_team_2) AS Rounds,
game_stats.match_stats_kills AS Kills,
(game.match_score_team_1 + game.match_score_team_2) /
game_stats.match_stats_kills AS KPR
FROM game
INNER JOIN game_stats ON game.match_id=game_stats.match_id
WHERE game.match_id = 1 AND game_stats.user_id = 1
推荐阅读
- json - 如何处理复杂的 API 响应以在 Flutter 中制作卡片?
- python - 运行代码以从 github 获取文件时出现 HTTP 错误
- python-3.x - np.save 正在将浮点数转换为奇怪的字符
- javascript - eval 函数表达式
- python - 添加新数据时无法从mysql数据库中获取python中的新数据
- audio - Nixos 默认音频设备
- php - PHP 在下一个按钮单击时创建 2 个会话文件
- excel - 将 .rtf 文件的内容加载到 RichTextBox 控件中
- ios - Firebase-Performance iOS-SDK:再次将isDataCollectionEnabled设置为false仅在重新安装后生效
- html - django 加载静态图片失败