精华内容
下载资源
问答
  • mysql中的行列转换
    2021-12-07 09:30:42

    mysql中的行列转换

    案例:1
    在这里插入图片描述
    建表语句

    CREATE TABLE `doc` (
    	`id` INT(11) NOT NULL AUTO_INCREMENT,
    	`doc_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    	`doc_class_id` INT(11) NULL DEFAULT NULL,
    	PRIMARY KEY (`id`) USING BTREE
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    ;
    
    CREATE TABLE `doc_class` (
    	`doc_class_id` INT(11) NOT NULL AUTO_INCREMENT,
    	`doc_class_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    	PRIMARY KEY (`doc_class_id`) USING BTREE
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=4
    ;
    
    -- 1.表关联的写法
    -- 实现步骤
    -- 1.1根据相同的doc_class_id关联两个表
    SELECT *
    FROM doc_class a
    LEFT JOIN doc b
    ON a.doc_class_id = b.doc_class_id;
    +--------------+----------------+------+----------+--------------+
    | doc_class_id | doc_class_name | id   | doc_name | doc_class_id |
    +--------------+----------------+------+----------+--------------+
    |            1 | 魔幻类         |    1 | 西游记   |            1 |
    |            2 | 言情类         |    2 | 红楼梦   |            2 |
    |            1 | 魔幻类         |    3 | 寻秦记   |            1 |
    |            2 | 言情类         |    4 | 东游记   |            2 |
    |            3 | 历史类         |    5 | 史记     |            3 |
    +--------------+----------------+------+----------+--------------+
    -- 1.2把doc_class_id统计一下,然后再跟doc_class类进行拼接。group by 一定要使用到聚合函数
    SELECT  a.doc_class_name AS '文献数量' ,count(a.doc_class_id) AS '文献数量'
    FROM doc_class a
    LEFT JOIN doc b
    ON a.doc_class_id = b.doc_class_id
    GROUP BY a.doc_class_id;
    +----------+----------+
    | 文献数量 | 文献数量 |
    +----------+----------+
    | 魔幻类   |        2 |
    | 言情类   |        2 |
    | 历史类   |        1 |
    +----------+----------+
    
    --2.子查询
    --2.1确定要一个主表,一个字段
    SELECT doc_class_name
    FROM doc_class
    +----------------+
    | doc_class_name |
    +----------------+
    | 魔幻类         |
    | 言情类         |
    | 历史类         |
    +----------------+
    --2.2再确定其他的字段,其他的字段用子查询,
    -- 先运行查询字段一,当子查询中有些内容不能查询时,就会阻塞一下,接着执行from后的内容,from后的内容有子查询中的内容,子查询中的内容会递归查询出来,用count(*)/count(1)用来代替count(*)统计查出的结果。接着查询下一个字段。
    SELECT doc_class_name AS '文献类型',
    	(SELECT COUNT(1) FROM doc WHERE doc.doc_class_id=doc_class.doc_class_id) AS '文献数量' 
    FROM doc_class;
    

    案例:2
    在这里插入图片描述

    -- 子查询,一列一列的查询,先判断一个主表,然后其他字段的列用子查询拼接。
    SELECT doc_class_name AS '文献类别',
      (SELECT COUNT(1) FROM doc WHERE doc.doc_class_id=doc_class.doc_class_id AND doc.status=0) AS '未通过' ,
      (SELECT COUNT(1) FROM doc WHERE doc.doc_class_id=doc_class.doc_class_id AND doc.status=1) AS '未审核' ,
      (SELECT COUNT(1) FROM doc WHERE doc.doc_class_id=doc_class.doc_class_id AND doc.status=2) AS '已通过' 
    FROM doc_class
    -- 联合查询。
    -- 1.构造字段,先标记审核的状态。状态对应的话就是1,不对应就是0
    SELECT doc_class.doc_class_name,doc.doc_name,STATUS,
    case when doc.status=0 then 1 ELSE 0 end 未通过 ,
    case when doc.status=1 then 1 ELSE 0 end 未审核,
    case when doc.status=2 then 1 ELSE 0 end 已通过 
    FROM doc 
    RIGHT JOIN doc_class
    ON doc_class.doc_class_id = doc.doc_class_id;
    +----------------+----------+--------+--------+--------+--------+
    | doc_class_name | doc_name | STATUS | 未通过 | 未审核 | 已通过 |
    +----------------+----------+--------+--------+--------+--------+
    | 魔幻类         | 西游记   |      0 |      1 |      0 |      0 |
    | 言情类         | 红楼梦   |      1 |      0 |      1 |      0 |
    | 魔幻类         | 寻秦记   |      1 |      0 |      1 |      0 |
    | 言情类         | 东游记   |      2 |      0 |      0 |      1 |
    | 历史类         | 史记     |      2 |      0 |      0 |      1 |
    | 言情类         | 琅琊榜   |      1 |      0 |      1 |      0 |
    +----------------+----------+--------+--------+--------+--------+
    --2.把上面的一个表当成子表,进行联合查询。
    SELECT doc_class_name,SUM(wtg) 未通过,SUM(wsh) 未审核,SUM(ytg) 已通过
    FROM(SELECT doc_class.doc_class_name,STATUS,
    case when doc.status=0 then 1 ELSE 0 END wtg ,
    case when doc.status=1 then 1 ELSE 0 END  wsh,
    case when doc.status=2 then 1 ELSE 0 end ytg 
    FROM doc 
    RIGHT JOIN doc_class
    ON doc_class.doc_class_id = doc.doc_class_id) b
    GROUP BY doc_class_name
    

    总结:列变横用子查询
    横变竖 案例3:
    在这里插入图片描述

    输出数据
    english 68
    chinese 69
    history 70

    -- 首先构造出输出的两列
    SELECT '' course,0 scoure FROM DUAL 
    -- 横着的变成竖着的一定是一个特例,每次只查一个
    SELECT '英语' course,english scoure FROM course_score WHERE sid = 1
    -- 将结果联合起来
    SELECT '英语' course,english scoure FROM course_score WHERE sid = 1
    union
    SELECT '语文' course,chinese scoure FROM course_score WHERE sid = 1
    union
    SELECT '历史' course,history scoure FROM course_score WHERE sid = 1
    
    更多相关内容
  • 行列转换实例 表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...
  • 一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 1 A...
  • 主要介绍了Oracle实现行列转换的方法,结合实例形式分析了Oracle针对固定列、不定列、列数不固定等情况下的行列转换操作技巧,需要的朋友可以参考下
  • 行列转换

    2019-07-13 02:14:31
    NULL 博文链接:https://cenyf.iteye.com/blog/728310
  • mysql行列转换

    2018-03-02 14:07:03
    java语言 数据库 mysql数据库 sql行列转换的例子 详解
  • Excel行列转换工具

    2019-03-29 11:55:29
    在excel中对行转换成列,也可将列转换成行。很方便的一键式工具。
  • 主要介绍了C#中DataTable实现行列转换的方法,实例分析了C#操作DataTable的相关技巧,非常具有实用价值,需要的朋友可以参考下
  • 行列转换,c++代码

    2020-08-28 17:54:30
    行列转换问题,不要积分
  • 主要介绍了vue实现行列转换的一种方法,本文给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
  • 主要介绍了sql server通过pivot对数据进行行列转换的方法,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
  • 最近突然玩起了sql语句,想着想着便给自己出了一道题目:“行列转换”。起初瞎折腾了不少时间也上网参考了一些博文,不过大多数是采用oracle数据库当中的一些便捷函数进行处理,比如”pivot”。那么,在Mysql环境下...
  • sql 普通行列转换

    2020-09-11 09:48:52
    说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。
  • 1. 列转行 2. 行转列 3. 多列转换成字符串 4. 多行转换成字符串 5. 字符串转换成多列 6. 字符串转换成多行
  • mysql 查询行列转换

    2019-07-29 01:43:00
    NULL 博文链接:https://chenyuxiaoxiao.iteye.com/blog/831674
  • EXCEL 行列转换工具

    2021-03-04 10:52:59
    EXCEL 行列转换工具
  • /*创建数据库*/ CREATE DATABASE tmp go USE tmp go /*创建数据库测试表*/ CREATE TABLE [Scores] ( [ID] INT IDENTITY(1, 1) PRIMARY KEY , [Student] VARCHAR(20) , [Subject] VARCHAR(30) , [Score] ...
  • 行列转换工具

    2018-11-14 08:13:35
    行列转换工具
  • 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函数

    展开全文
  • 行列互换工具毫秒级_V2.2.2行列转换
  • NULL 博文链接:https://hb-keepmoving.iteye.com/blog/803348
  • Oracle行列转换

    2012-09-08 08:00:40
    Oracle行列转换,一个挺经典的例子,值得学习和思考……
  • MySQL行列转换

    2021-01-18 18:45:33
    比如一张表在数据库中是这样的:图1但是,需要的结果可能是这样:图2这个时候就得行列转换了。1.行转列的几种方法1.1 case... when ... then ... else ... endselectuname,uid, -- 正常查询的字段sum(casewhen ...

    实际应用中,会遇到需要把表的某些行转换成列,或者把列转换成行的情况。比如一张表在数据库中是这样的:

    88a815a7a73e57c5c4b460bb48c6970d.png 图1

    但是,需要的结果可能是这样:

    4cb1d92d47d5b89407ca797a1dcb2bb6.png 图2

    这个时候就得行列转换了。

    1.行转列的几种方法

    1.1 case ...  when  ... then ... else ... end

    selectuname,uid, -- 正常查询的字段sum(case

    when course ='英语' thenscore -- 需要转换的字段

    else 0end) '英语',sum(case

    when course= '物理' thenscore

    else 0end) '物理',sum(case

    when course='化学' thenscore

    else 0end) '化学'

    fromcoursegroup by uid

    另一种写法:

    casecoursewhen '化学' thenscoreelse 0

    end

    另外若省略‘else 0‘,则没有该课程的同学的分数会填充为null; sum替换成max结果一样。

    1.2  if (`字段名1`=‘字段值’,,)

    selectuname,uid,sum(if(`course`='英语',score,0)) '英语',sum(if(`course`='物理',score,0)) '物理',sum(if(`course`='化学',score,0)) '化学'

    fromcoursegroup by uname

    貌似比第一种方法简洁一些,所以下面的扩展是基于这种方法的~

    以上两种转换方法结果相同,如图2。另外实际应用中还可能需要有总计的结果,如图3.

    9b76f7cc4a1da8c23d00ab3538ddc152.png 图3

    total这一列简单,直接在之前的查询基础上加一个sum(score) 'total'即可;Total这一行则可以看成之前的查询不加group by而聚合成一行。因此可以看做是两个表组合到一起如图4和图5:

    7b5b5b10de591273e5e0b9904774295a.png 图4

    92b245818b36e85a521b5c378d57fed0.png 图5

    1.3  if (`字段名1`=‘字段值’,,) + union

    把两个查询结果拼接到一起就是图3的样子了,代码如下:

    selectuid,uname,sum(if(`course`='英语',score,0)) '英语',sum(if(`course`='物理',score,0)) '物理',sum(if(`course`='化学',score,0)) '化学',sum(score) 'total'

    fromcoursegroup byunameunion

    select 'Total',null,sum(if(`course`='英语',score,0)) '英语',sum(if(`course`='物理',score,0)) '物理',sum(if(`course`='化学',score,0)) '化学',sum(score) 'total'

    from course

    1.4  if (`字段名1`=‘字段值’,,) + IFNULL()+with rollup

    这种方法效果同1.3,

    select ifnull(uid,'Total') uid, uname,sum(if(`course`='英语',score,0)) '英语',sum(if(`course`='物理',score,0)) '物理',sum(if(`course`='化学',score,0)) '化学',sum(score) 'total'

    fromcoursegroup byuidwith ROLLUP

    比1.3简洁一些,效率应该也高一点。with rollup和group by配套使用,会在已有的查询结果上再多出一行,对结果再聚合成一行,即图5的那一行,若不是数字类型,则返回最下面一行的数据,最后一行分组的字段会显示null,因此在配合ifnull()就可以了。

    cdcdbacf7844cec7e4fd006383eb0679.png

    有瑕疵,想把它变成 null,有待完善。

    2.列转行

    列转行刚好和行转列情况相反,即:

    数据库中存储的是这样

    4cb1d92d47d5b89407ca797a1dcb2bb6.png 图2

    而我们需要这样的结果

    88a815a7a73e57c5c4b460bb48c6970d.png 图1

    没有在创建新表,暂且把这个视图当成新表吧

    create view rtc as

    select ifnull(uid,'Total') uid,uname,sum(if(`course`='英语',score,0)) '英语',sum(if(`course`='物理',score,0)) '物理',sum(if(`course`='化学',score,0)) '化学',sum(score) 'total'

    fromcoursegroup byuidwithROLLUP

    -- 下面是列转行代码select uid,uname,'英语' course,英语 score from rtc where uid <>'Total' and 英语>0

    union all select uid,uname,'物理' ,物理 from rtc where uid <>'Total' and 物理>0

    union all select uid,uname,'化学' ,化学 from rtc where uid <>'Total' and 化学>0

    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 58,119
精华内容 23,247
关键字:

行列转换

友情链接: 弹幕库开源.zip