精华内容
下载资源
问答
  • SQL实战篇:SQL行列转换及真题
    2022-02-26 18:48:01

    对于数据分析师来说,日常进行SQL分析和制作报表的过程中,经常会遇到通过SQL进行行列转换的需求,本节内容对常见的行列转换进行了总结,常见的行列转换包括以下四种情况:

    1. 列转行
    2. 行转列
    3. 列转换成字符串
    4. 字符串转换成列

    下面内容将具体进行案例讲解。

    列转行

    首先,创建一张表学生表t_student。

    DROP TABLE IF EXISTS `t_student`;
    CREATE TABLE `t_student` (
      `id` int
    更多相关内容
  • 一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 1 A...
  • SQL行列转换

    2022-04-09 09:24:31
    本文主要介绍SQL常见的行列转换

    常见的行列转换包括以下四种情况:

    1.列转行

    2.行转列

    3.列转换成字符串

    4.字符串转换成列

    1.列转行

    导入数据

    DROP TABLE IF EXISTS `t_student`;
    CREATE TABLE `t_student` (
      `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
      `name` varchar(50) DEFAULT NULL COMMENT '姓名',
      `course` varchar(50) DEFAULT NULL COMMENT '课程',
      `score` int(3) DEFAULT NULL COMMENT '成绩',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
    
    INSERT INTO `t_student` VALUES (1,'张三', '语文', 95);
    INSERT INTO `t_student` VALUES (2,'李四', '语文', 99);
    INSERT INTO `t_student` VALUES (3,'王五', '语文', 80);
    INSERT INTO `t_student` VALUES (4,'张三', '数学', 86);
    INSERT INTO `t_student` VALUES (5,'李四', '数学', 96);
    INSERT INTO `t_student` VALUES (6,'王五', '数学', 81);
    INSERT INTO `t_student` VALUES (7,'张三', '英语', 78);
    INSERT INTO `t_student` VALUES (8,'李四', '英语', 88);
    INSERT INTO `t_student` VALUES (9,'王五', '英语', 87);
    INSERT INTO `t_student` VALUES (10,'张三', '历史', 98);
    INSERT INTO `t_student` VALUES (11,'李四', '历史', 85);
    INSERT INTO `t_student` VALUES (12,'王五', '历史', 89);

    t_student表 (学生成绩表)

    1.1 MAX(CASE WEHN)方法

    SELECT name as '姓名',
           MAX(CASE WHEN course = '语文' THEN score END) AS '语文',
           MAX(CASE WHEN course = '数学' THEN score END) AS '数学',
           MAX(CASE WHEN course = '英语' THEN score END) AS '英语',
           MAX(CASE WHEN course = '历史' THEN score END) AS '历史'
    FROM t_student
    GROUP BY name;

    结果展示:

    涉及知识点CASE表达式 | 聚合函数

    1.2 SUM(IF(条件,列值,0))

    SELECT name as '姓名',
           SUM(IF(course = '语文',score,0)) AS '语文',
           SUM(IF(course = '数学',score,0)) AS '数学',
           SUM(IF(course = '英语',score,0)) AS '英语',
           SUM(IF(course = '历史',score,0)) AS '历史'
    FROM t_student
    GROUP BY name;

    结果展示:

    涉及知识点: IF函数

    2.行转列

    导入数据

    DROP TABLE IF EXISTS `t_course`;
    CREATE TABLE `t_course` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
      `chinese` double DEFAULT NULL COMMENT '语文成绩',
      `math` double DEFAULT NULL COMMENT '数学成绩',
      `english` double DEFAULT NULL COMMENT '英语成绩',
      `history` double DEFAULT NULL COMMENT '历史成绩',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    INSERT INTO t_course VALUES ('1', '张三', '95', '86', '78', '98');
    INSERT INTO t_course VALUES ('2', '李四', '99', '96', '88', '85');
    INSERT INTO t_course VALUES ('3', '王五', '80', '81', '87', '89');

    t_course表

    行转列的过程, 其实就是列转行的逆过程

    -- 列转行:通过UNION或UNION ALL实现
    SELECT user_name,'语文' AS course,chinese AS score FROM t_course
    UNION ALL
    SELECT user_name,'数学' AS course,math AS score FROM t_course
    UNION ALL
    SELECT user_name,'英语' AS course,english AS score FROM t_course
    UNION ALL
    SELECT user_name,'政治' AS course,history AS score FROM t_course
    ORDER BY user_name;

    部分结果展示:

    涉及知识点: 组合查询

    UNION 与 UNION ALL的区别:

    1.对重复结果的处理: UNION会去掉重复记录,UNION ALL不会

    2.对排序的处理: UNION会排序,UNION ALL只是简单地将两个结果集合并

    3.效率方面的区别: 因为UNION会做去重和排序处理,因此效率比UNION ALL慢很多

    3.列转换成字符串

    在某些场景下,我们可能会对单列或者多列转换成字符串,实现这个需求需要使用到 GROUP_CONCAT函数

    语法格式

    GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])

    导入数据

    DROP TABLE IF EXISTS `t_student`;
    CREATE TABLE `t_student` (
      `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
      `name` varchar(50) DEFAULT NULL COMMENT '姓名',
      `course` varchar(50) DEFAULT NULL COMMENT '课程',
      `score` int(3) DEFAULT NULL COMMENT '成绩',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
    
    INSERT INTO `t_student` VALUES (1,'张三', '语文', 95);
    INSERT INTO `t_student` VALUES (2,'李四', '语文', 99);
    INSERT INTO `t_student` VALUES (3,'王五', '语文', 80);
    INSERT INTO `t_student` VALUES (4,'张三', '数学', 86);
    INSERT INTO `t_student` VALUES (5,'李四', '数学', 96);
    INSERT INTO `t_student` VALUES (6,'王五', '数学', 81);
    INSERT INTO `t_student` VALUES (7,'张三', '英语', 78);
    INSERT INTO `t_student` VALUES (8,'李四', '英语', 88);
    INSERT INTO `t_student` VALUES (9,'王五', '英语', 87);
    INSERT INTO `t_student` VALUES (10,'张三', '历史', 98);
    INSERT INTO `t_student` VALUES (11,'李四', '历史', 85);
    INSERT INTO `t_student` VALUES (12,'王五', '历史', 89);

    t_student表 (学生成绩表)

    问题: 实现t_student表中课程和成绩拼接为一个字符串的功能

    SELECT name, GROUP_CONCAT(course, ":", score) AS '课程:成绩'
    FROM t_student
    GROUP BY name;

    结果展示:

    涉及知识点: GROUP_CONCAT函数

    4.字符串转换成列

    在某些场景下,我们需要把某一列的字符串转成多列

    导入数据

    t_user_order表 (用户订单表)

    DROP TABLE IF EXISTS `t_user_order`;
    CREATE TABLE `t_user_order` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
      `user_id` varchar(50) DEFAULT NULL COMMENT '用户 id',
      `order_id` varchar(100) DEFAULT NULL COMMENT '订单 ids',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    INSERT INTO t_user_order VALUES ('1', 'user1', '1,3,5,19,20');
    INSERT INTO t_user_order VALUES ('2', 'user2', '2,4,6,8,30,50');
    INSERT INTO t_user_order VALUES ('3', 'user3', '11,15,29,31,33');

    结果展示:

    从上表可以看出用户ID (user_id)和订单ID (order_id)之间的关系是一对多关系,用户ID对应的订单 ID是一个字符串

    问题:  将order_id中的字符串转换成列

    思路: 利用help_topic表把以逗号分隔的字符串转换成行

    -- 字符串转换成列: 利用SUBSTRING_INDEX和mysql.help_topic实现
    SELECT a.user_id,
           SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_id, ',', b.help_topic_id + 1 ), ',',- 1 )AS order_id
    FROM t_user_order AS a
    LEFT JOIN mysql.help_topic AS b 
    ON b.help_topic_id < (length(a.order_id) - length(REPLACE(a.order_id, ',', '' )) + 1);

    部分结果展示:

    涉及知识点: SUBSTRING函数 | SUBSTRING_INDEX函数

    展开全文
  • 行列转换实例 表ttt有三个字段 seq --序列 jcxm --检查项目 zhi --值 数据分别如下: seq jcxm zhi ------- -------- -------- 11 1 0.50 11 2 0.21 11 3 0.25 12 1 0.24 12 2...
  • oracle sql 行列转换

    2011-09-26 17:15:29
    行列转换,sys_connect_by_path,row_number等函数的用法
  • 1. 列转行 2. 行转列 3. 多列转换成字符串 4. 多行转换成字符串 5. 字符串转换成多列 6. 字符串转换成多行
  • sum函数不止可以求和,还可以行列转换

    sum函数不止可以求和,还可以行列转换


    表数据(一)

    在这里插入图片描述

    一、行列转换(一)

    写法一:

    select date_format(game_date, '%Y-%m-%d') 时间,
    		sum(if(Win_Lose='胜',1,0)),
    		sum(if(Win_Lose='负',1,0))from jg
    group by game_date;
    

    在这里插入图片描述

    写法二:

    SELECT date_format(game_date, '%Y-%m-%d') AS 时间
    	, SUM(if(Win_Lose = '胜', 1, 0)) AS, SUM(if(Win_Lose = '负', 1, 0)) ASFROM jg
    GROUP BY game_date;
    

    在这里插入图片描述
    执行原理:1.先FROM jg
    2.GROUP BY game_date
    3.SQL聚集函数:SUM()

    二、行列转换(二)

    表数据:
    在这里插入图片描述
    User ID(用户ID)、Customer Service ID(客服ID)、Evaluation ID(评价ID)、Evaluation category(评价类别)、Rating stars(评价星级)

    行列转化,把评价类别Evaluation category转化为列,最后找到符合物流=5,服务=5,质量=5的评价id
    在这里插入图片描述

    SELECT User_ID, Customer_Service_ID
    	, SUM(if(Rating_stars = '物流', 5, NULL)) AS 物流
    	, SUM(if(Rating_stars = '服务', 5, NULL)) AS 服务
    	, SUM(if(Rating_stars = '质量', 5, NULL)) AS 质量
    FROM Customer_Service_data
    GROUP BY User_ID, Customer_Service_ID;
    
    展开全文
  • sql行列转换case when

    2021-07-31 11:52:44
    1.每个通告的已报名、已确认、已通过人数 SELECT res.*, count(DISTINCT case when u.user_status = 'YQR' then u.pair_id end) yqr, count(DISTINCT case when u.user_status = 'YBM' then u.pair_id end) ybm, ...

    1.每个通告的已报名、已确认、已通过人数

    SELECT res.*,
                         count(DISTINCT case when u.user_status = 'YQR' then u.pair_id end) yqr,
                         count(DISTINCT case when u.user_status = 'YBM' then u.pair_id end) ybm,
                         count(DISTINCT case when u.user_status = 'YTG' then u.pair_id end) ytg
                  from (SELECT title, act_id, DATE_FORMAT(create_time, '%Y-%m-%d') create_time
                        FROM activity
                       ) res
                           LEFT JOIN activity_user u on res.act_id = u.act_id
                  GROUP BY res.act_id, u.user_status
    

    2.运行结果
    在这里插入图片描述

    展开全文
  • sql 行列转换

    2018-09-10 13:05:04
    一、 CASE WHEN 语句在DB2, ORACLE, SQL SERVER, SYBASE,MYSQL等数据库都受到支持,是标准的SQL语句. --1 SELECT student, max(yw) as '语文', max(yu) as '英语' FROM( SELECT student, CASE subject WHEN '...
  • sql 普通行列转换

    2020-09-11 09:48:52
    说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。
  • sql行列转换、一个字段包含另一个字段.sql
  • Flink Table & SQL 行列转换

    千次阅读 2021-12-12 15:53:06
    SQL 中,如何将一个数组(Array)或对象(Map)展开。 测试数据 用datagen连接器构造测试数据。DDL如下: CREATE TABLE source_table ( userId INT, eventTime as '2021-10-01 08:08:08', eventType as 'click', ...
  • sql行列转换

    2020-04-18 13:08:02
    sql行列转换列转行行转列 列转行 列转行:sum(case when then else end) 新列名, 或 max() 新列名。二者结果一样(原因见下) 题目原表如下: #记得when之后的引用加‘’ select name, sum(case subject when '...
  • sql行列转换[借鉴].pdf

    2021-10-12 00:43:12
    sql行列转换[借鉴].pdf
  • hive sql 行列转换

    2018-11-08 17:14:00
    -- 对一张大表的每一行,后面加多种label值 -- 其实就是笛卡尔积,举例 -- SELECT * FROM dev.dev_jiadian_user_yuge_temp -- CROSS JOIN -- ( -- SELECT 0 AS label -- UNION ALL -- SELECT 1 AS label ...
  • /*创建数据库*/ CREATE DATABASE tmp go USE tmp go /*创建数据库测试表*/ CREATE TABLE [Scores] ( [ID] INT IDENTITY(1, 1) PRIMARY KEY , [Student] VARCHAR(20) , [Subject] VARCHAR(30) , [Score] ...
  • SQL行列转换 Pivot UnPivot
  • sql server 行列转换

    2011-12-30 14:40:37
    sql server 行列转换例子,两步搞掂。
  • Hive sql 行列转换(行转列,列转行)

    千次阅读 2021-09-22 20:08:11
    在Hive sql应用中会遇到“行转列”和“列转行”的场景,下面介绍其基本使用语法。 1.行转列: 关键字:collect_set() / collect_list()、concat_ws() 1)collect_set()/collect_list():collect_set( )函数只接受...
  • SQL行列转换

    2021-07-13 16:33:02
    比较基础的方法,特点就是好理解记忆。 现在有两张表 纵表 name | subject | score -------+---------+------- Kevin | Math | 98 Kevin | Physics | 97 Kevin | Biology | 100 ...name | math | ph...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 15,169
精华内容 6,067
关键字:

sql行列转换

友情链接: src.rar