mysql之常用函数，行转列等相关运用

一.相关常用函数的使用

提示：网上已经很多了，包括各个博主，官网资料等，此处不再赘述！
此处引用: mysql常用函数
此处引用资料: mysql常用函数解析
此处引用资料: mysql常用函数解析
此处参考资料: mysql行转列，列转行

大家可收集资料学习

---

二.多字段列转行：

1.先解读函数及思路

1.使用函数：

字符串函数：
CONCAT：字符串拼接，CONCAT(str1,str2,…)
GROUP_CONCAT(expr)：该函数将非空列值按照分组条件进行合并并最终返回

row_number() over(partition by 分组列 order by 排序列 desc)：分组排序功能

条件控制函数：CASE WHEN … THEN …ELSE… END
扩展：

IF(expr1,expr2,expr3)：expr1为true，返回expr2，否则返回expr3。
IFNULL(expr1,expr2)：如果expr1为null，就返回expr2，否则返回expr1。
NULLIF(expr1,expr2)：如果expr1和expr2相等，就返回null 否则返回expr1。
CASE WHEN THEN ELSE END;

聚合函数：MAX()，SUM()：此处运用要看具体行转列字段的数据类型,比如字符串，整数据，浮点数，金额等等

2.思路

分为两种情况：
1.直接将一些字段定死
2.动态拼接字段
使用的场景需要根据需求来，比如课程等，高中基本固定，大学会根据不同专业有不同的课程（就要使用动态的）

即将生成的动态字段包括条件分支等，使用字符串拼接起来，然后传入主SQL中作为查询的字段。
存储过程逻辑也是一样，将动态SQL进行组合查询而已。

2.SQL脚本准备

-- 创建示例数据表
CREATE TABLE teacher_assignments (
    teacher_name VARCHAR(50),
    org_name VARCHAR(50),
    position VARCHAR(50)
);

-- 插入示例数据
INSERT INTO teacher_assignments (teacher_name, org_name, position)
VALUES
    ('曹塽', '规划研究所', '副主任规划师'),
    ('曹塽', '信息中心', '应用工程师'),
    -- 其他数据行
    ('其他教师', '其他组织', '其他职位');

select * from teacher_assignments

3.方式一：存储过程

SET @sql = NULL;
SELECT
  GROUP_CONCAT(
    DISTINCT
    CONCAT(
     --rn 拼接到字段的org1,org2
      'MAX(CASE WHEN rn = ', rn, ' THEN org_name ELSE ''/'' END) AS org', rn, ',', 
      'MAX(CASE WHEN rn = ', rn, ' THEN position ELSE ''/'' END) AS position', rn
    )
  ) INTO @sql
FROM (
    SELECT DISTINCT 
           ROW_NUMBER() OVER(PARTITION BY teacher_name ORDER BY org_name) AS rn
    FROM teacher_assignments
) AS Ranks;

SET @sql = CONCAT('SELECT teacher_name, ', @sql, ' FROM (
    SELECT teacher_name, org_name, position,
           ROW_NUMBER() OVER(PARTITION BY teacher_name ORDER BY org_name) AS rn
    FROM teacher_assignments
) AS RankedData
GROUP BY teacher_name;');

-- 执行动态生成的SQL
PREPARE dynamic_sql FROM @sql;
EXECUTE dynamic_sql;
DEALLOCATE PREPARE dynamic_sql;

查询结果：

4.方式二：纯单查询SQL

1.先获取动态字段（字段名可根据需求自定义）

比如课程：MAX(CASE WHEN kcname= ', 名称具体字符串，ID，编码等, ’ THEN 名称END) AS 课程名称（根据需求调整）

-- 方式一		
SELECT
  GROUP_CONCAT(
    DISTINCT
    CONCAT(
      'MAX(CASE WHEN rn = ', rn, ' THEN org_name END) AS org', rn, ',',
      'MAX(CASE WHEN rn = ', rn, ' THEN position END) AS position', rn
    )
  ) 
FROM (
    SELECT DISTINCT 
           ROW_NUMBER() OVER(PARTITION BY teacher_name ORDER BY org_name) AS rn
    FROM teacher_assignments
) AS Ranks;

-- 方式二
SELECT
  GROUP_CONCAT(
    DISTINCT
    CONCAT(
      'MAX(IF(rn = ', rn, ',org_name,''-'')) AS org', rn, ',',
      'MAX(IF(rn = ', rn, ',position,''-'')) AS position', rn
    )
  ) 
FROM (
    SELECT DISTINCT 
           ROW_NUMBER() OVER(PARTITION BY teacher_name ORDER BY org_name) AS rn
    FROM teacher_assignments
) AS Ranks;

如图：

2.将1中的动态字段拼接字符串，放入到主SQL中（即在代码中查询2次，将第一次的结果作为参数，传入的SQL2中作为动态字段，后续更新至案例）

-- 方式一
SELECT 
    teacher_name,
    MAX(CASE WHEN rn = 1 THEN org_name ELSE '-' END) AS org1,-- 此场景可自定义具体的值
    MAX(CASE WHEN rn = 1 THEN position ELSE '-' END) AS position1,
    MAX(CASE WHEN rn = 2 THEN org_name ELSE '-' END) AS org2,
    MAX(CASE WHEN rn = 2 THEN position ELSE '-' END) AS position2
FROM (
    SELECT 
        teacher_name,
        org_name,
        position,
        ROW_NUMBER() OVER (PARTITION BY teacher_name ORDER BY org_name, position) AS rn
    FROM teacher_assignments
) AS assignments_pivoted
--WHERE teacher_name = '曹塽'
GROUP BY teacher_name;

-- 方式二
SELECT 
    teacher_name,
   MAX(IF(rn = 1,org_name,'')) AS org1,MAX(IF(rn = 1,position,'-')) AS position1,MAX(IF(rn = 2,org_name,'-')) AS org2,MAX(IF(rn = 2,position,'-')) AS position2
FROM (
    SELECT 
        teacher_name,
        org_name,
        position,
        ROW_NUMBER() OVER (PARTITION BY teacher_name ORDER BY org_name, position) AS rn
    FROM teacher_assignments
) AS assignments_pivoted
-- WHERE teacher_name = '曹塽'
GROUP BY teacher_name;

查询结果如图：

---

三.Mybatis Plus中以上两种方式的使用案例

注：如果是需要传入相关过滤条件，或者分页，直接在SQL中加语句就行（存储过程的参数传递自行修改）

1.存储过程的调用（直接在你对应的Mapper中加注解，或者用XML的方式也可以）

存储过程的使用带来的好坏，自行查阅

(此处和上文数据不符，只是演示案例，对应着来即可,改的是函数名字和具体的SQL语句罢了) 要体验过程，而不是直接copy，不要当CV侠！

	//p1() 为函数名称	
    @Select("{call p1()}")
    @Options(statementType = StatementType.CALLABLE)
    List<SomeData> getSomeData();

---

2.将动态SQL，传入到SQL（分2次查询）

注：我们不准使用存储过程，就用这种

    String dynamicSql = scoreManageMapper.getDynamicSQl();
    List<SomeData> data = scoreManageMapper.getListData(dynamicSql);

//1.动态字段字符串，XML配置
<select id="getDynamicSQl" resultType="java.lang.String">
        SELECT
            GROUP_CONCAT(DISTINCT
                         CONCAT(
                                 'MAX(CASE WHEN s.courseName = ''',
                                 c.id,
                                 ''' THEN s.score ELSE 0 END) AS ',
                                 REPLACE(c.name, ' ', '_')
                             ))
        from course_manage c
    </select>

//2.传入动态SQL参数，XML配置
<select id="getListData" resultType="com.example.localdemo.response.SomeData">
        select
            cm.className,
            sm.name as studentName,
            #{dynamicSql},  //传入的动态SQL
            SUM(s.score) totalScore,
            RANK() OVER (PARTITION BY cm.className ORDER BY SUM(s.score)) DESC) AS classRank //班级排名
        FROM score_manage s
                 left join student_manage sm on sm.id = s. studentName
                 left join class_manage cm on cm.id = s.onClass
        GROUP BY sm.id,cm.id
    </select>

---

四.扩展

在SQLServer中使用类似（语法，或者有些函数使用不同）

如报表需求：我需要知道某个人，在某年中的人事调动（包括公司，部门，岗位等变动），以及工作天数，请假天数，人员编码，名称，年总天数，人数【公式占比计算】等 字段

注：额外场景补充

---

五.总结

吃了没文化的亏，不就得恶补学习么！

如有不同的见解、看法或者有疑问的地方，欢迎留言共同学习！

Day Day No Bug !

---