database - 这个查询可能吗？

Question

提前对大量查询表示歉意。我一直在尝试，但终生无法让这个查询正常工作。当两个登录用户互相对战时，它会增加两个用户的输赢。 (这是石头剪刀布)。我可以让它为单个用户工作，但是当我尝试收集用户的“统计表”汇总时，我得到了重复项。

这是我的为简洁起见而缩写的架构

create table rps_user (
    user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(255),
    PRIMARY KEY (user_id),
    UNIQUE (username)
);

CREATE TABLE rps_session (
  session_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  player1_user_id INT UNSIGNED DEFAULT NULL,
  player2_user_id INT UNSIGNED DEFAULT NULL,
  connected BOOLEAN DEFAULT 0,
  PRIMARY KEY (session_id)
);

CREATE TABLE rps_game (
  game_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  game_number INT UNSIGNED DEFAULT NULL,
  session_id INT UNSIGNED NOT NULL,
  player1_choice ENUM('ROCK','PAPER','SCISSORS') DEFAULT NULL,
  player2_choice ENUM('ROCK','PAPER','SCISSORS') DEFAULT NULL,
  PRIMARY KEY (game_id)
);

这是我有效的查询

SELECT IF((player1_choice + 1) % 3 + 1 = player2_choice + 0 AND player1_user_id = rps_user.user_id OR
        (player2_choice + 1) % 3 + 1 = player1_choice + 0 AND player2_user_id = rps_user.user_id, 1, 0) AS win, 
    IF(player1_choice = player2_choice, 1, 0) as tie, 
    IF(player1_choice % 3 + 1 = player2_choice + 0 AND player1_user_id = rps_user.user_id OR
        player2_choice % 3 + 1 = player1_choice + 0 AND player2_user_id = rps_user.user_id, 1, 0) AS loss
    FROM rps_game INNER JOIN rps_session USING (session_id)
    INNER JOIN rps_user ON rps_session.player1_user_id = rps_user.user_id OR rps_session.player2_user_id = rps_user.user_id
    WHERE player1_choice IS NOT NULL AND player2_choice IS NOT NULL and rps_user.user_id = ?

没有的

SELECT username, SUM(IF((player1_choice + 1) % 3 + 1 = player2_choice + 0 AND player1_user_id = rps_user.user_id OR
        (player2_choice + 1) % 3 + 1 = player1_choice + 0 AND player2_user_id = rps_user.user_id, 1, 0)) AS wins, 
    SUM(IF(player1_choice = player2_choice, 1, 0)) AS ties,
    SUM(IF(player1_choice % 3 + 1 = player2_choice + 0 AND player1_user_id = rps_user.user_id OR
        player2_choice % 3 + 1 = player1_choice + 0 AND player2_user_id = rps_user.user_id, 1, 0)) AS losses
    FROM rps_game INNER JOIN rps_session USING (session_id)
    INNER JOIN rps_user ON rps_session.player1_user_id = rps_user.user_id OR rps_session.player2_user_id = rps_user.user_id
    WHERE player1_choice IS NOT NULL AND player2_choice IS NOT NULL
    GROUP BY user_id ORDER BY wins DESC

祝你好运，谢谢!

Answer 1

目前，您的表设计没有正确规范化 - 您将始终有两个不同的玩家与每个 rps_session 和 rps_game 记录相关联。根据我的选择，我会将它们规范化，以便它们中的每一个与 rps_user 表之间都有一个链接表。

话虽如此，您很可能无法做到这一点，因此有一个简单的答案:只需将第二个查询的胜负平局数字除以 2。这应该总是有效，对于每个一个用户是 user1，另一个用户是 user2 的游戏 - 这就是为什么您会看到双值的原因。

注意。如果您实际系统中三个表之间的关系比您在此处呈现的更复杂(这样您就不能依赖每个游戏总是有两个用户)，此解决方案将不可靠。