常用函数：其他类型函数

1、聚合函数：group_concat

• 功能：用于在分组时，将指定字段的值进行合并拼接成一个字符串

• 场景：分组聚合、行列转换

• 语法：

• group_concat(  [distinct] col [order by col] [separator 分隔符]  )
  
  distinct：对元素的值进行去重
  order by：按照某一列的值进行排序
  separator：用于指定分隔符，不给默认为逗号作为分隔符

• 示例：

• -- 查询所有学生的个人信息和考试科目，将考试科目合并为一列
  with t1 as(
  select s_id, group_concat(c_name) as l_name
  from score
  join Course C on Score.c_id = C.c_id
  group by s_id)
  select *
  from student
  join t1 on Student.s_id = t1.s_id
  ;

2、逻辑判断函数：case when

• 功能：基于不同的条件，返回不同的结果

• 场景：如果 A 则 B，如果 C 则 D ……，多条件的判断场景

• 语法：

• -- 语法1：单列等值判断
  case col when v1 then r1
  	     when v2 then r2
  	     ……
  	     else rn
  end
  
  -- 语法2：任意条件判断
  case when 条件1 then r1
  	 when 条件2 then r2
  	 ……
  	 eles rn
  end

• 示例：

• -- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
  SELECT
  	course.c_name,
  	p.* 
  FROM
  	course
  	JOIN (
  SELECT
  	t.c_id,
  	SUM( IF ( t.LEVEL = 'level1', 1, 0 ) ) '[100-85]',
  	SUM( IF ( t.LEVEL = 'level2', 1, 0 ) ) '[85-70]',
  	SUM( IF ( t.LEVEL = 'level3', 1, 0 ) ) '[70-60]',
  	SUM( IF ( t.LEVEL = 'level4', 1, 0 ) ) '[<60]',
  	COUNT( t.s_id ) sumstu 
  FROM
  	(
  SELECT
  	c_id,
  	s_id,
  	s_score,
  CASE
  	
  	WHEN s_score BETWEEN 85 
  	AND 100 THEN
  	'level1' 
  WHEN s_score BETWEEN 70 
  AND 85 THEN
  'level2' 
  WHEN s_score BETWEEN 60 
  AND 70 THEN
  'level3' ELSE 'level4' 
  END LEVEL 
  FROM
  	score 
  	) t 
  GROUP BY
  	t.c_id 
  ) p ON course.c_id = p.c_id
  

3、其他判断函数if、ifnull、coalesce

• if

  • 功能：用于实现条件判断，基于条件判断的结果返回不同的值，用于单条件场景

  • 语法：if(判断条件，条件成立返回的结果，条件不成立返回的结果)

  • 示例1：

  • -- 对成绩表，如果成绩大于等于60分显示通过，否则显示未通过
    select *,
           if(s_score>=60,'通过','未通过') as is_pass
    from score;

  • 示例2：多条件嵌套查询

  • -- 对于成绩表，如果成绩>=60为及格，中等为：70-80，优良为：80-90，优秀为：>=90
    
    select *,
           if(s_score<60,'不合格',if(s_score<70,'及格',if(s_score<80,'中等',if(s_score<90,'良好','优秀')))) as sc
    from score;

• ifnull

  • 功能：用于判断第一个参数是否为null，如果为null则返回第二个参数，如果不为null则返回第一个参数【返回参数中第一个不为null的值】

  • 语法：ifnull(参数1，参数2)

  • 示例：

  • -- 查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况
    SELECT
    	t.id,
    	AVG( t.score ) avgscore 
    FROM
    	(
    SELECT
    	st.s_id id,
    	IFNULL( sc.s_score, 0 ) score 
    FROM
    	student st
    	LEFT JOIN score sc ON st.s_id = sc.s_id 
    	) t 
    GROUP BY
    	t.id 
    HAVING
    	avgscore < 60
    

  • coalesce

    • 功能：返回参数列表中第一个非空的值

    • 语法：coalesce（参数1，参数2，参数3……参数N）

    • 示例：

    • 预处理：

    • -- 先查询出所有学生的姓名、考试科目和成绩，如果该学生没有考试该科目，则使用null值
      with t1 as (
          select distinct s_id
          from score
      )
      select t1.s_id,
             c_name,
             s_score
      from t1
      join course
      left join score s on t1.s_id = s.s_id and Course.c_id = s.c_id
      ;

    • 使用coalesce

    • -- 对上述查询结果进行处理，如果成绩为null值，则使用数字0填充
      with t2 as (
      with t1 as (
          select distinct s_id
          from score
      )
      select t1.s_id as sid,
             c_name as cname,
             s_score as sscore
      from t1
      join course
      left join score s on t1.s_id = s.s_id and Course.c_id = s.c_id
      )
      select sid,
             cname,
             coalesce(sscore,0) as ssscore
      from t2
      ;

4、类型转换函数：cast

• cast

  • 功能：将某一数据的类型进行转换

  • 语法：cast( 列 as 新的类型)

  • 示例：

  • select cast('5' as UNSIGNED) - 1;
    select '5' - 1;
    select cast('2023-01-01 15:31:27' as date );
    select cast('2023-01-01 15:31:27' as time );
    select cast('2023-01-01' as datetime);

value	describe
DATE	将value转换成'YYYY-MM-DD'格式
DATETIME	将value转换成'YYYY-MM-DD HH: MM: SS'格式
TIME	将value转换成'HH: MM: SS'格式
CHAR	将value转换成CHAR(固定长度的字符串)格式
SIGNED	将value转换成INT(有符号的整数)格式
UNSIGNED	将value转换成INT(无符号的整数)格式
DECIMAL	将value转换成FLOAT(浮点数)格式
BINARY	将value转换成二进制格式