主页 / dba / 问题 / 95376
Accepted
Dannymh
Asked: 2015-03-17 07:59:26 +0800 CST

分组时的案例陈述？

  • 772

我有包含多个游戏的数据，每个游戏都会有几个用户选择它，他们都会被分组到一个星期内。

在那个周末，我评估结果并打分。在游戏列表中有一个奖励游戏，我想从该游戏中获得利润，但它不起作用，因为我相信有一个分组它总是返回 null。

我的查询是：

SELECT COUNT(game.game_id) AS correct_picks, 
       sum(game.points) points,
       pick.user_id, game.bonus,
       user.user_id, user.username, 
       user.avatar_date, user.avatar_width, user.avatar_height, 
       user.gravatar, user.gender,
       CASE 
         WHEN game.bonus = 1 THEN ABS(SUM((game.hteam_score - game.vteam_score) - (pick.hscoreary - pick.ascoreary)))
         ELSE NULL
       END AS margin
FROM      xf_nflj_pickem_game game
LEFT JOIN xf_nflj_pickem_pick as pick ON (pick.game_id = game.game_id)
LEFT JOIN xf_user             as user ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id)
        AND game.week_id = 1
        AND game.pool_id = 1
        AND pick.team_id <> 0
GROUP BY pick.user_id

如您所见，我有案例陈述。现在，如果只有一个游戏，那么这似乎可行，但当有多个游戏时，它只会返回 null。

关于如何在单个查询中实现此目的的任何想法？

担

mysql

2 个回答

  1. Best Answer

    不要误会我的意思。我建议您摆脱用户信息以使您的查询更具可读性，因为用户信息超出了您的答案范围。恕我直言，如果没有充分的理由（通常是性能），最好有更多具有单一目的的小查询，而不是有一个大查询来检索您的应用程序需要的所有信息。小查询更易于维护，更容易理解（多年后看到）......你可以在这里添加你喜欢的无聊美德:-)

    我试图概述一个答案，因为我不完全理解你想要实现的目标。

    我想只有一周的奖励游戏和轮询，所以下面的查询总是返回一个数字：

    select (game.hteam_score - game.vteam_score)
from xf_nflj_pickem_game as game 
where game.bonus = 1 and game.week_id = 1 and game.pool_id = 1;

    所以我想你想把之前数字和的差加起来pick.hscoreary - pick.ascoreary。我已经重写了您的查询：

    SELECT pick.user_id,
       count(game.game_id) as correct_picks, 
       sum(game.points)    as points,
       -- game.bonus,      -- cannot aggregate on this field       
       SUM(
         CASE 
         WHEN pick.hscoreary is not NULL
         THEN ABS( (select (game.hteam_score - game.vteam_score)
                    from xf_nflj_pickem_game as game 
                    where game.bonus = 1 and game.week_id = 1 and game.pool_id = 1) 
                    - (pick.hscoreary - pick.ascoreary)
                 )
         ELSE 0
         END) 
       as margin

FROM      xf_nflj_pickem_game as game
LEFT JOIN xf_nflj_pickem_pick as pick ON game.game_id = pick.game_id 
WHERE       game.winner = pick.team_id
       AND game.week_id = 1
       AND game.pool_id = 1
       AND pick.team_id <> 0
GROUP BY pick.user_id;

    不知道前面的查询是否正确。但你可以试试 :-)

    您可以从先前查询的结果集中检索有关用户的信息：

    SELECT user.username FROM xf_user where user_id IN(...)
    • 0

  2. 下面修复了这个

    SELECT COUNT(game.game_id) AS correct_picks, sum(game.points) points, pick.user_id, user.user_id, user.username, user.avatar_date, user.avatar_width, user.avatar_height, user.gravatar, user.gender , (select ABS(SUM((game.hteam_score - game.vteam_score) - (pick.hscoreary - pick.ascoreary))) AS margin FROM xf_nflj_pickem_pick pick LEFT JOIN xf_nflj_pickem_game game ON game.game_id = pick.game_id WHERE game.bonus=1 AND pick.user_id = user.user_id and game.week_id=?) AS margin FROM xf_nflj_pickem_game game LEFT JOIN xf_nflj_pickem_pick as pick ON (pick.game_id = game.game_id) LEFT JOIN xf_user as user ON (user.user_id = pick.user_id) WHERE (game.winner = pick.team_id) AND game.week_id = ? AND game.pool_id = ? AND pick.team_id <> 0 GROUP BY pick.user_id ORDER BY points DESC, margin ASC, user.username

    • 0

相关问题