mysql - 怎么把这两条sql语句结合起来

Question

我在 sql 方面不是很有经验，但通常可以构建基本查询，但这对我来说太复杂了。

我有两个查询，每个查询都单独给出了我需要的结果，但是我需要将它们组合起来以获得我需要的最终结果。

在一个查询中，它会获取特定学生的所有“分数”并返回平均值。在另一个查询中，我从表中删除了最高和最低记录。

我想结合这些查询，首先去除最高分和最低分，然后仅从剩余记录中返回平均分。请帮助指导我使用正确的语法。

我的查询:

---

这将返回所有最高分和最低分:

SELECT *
FROM `scores`
WHERE `tot_score` = (
SELECT MAX( `tot_score` )
FROM `scores` )
OR `tot_score` = (
SELECT MIN( `tot_score` )
FROM `scores` ) 

---

这将返回平均分数:

SELECT `pres_id`, COUNT( `pres_id` ) , ROUND( AVG( `tot_score` ) , 2 ) , `username`, `name`, `pres_day`
FROM `scores`, `users`
WHERE `users.id_user` = `scores.pres_id`
GROUP BY `pres_id`

我怎样才能将这些结合起来以获得我需要的结果？

Answer 1

我建议您在单个子查询中计算最小值和最大值。然后你可以简单地使用 where 子句来过滤你想要的行:

SELECT s.`pres_id`, COUNT( `pres_id` ), ROUND( AVG( `tot_score` ) , 2 ) ,
       `username`, `name`, `pres_day`
FROM `scores` s join
     `users` u
     on u.id_user = s.pres_id join
     (select s.pres_id, max(tot_score) as maxts, min(tot_score) as mints
      from scores s
      group by s.pres_id
     ) ssum
     on u.id_user = ssum.pres_id
where s.tot_score > ssum.mints and s.tot_score < ssum.maxts
GROUP BY s.`pres_id`;

我猜你想去掉每个学生的最高分和最低分。如果您只想忽略所有 学生的最高和最低，请使用以下内容:

SELECT s.`pres_id`, COUNT( `pres_id` ), ROUND( AVG( `tot_score` ) , 2 ) ,
       `username`, `name`, `pres_day`
FROM `scores` s join
     `users` u
     on u.id_user = s.pres_id cross join
     (select max(tot_score) as maxts, min(tot_score) as mints
      from scores s
     ) ssum
where s.tot_score > ssum.mints and s.tot_score < ssum.maxts
GROUP BY s.`pres_id`;

编辑:

当一个学生只有一个分数时保持分数的修改:

SELECT s.`pres_id`, COUNT( `pres_id` ), ROUND( AVG( `tot_score` ) , 2 ) ,
       `username`, `name`, `pres_day`
FROM `scores` s join
     `users` u
     on u.id_user = s.pres_id join
     (select s.pres_id, max(tot_score) as maxts, min(tot_score) as mints,
             count(*) as cnt
      from scores s
      group by s.pres_id
     ) ssum
     on u.id_user = ssum.pres_id
where (s.tot_score > ssum.mints and s.tot_score < ssum.maxts) or (cnt = 1)
GROUP BY s.`pres_id`;