mysql加入两个非常复杂的查询

Question

我目前有两个非常复杂的查询，我用它们来获取分数和排名。第一个是所有参与者和他们的阶段排名分数，第二个是每个参与者分组并计算他们的总排名(阶段排名总和)。

首先查询获取每个阶段的所有参与者的分数和排名

SELECT * FROM (
  SELECT alias, catabbr, sh_dq, raw, sdq, dnf, 
@rownum := IF(@stage != stage_name, 1, @rownum + 1) AS rowNum,
@rank := IF(@prevVal != cTime, @rownum, @rank) AS rank,
@stage := stage_name as stage_name,
@prevVal := cTime AS cTime,
id, zeroTime
  FROM 
(SELECT @rownum:= 0) rn,
(SELECT @rank:= 1) av,
(SELECT @stage:= '') sv,
(SELECT @prevVal:= 0) pv,
(SELECT s.stage_name stage_name, sc.alias alias, sc.catAbbr catabbr, sc.sh_dq sh_dq, sc.time raw, sc.id id, sc.sdq sdq, sc.dnf dnf, 
    IF(sc.time = "0", 1, 0) AS zeroTime,
    ((ROUND(sc.time, 2) + (sc.miss * 5)) + ((sc.proc * 10) + (sc.saf * 10) + (sc.sog * 30)) - (sc.bthPoints)) cTime
    FROM matches m
    LEFT JOIN stages s ON s.match_id = m.id
    LEFT JOIN scores sc ON sc.stage_id = s.id
    WHERE m.matchuuid = 'E13A4C61-A2B8-48E2-BE1B-1FFB77CC5849'
    GROUP BY sc.alias, s.stage_name
    ORDER BY s.stage_name, zeroTime, cTime
) tv
) t
ORDER BY zeroTime, alias, stage_name;

输出:

+-----------+---------+-------+-----------------+-----+-----+--------+------+-----------------------+-------+----------+
| alias     | catabbr | sh_dq | raw             | sdq | dnf | rowNum | rank | stage_name            | cTime | zeroTime |
+-----------+---------+-------+-----------------+-----+-----+--------+------+-----------------------+-------+----------+
| Back Bob  | S       | 0     | 52.9799995422   | 0   | 1   | 54     | 54   | Stage 1               | 92.98 | 0        |
+-----------+---------+-------+-----------------+-----+-----+--------+------+-----------------------+-------+----------+
| Back Bob  | S       | 0     | 43.9099998474   | 0   | 0   | 46     | 46   | Stage 2               | 48.91 | 0        |
+-----------+---------+-------+-----------------+-----+-----+--------+------+-----------------------+-------+----------+
| Ben Scal  | ES      | 0     | 26.9699993134   | 0   | 0   | 27     | 27   | Stage 1               | 31.97 | 0        |
+-----------+---------+-------+-----------------+-----+-----+--------+------+-----------------------+-------+----------+
| Ben Scal  | ES      | 0     | 32.8800010681   | 0   | 0   | 38     | 38   | Stage 2               | 42.88 | 0        |
+-----------+---------+-------+-----------------+-----+-----+--------+------+-----------------------+-------+----------+

第二个查询按名称对参与者进行分组，并对他们的排名求和以获得最终排名

SELECT alias, catabbr, SUM(cTime) fTime, SUM(rank) fRank, zeroTime, rank
FROM (
  SELECT alias, catabbr, sh_dq, raw, sdq, dnf, 
@rownum := IF(@stage != stage_name, 1, @rownum + 1) AS rowNum,
@rank := IF(@prevVal != cTime, @rownum, @rank) AS rank,
@stage := stage_name as stage_name,
@prevVal := cTime AS cTime,
id, zeroTime
  FROM 
(SELECT @rownum:= 0) rn,
(SELECT @rank:= 1) av,
(SELECT @stage:= '') sv,
(SELECT @prevVal:= 0) pv,
(SELECT s.stage_name stage_name, sc.alias alias, sc.catAbbr catabbr, sc.sh_dq sh_dq, sc.time raw, sc.id id, sc.sdq sdq, sc.dnf dnf, 
    IF(sc.time = "0", 1, 0) AS zeroTime,
    ((ROUND(sc.time, 2) + (sc.miss * 5)) + ((sc.proc * 10) + (sc.saf * 10) + (sc.sog * 30)) - (sc.bthPoints)) cTime
    FROM matches m
    LEFT JOIN stages s ON s.match_id = m.id
    LEFT JOIN scores sc ON sc.stage_id = s.id
    WHERE m.matchuuid = 'E13A4C61-A2B8-48E2-BE1B-1FFB77CC5849'
    GROUP BY sc.alias, s.stage_name
    ORDER BY s.stage_name, zeroTime, cTime
) tv
) t
GROUP BY alias
ORDER BY zeroTime, fRank, fTime;

输出:

+-----------+-----------+-----------+-------+-----------+
| alias     | catabbr   | fTime     | fRank | zeroTime  |
+-----------+-----------+-----------+-------+-----------+
| Back Bob  | S         | 141.89    | 100   | 0         |
+-----------+-----------+-----------+-------+-----------+
| Ben Scal  | ES        | 74.85     | 68    | 0         |
+-----------+-----------+-----------+-------+-----------+

zeroTime 列使得我可以按大于 0 的时间排序，并正确计算排名。

当我尝试加入他们时，我没有得到任何结果。有没有办法将这两个查询组合/加入一个查询，以便它们输出以下内容？

+-----------+-----------+-----------+-------+-----------+---------------+---------------+---------------+---------------+
| alias     | catabbr   | fTime     | fRank | zeroTime  | Stage 1 Time  | Stage 1 Rank  | Stage 2 Time  | Stage 2 Rank  |
+-----------+-----------+-----------+-------+-----------+---------------+---------------+---------------+---------------+
| Ben Scal  | ES        | 74.85     | 68    | 0         | 31.97         | 27            | 42.88         | 38            |
+-----------+-----------+-----------+-------+-----------+---------------+---------------+---------------+---------------+
| Back Bob  | S         | 141.89    | 100   | 0         | 92.98         | 54            | 48.91         | 46            |
+-----------+-----------+-----------+-------+-----------+---------------+---------------+---------------+---------------+

这将进入一个 php 页面，因此我可以遍历并提取我需要的内容，我要做的主要事情是加入这两个查询。

我的结构和数据对于 sqlfiddle 来说太大了。这是匹配和阶段的结构和插入:http://pastebin.com/YvZevd5j这是乐谱的结构和插入:http://pastebin.com/jLBYxMvc

还有一点需要注意，游戏可以有 1 个或多个阶段和 1 个或多个参与者。

Answer 1

这个答案可能无法很好地扩展，因为您需要知道阶段的数量，但也许您可以通过循环或应用程序中的其他内容处理构建它。这是一个简化版本，您可以对其进行修改以使用您的数据。

SELECT 
  alias,
  catAbbr,
  sum(fTime) as fTime,
  sum(fRank) as fRank,
  sum(zeroTime) as zeroTime,
  sum(Stage_1_Time) as stage_1_Time,
  sum(Stage_1_Rank) as Stage_1_Rank,
  sum(Stage_2_Time) as stage_2_Time,
  sum(Stage_2_Rank) as Stage_2_Rank
FROM (
  SELECT 
    alias,
    catAbbr,
    0 as fTime,
    0 as fRank,
    0 as zeroTime,
    Stage_1_Time as stage_1_Time,
    Stage_1_Rank as Stage_1_Rank,
    0 as stage_2_Time,
    0 as Stage_2_Rank
  FROM (query that gets stage 1 info)
  UNION
  SELECT
    alias,
    catAbbr,
    0 as fTime,
    0 as fRank,
    0 as zeroTime,
    0 as stage_1_Time,
    0 as Stage_1_Rank,
    stage_2_Time as stage_2_Time,
    Stage_2_Rank as Stage_2_Rank
  FROM (query that gets stage 2 info)
  UNION
  SELECT
    alias,
    catAbbr,
    fTime as fTime,
    fRank as fRank,
    zeroTime as zeroTime,
    0 as stage_1_Time,
    0 as Stage_1_Rank,
    0 as stage_2_Time,
    0 as Stage_2_Rank
  FROM (query that gets averaged info)
) temp
GROUP BY alias, catAbbt