时间戳

首页 > 解决方案 > 如何在 MySQL 中将多个查询解决方案编写为单个查询

mysql - 如何在 MySQL 中将多个查询解决方案编写为单个查询

问题描述

我的任务是通过他们的 id 找到所有那些在数据库中具有（至少一个，但是）最低及格分数最少的科目（等级是 6 年级）。我已经设法用三个查询编写了解决方案，但是我的任务是将其编写为 MySQL 中的单个查询。先感谢您。

-- 1. single query "solution"
SELECT subject_id FROM (SELECT subject_id, COUNT(*) AS six_count 
FROM exams WHERE grade = 6
GROUP BY subject_id) AS sixes
WHERE subject_id = (SELECT MIN(six_count) FROM sixes);

-- 2. multiple queries solution
CREATE TABLE sixes AS (SELECT subject_id, COUNT(*) AS six_count 
FROM exams WHERE grade = 6
GROUP BY subject_id);

SELECT subject_id FROM sixes
WHERE subject_id = (SELECT MIN(six_count) FROM sixes);

DROP TABLE sixes;

编辑： 考试表示例：

| subject_id | student_id | exam_year | exam_mark | grade | exam_date  |
|          1 |    20100022|      2011 |     'apr' |    10 | 2011-04-11 |
|          2 |    20100055|      2011 |     'oct' |     6 | 2011-10-04 |
|          3 |    20110030|      2011 |    'jan1' |     7 | 2011-01-26 |
|          5 |    20110055|      2011 |    'jan2' |     6 | 2011-02-13 |
|          5 |    20110001|      2011 |    'jun1' |     8 | 2011-06-23 |

标签： mysqlsql

解决方案

这应该可以解决问题。子查询选择第一个最低的六位数。主查询选择具有该编号的所有主题。诀窍在于ORDER BY count(*) LIMIT 1，它使子查询返回计数最低的记录。

SELECT
  subject_id,
  count(*) as six_count
FROM exams
WHERE grade = 6
GROUP BY subject_id
HAVING count(*) = 
  ( SELECT count(*)
    FROM exams
    WHERE grade = 6
    GROUP BY subject_id
    ORDER BY count(*)
    LIMIT 1
  )

推荐阅读