MySQL 从另一个表中选择前 4 个男性分数和前 2 个女性分数

Question

我有一个 MySQL 查询问题，我觉得它需要连接表，但我在连接查询方面不是很有经验，想知道是否有人可以帮助我吗？

我有两张 table 。第一个叫做“大学”，看起来像这样:

id | name
 1 | University One
 2 | University Two

我还有第二张看起来像竞争对手的 table ，看起来像这样:

id | universityid | male | r1 | r2 | r3 | r4

其中“universityid”是指第一个表的外键，“male”是确定参赛者性别的 bool 值，r1 - r4 是不同轮次比赛的分数。

所以这张表可能看起来像:

id | universityid | male | r1  | r2  | r3  | r4
-------------------------------------------------
 1 | 1            | 1    | 200 | 100 | 150 | 200
 2 | 1            | 1    | 50  | 100 | 150 | 200
 3 | 1            | 1    | 50  | 100 | 150 | 200
 4 | 1            | 1    | 50  | 100 | 150 | 200
 5 | 1            | 0    | 50  | 100 | 150 | 150
 6 | 1            | 0    | 50  | 100 | 150 | 150
 7 | 2            | 1    | 200 | 200 | 150 | 200
 8 | 2            | 1    | 200 | 100 | 150 | 200
 9 | 2            | 1    | 50  | 100 | 150 | 200
 10| 2            | 1    | 50  | 200 | 150 | 200
 11| 2            | 0    | 50  | 100 | 150 | 150
 12| 2            | 0    | 50  | 100 | 150 | 150

我想做的是找出每所大学每一轮 (r1 - r2) 前 4 名男性分数和前 2 名女性分数的总和，并返回每一轮的总分。然后将它们加在一起得出每所大学所有回合的总分，然后根据这些总分对大学行进行 DESC 排序。

所以返回表可能看起来像这样

university.name | r1total | r2total | r3total | r4total | totalscore
---------------------------------------------------------------------
uni2name        | 600     | 800     | 900     | 1100    | 3400

uni1name        | 450     | 600     | 900     | 1100    | 3050

如有任何帮助解决此问题，我们将不胜感激。

Answer 1

SELECT universityid,SUM(r1sum),SUM(r2sum),SUM(r3sum),SUM(r4sum),
SUM(r1sum+r2sum+r3sum+r4sum)as TotalScore 
FROM
(SELECT
 universityid,
CAST(SUBSTRING_INDEX(
   GROUP_CONCAT(r1 ORDER BY r1 DESC),
   ',', 1)as unsigned)+
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r1 ORDER BY r1 DESC),',0'),
   ',', 2),',',-1)as unsigned)+
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r1 ORDER BY r1 DESC),',0'),
   ',', 3),',',-1)as unsigned)+
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r1 ORDER BY r1 DESC),',0'),
   ',', 4),',',-1)as unsigned)as r1sum,
 CAST(SUBSTRING_INDEX(
   GROUP_CONCAT(r2 ORDER BY r2 DESC),
   ',', 1)as unsigned)+
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r2 ORDER BY r2 DESC),',0'),
   ',', 2),',',-1)as unsigned)+
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r2 ORDER BY r2 DESC),',0'),
   ',', 3),',',-1)as unsigned)+
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r2 ORDER BY r2 DESC),',0'),
   ',', 4),',',-1)as unsigned)as r2sum,
 CAST(SUBSTRING_INDEX(
   GROUP_CONCAT(r3 ORDER BY r3 DESC),
   ',', 1)as unsigned)+
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r3 ORDER BY r3 DESC),',0'),
   ',', 2),',',-1)as unsigned)+
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r3 ORDER BY r3 DESC),',0'),
   ',', 3),',',-1)as unsigned)+
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r3 ORDER BY r3 DESC),',0'),
   ',', 4),',',-1)as unsigned)as r3sum,
  CAST(SUBSTRING_INDEX(
   GROUP_CONCAT(r4 ORDER BY r4 DESC),
   ',', 1)as unsigned)+
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r4 ORDER BY r4 DESC),',0'),
   ',', 2),',',-1)as unsigned)+
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r4 ORDER BY r4 DESC),',0'),
   ',', 3),',',-1)as unsigned)+
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r4 ORDER BY r4 DESC),',0'),
   ',', 4),',',-1)as unsigned)as r4sum


FROM
 competitors
WHERE male=1
GROUP BY
 universityid
UNION ALL
SELECT
 universityid,
CAST(SUBSTRING_INDEX(
   GROUP_CONCAT(r1 ORDER BY r1 DESC),
   ',', 1)as unsigned)+
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r1 ORDER BY r1 DESC),',0'),
   ',', 2),',',-1)as unsigned)as r1sum,
 CAST(SUBSTRING_INDEX(
   GROUP_CONCAT(r2 ORDER BY r2 DESC),
   ',', 1)as unsigned)+
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r2 ORDER BY r2 DESC),',0'),
   ',', 2),',',-1)as unsigned)as r2sum,
  CAST(SUBSTRING_INDEX(
   GROUP_CONCAT(r3 ORDER BY r3 DESC),
   ',', 1)as unsigned)+
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r3 ORDER BY r3 DESC),',0'),
   ',', 2),',',-1)as unsigned)as r3sum,
   CAST(SUBSTRING_INDEX(
   GROUP_CONCAT(r4 ORDER BY r4 DESC),
   ',', 1)as unsigned)+
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(
   GROUP_CONCAT(r4 ORDER BY r4 DESC),',0'),
   ',', 2),',',-1)as unsigned)as r4sum
FROM
 competitors
WHERE male=0
GROUP BY
 universityid)x
GROUP BY universityid
ORDER BY TotalScore DESC

Fiddle