一、知识点总结与拓展

1）字符串的常见处理
    a、将字母转换为大写格式upper( )
    b、索引式提取substring_index(字段名,'索引符号',位置) 
    c、直接提取substring(字段名，开始位置，结束位置)
    d、计算字符串长度char_length(字段名) 和 length(字段名)
        * length(字段名)汉字算3个字符
        * char_length(字段名) 汉字算1个字符
    e、转换数据格式：cast(字段名 AS 数据类型)

2）表连接
常见的表连接主要有两种方式。
    a、第一种方式是直接从两张表里筛选数据：
        常见的写法有SELECT 字段名 FROM 表名1，表名2（具体的书写案例见下方解题步骤）；
    b、第二种方式是使用join来连接数据（左右内外连接）（详见下方连接，之前解题种整理的内容）。

二、解题步骤

1）试卷的类别tag可能出现大小写混乱的情况
  • 字符的大写转换函数upper()
2）请先筛选出试卷作答数小于3的类别tag。
  • 统计各类试卷作答情况,并命名为t1 
SELECT tag,COUNT(uid) answer_cnt
FROM exam_record  LEFT JOIN examination_info  USING(exam_id)
GROUP BY tag


3）统计将其转换为大写后对应的原本试卷作答数。
  • 自连接t1表，左边命名为a，右边命名为b 
WITH t1 as (
    SELECT tag, COUNT(uid) as answer_cnt
    FROM exam_record
    LEFT JOIN examination_info USING(exam_id)
    GROUP BY tag
)

SELECT a.*, b.*
FROM t1 a,t1 b;#自连接t1表

  • 先看那些大小写被混写的数据：把左边数据都转换为大写后的数据 
WITH t1 as (
    SELECT tag, COUNT(uid) as answer_cnt
    FROM exam_record
    LEFT JOIN examination_info USING(exam_id)
    GROUP BY tag
)

SELECT a.*, b.*
FROM t1 a,t1 b
WHERE UPPER(a.tag)=b.tag; #取a表中tag改为大写情况后和b的tag表相同的数据

从上表数据可以看出，目前存在两种情况，第一种情况，左边tag本身就是大写，第二种情况左边tag是小写。

4）如果转换后tag并没有发生变化，不输出该条结果。
  • tag没有变化的意思就是左边的tag和右边的tag是不相等的，即a.tag<>b.tag。
  • 以及加上筛选条件a.answer<3。完成~ 
WITH t1 AS (
SELECT tag,COUNT(uid) answer_cnt
FROM exam_record  LEFT JOIN examination_info  USING(exam_id)
GROUP BY tag
)
/*建立子表，查询每个tag的答题数*/
SELECT a.tag,b.answer_cnt 
FROM t1 a,t1 b # 表自连接
WHERE UPPER(a.tag)=b.tag #取a表中tag改为大写情况后和b的tag表相同的数据
AND a.tag<>b.tag #取a表的tag为小写b表的tag为大写
AND a.answer_cnt<3;#取小写tag（a表）的作答数小于3的数据