主页 / dba / 问题 / 188436
Accepted
John Biddle
Asked: 2017-10-14 09:43:56 +0800 CST

多表 Groupwise 最大值问题，我需要第二个字段以及最大值

  • 772

我有一个遇到问题的查询，涉及下面描述的 3 个表。还有更多字段对此查询无关紧要。

users:
  id
  display_name
matches:
  id
  league_seasons_id
  date_played
  winner_id
  loser_id
players:
  id
  match_id
  player_id
  nth_match
  division
  result   # values are "Win" or "Loss"
  end_hcp

我正在尝试产生这样的输出：

  Name        Hcp   Wins   Losses   High Run    HR Pct of Hcp
  Alan        705    12       2       27            3.83%
  Bill        815    10       4       30            3.68%
  Charlie     611     9       5       12            1.96%
  Denise      788     8       6       15            1.90%
  ...

下面的查询大部分都有效，但Hcp列除外。在玩家表中，每场比赛有 2 条记录，一条是获胜者的信息，另一条是失败者的信息。在每条记录中都有start_hcp和end_hcp，即球员在比赛前的差点和基于比赛的新差点。

我需要end_hcp从每个玩家玩到现在的最后一场比赛（查询的日期/时间）。我猜会涉及一个子查询，但我还没有弄清楚它是什么。

SELECT 
    p.division AS Division,
    u.display_name AS Name,
    p.end_hcp AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "Loss", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM 
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE 
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses
mysql greatest-n-per-group

2 个回答

  1. 我不是 MySQL 专家，但是像下面这样的东西（对于 end_hcp）呢？

    编辑：基于语法注释

    SELECT 
    p.division AS Division,
    u.display_name AS Name,
    (select max(end_hcp) from lwljhb_lwl_players where player_id = p.player_id and match_id =(select max(p.match_id) from lwljhb_lwl_players where p.player_id = u.id))
 AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM 
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE 
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses
    • 0
  2. Best Answer

    根据上面@Rick James 的几条评论，我找到了有关 Groupwise Max 问题的信息。虽然我没有找到直接解决我需要从包含 max(nth_match) 的记录中获取第二个字段的解决方案，但我能够使用制作临时表的技术来获取我需要的数据，然后将它们连接到我的数据表中以获得我的答案。

    如果有人感兴趣，这是代码。此外，如果有人看到比制作两个临时表更好的方法，我很乐意看到它，在这个之前我必须尝试过 20 种不同的非工作想法。

    DROP TEMPORARY TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1
SELECT player_id,
       max(nth_match) AS last_match
FROM `lwljhb_lwl_players` AS p INNER JOIN `lwljhb_lwl_matches` AS m
ON    p.match_id = m.id
WHERE m.playoff_round = 0 AND
      m.league_seasons_id = 9
GROUP BY p.player_id;

DROP TEMPORARY TABLE IF EXISTS temp2;
CREATE TEMPORARY TABLE temp2
SELECT p.player_id,
       t1.last_match,
       p.end_hcp
FROM `temp1` AS t1 INNER JOIN `lwljhb_lwl_players` AS p
ON t1.last_match = p.nth_match AND t1.player_id = p.player_id
GROUP BY p.player_id;

SELECT p.division AS Division,
       u.display_name AS Name,
       t2.end_hcp AS Handicap,
       count(if(result = "Win", 1, NULL)) AS Wins,
       count(if(result = "Loss", 1, NULL)) AS Losses,
       max(high_run) AS "High Run",
       concat(truncate((max(high_run) * 100 / t2.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM `lwljhb_lwl_players` AS p
INNER JOIN `lwljhb_users` AS u
INNER JOIN `lwljhb_lwl_matches` AS m
INNER JOIN `temp2` AS t2
ON p.match_id = m.id AND
   p.player_id = u.id AND
   t2.player_id = p.player_id
WHERE u.display_name <> "Bye" AND
      m.league_seasons_id = 9 AND
      p.division = "A" AND
      m.playoff_round = 0
GROUP BY u.display_name
ORDER BY Wins DESC, Losses, t2.end_hcp DESC;
    • 0

相关问题