题目

请先筛选出试卷作答数小于3的类别tag，统计将其转换为大写后对应的原本试卷作答数。如果转换后tag并没有发生变化，不输出该条结果。

题目其实没表达地特别清楚，包括给的示例、答案提示都是有误的，讲人话来解释题意就是：

  • 首先，先筛选出作答数小于3的试卷类别（无论是否完成，都算一次作答）
  • 然后，如果该类别是小写的，则再找出其对应的大写形式，再者，在返回大写所对应的作答次数 —— 即 返回“小写类别 ：大写类别的作答次数”
tag小写
tag大写
tag大写的作答次数
c++ C++ 6


  • 如果小于3的试卷类别都是大写形式的，则不需要输出结果（即，结果为空）

解题步骤

步骤 1：先找出作答次数小于3、且是小写形式的类别tag

  • 这一步比较简单了，主要是还需用到UPPER函数 
    SELECT ei.tag, COUNT(er.exam_id) AS answer_cnt, UPPER(ei.tag) AS up_tag
FROM exam_record AS er LEFT OUTER JOIN examination_info AS ei
ON er.exam_id = ei.exam_id
/* 除去转换后并没有发生变化的tag，即大写的tag  */
WHERE ei.tag != UPPER(ei.tag)
GROUP BY ei.tag, UPPER(ei.tag)
/* 再筛选出作答次数小于3的小写的tag  */
HAVING answer_cnt < 3


步骤 2：再建立一个查询，统计每一个类别tag（无论大小写）的作答次数

  • 和上述的查询语句基本相同，更加简单 
    SELECT ei.tag, COUNT(er.exam_id) AS answer_cnt
FROM exam_record AS er LEFT OUTER JOIN examination_info AS ei
ON er.exam_id = ei.exam_id
GROUP BY ei.tag

步骤 3：联结两个表，找出“小写形式 - 对应大写形式 - 对应大写形式的作答次数”

  • 重点在于联结条件：要把结果1中的小写类别的大写形式，与结果2的大写类别匹配起来 
SELECT t1.tag, t2.answer_cnt /* 结果2的作答次数才是大写类别的作答次数 */
FROM
    (SELECT ei.tag, COUNT(er.exam_id) AS answer_cnt, UPPER(ei.tag) AS up_tag
    FROM exam_record AS er LEFT OUTER JOIN examination_info AS ei
    ON er.exam_id = ei.exam_id
    WHERE ei.tag != UPPER(ei.tag)
    GROUP BY ei.tag, UPPER(ei.tag)
    HAVING answer_cnt < 3) AS t1

INNER JOIN

    (SELECT ei.tag, COUNT(er.exam_id) AS answer_cnt
    FROM exam_record AS er LEFT OUTER JOIN examination_info AS ei
    ON er.exam_id = ei.exam_id
    GROUP BY ei.tag) AS t2

/* 结果1的小写类别与结果2的大写类别匹配 */
ON t1.up_tag = t2.tag