横纵表之间的转换

纵表转横表

SELECT s.name,

CASE s.subject WHEN '语文' THEN s.score ELSE NULL END AS '语文',

CASE s.subject WHEN '数学' THEN s.score ELSE NULL END AS '数学',

CASE s.subject WHEN '英语' THEN s.score ELSE NULL END AS '英语'

FROM sql_1 s;

#分组 SELECT s2.name, SUM(语文) as 语文, SUM(数学) as 数学, SUM(英语) as 英语 from (SELECT s.name,

CASE s.subject WHEN '语文' THEN s.score ELSE NULL END AS 语文,

CASE s.subject WHEN '数学' THEN s.score ELSE NULL END AS 数学,

CASE s.subject WHEN '英语' THEN s.score ELSE NULL END AS 英语

FROM sql_1 s) s2 GROUP BY s2.name;

#优化 SELECT s.name,

SUM(CASE s.subject WHEN '语文' THEN s.score ELSE NULL END) as 语文,

SUM(CASE s.subject WHEN '数学' THEN s.score ELSE NULL END) as 数学,

SUM(CASE s.subject WHEN '英语' THEN s.score ELSE NULL END) as 英语

FROM sql_1 s GROUP BY s.name;

公式：

-- 列转行：公式，把纵表转横表 -- SELECT 分组列,

-- 聚合函数（case 转换列 when 转换列的值1 then 数据列 else ... end ） as 列名1，

-- 聚合函数（case 转换列 when 转换列的值2 then 数据列 else ... end ） as 列名2，

-- 聚合函数（case 转换列 when 转换列的值3 then 数据列 else ... end ） as 列名3 -- .... -- FROM 表名 -- GROUP BY 分组列;

行转列

-- 行转列 SELECT name,'语文' AS subject,语文 as score

FROM sql_3

UNION ALL

SELECT name,'数学' AS subject,数学 as score

FROM sql_3

UNION ALL

SELECT name,'英语' AS subject,英语 as score

FROM sql_3;

行转列公式：

-- 公式 SELECT 非转换列 ,

'新增列的值1' AS 新加列的别名, 转换列1 AS 转换列的别名 FROM 表名

UNION ALL

SELECT 非转换列 ,'新增列的值2' AS 新加列的别名, 转换列2 AS 转换列的别名

FROM 表名

UNION ALL SELECT

非转换列 ,'新增列的值3' AS 新加列的别名, 转换列3 AS 转换列的别名

FROM 表名 ... ORDER BY ... ;