sql—如何在mysql中将多查询解决方案作为单个查询编写

huwehgph 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(281)

我的任务是根据他们的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 |

sql mysql

来源：https://stackoverflow.com/questions/53891321/how-to-write-a-multiple-query-solution-as-a-single-query-in-mysql

2条答案

按热度按时间

taor4pac1#

这种模式应该能奏效。广义名称。

SELECT subjectID
  FROM TEST_DATA
 WHERE grade = 6
 GROUP
    BY SubjectID
HAVING COUNT(1) = 
         (  SELECT count(1) AS minCount
              FROM TEST_DATA
             WHERE grade = 6
             GROUP 
                BY subjectID
             ORDER
                BY minCount 
             LIMIT 1
         );

赞(0）回复(0）举报 2021-06-16

yyyllmsg2#

这应该能奏效。子查询选择第一个最低的6。主查询选择具有该编号的所有主题。诀窍在于 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
  )

赞(0）回复(0）举报 2021-06-16

我来回答

sql—如何在mysql中将多查询解决方案作为单个查询编写

2条答案

相关问题

热门标签

最新问答