精华内容
下载资源
问答
  • MySQL行转列函数

    千次阅读 2019-11-04 09:07:51
    原文链接: ...概述 好久没写SQL语句,今天看到问答中的一个问题,拿来...学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作。数据表里面数据如下图,使用姓名+课程作为联合主键(...

    原文链接:
    http://www.360doc.com/content/18/0525/20/14808334_757019563.shtml
    概述
    好久没写SQL语句,今天看到问答中的一个问题,拿来研究一下。

    问题链接:关于Mysql 的分级输出问题

    情景简介
    学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作列。数据表里面数据如下图,使用姓名+课程作为联合主键(有些需求可能不需要联合主键)。本文以MySQL为基础,其他数据库会有些许语法不同。

    数据库表数据:
    在这里插入图片描述

    处理后的结果(行转列):
    在这里插入图片描述
    在这里插入图片描述
    方法一:

    这里可以使用Max,也可以使用Sum;

    注意第二张图,当有学生的某科成绩缺失的时候,输出结果为Null;

    SELECT  
        SNAME,  
        MAX(  
            CASE CNAME  
            WHEN 'JAVA' THEN  
                SCORE  
            END  
        ) JAVA,  
        MAX(  
            CASE CNAME  
            WHEN 'mysql' THEN  
                SCORE  
            END  
        ) mysql  
    FROM  
        stdscore  
    GROUP BY  
        SNAME;  
    

    可以在第一个Case中加入Else语句解决这个问题:

    SELECT  
        SNAME,  
        MAX(  
            CASE CNAME  
            WHEN 'JAVA' THEN  
                SCORE  
            ELSE  
                0  
            END  
        ) JAVA,  
        MAX(  
            CASE CNAME  
            WHEN 'mysql' THEN  
                SCORE  
            ELSE  
                0  
            END  
        ) mysql  
    FROM  
        stdscore  
    GROUP BY  
        SNAME;  
    

    方法二:

    SELECT DISTINCT  a.sname,  
    (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',  
    (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'  
    FROM stdscore a  
    

    方法三:

    DROP PROCEDURE  
    IF EXISTS sp_score;  
    DELIMITER &&  
      
    CREATE PROCEDURE sp_score ()  
    BEGIN  
        #课程名称  
        DECLARE  
            cname_n VARCHAR (20) ; #所有课程数量  
            DECLARE  
                count INT ; #计数器  
                DECLARE  
                    i INT DEFAULT 0 ; #拼接SQL字符串  
                SET @s = 'SELECT sname' ;  
                SET count = (  
                    SELECT  
                        COUNT(DISTINCT cname)  
                    FROM  
                        stdscore  
                ) ;  
                WHILE i < count DO  
      
      
                SET cname_n = (  
                    SELECT  
                        cname  
                    FROM  
                        stdscore  
                    GROUP BY CNAME   
                    LIMIT i,  
                    1  
                ) ;  
                SET @s = CONCAT(  
                    @s,  
                    ', SUM(CASE cname WHEN ',  
                    '\'',  
                    cname_n,  
                    '\'',  
                    ' THEN score ELSE 0 END)',  
                    ' AS ',  
                    '\'',  
                    cname_n,  
                    '\''  
                ) ;  
                SET i = i + 1 ;  
                END  
                WHILE ;  
                SET @s = CONCAT(  
                    @s,  
                    ' FROM stdscore GROUP BY sname'  
                ) ; #用于调试  
                #SELECT @s;  
                PREPARE stmt  
                FROM  
                    @s ; EXECUTE stmt ;  
                END&&  
      
    CALL sp_score () ;  
    

    处理后的结果(行转列)分级输出:
    在这里插入图片描述
    在这里插入图片描述
    方法一:
    这里可以使用Max,也可以使用Sum;

    注意第二张图,当有学生的某科成绩缺失的时候,输出结果为Null;

    SELECT  
        SNAME,  
        MAX(  
            CASE CNAME  
            WHEN 'JAVA' THEN  
                (  
                    CASE  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN  
                        '优秀'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN  
                        '良好'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN  
                        '普通'  
                    ELSE  
                        '较差'  
                    END  
                )  
            END  
        ) JAVA,  
        MAX(  
            CASE CNAME  
            WHEN 'mysql' THEN  
                (  
                    CASE  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN  
                        '优秀'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN  
                        '良好'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN  
                        '普通'  
                    ELSE  
                        '较差'  
                    END  
                )  
            END  
        ) mysql  
    FROM  
        stdscore  
    GROUP BY  
        SNAME;  
    

    方法二:

    SELECT DISTINCT  a.sname,  
    (SELECT (  
                    CASE  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN  
                        '优秀'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN  
                        '良好'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN  
                        '普通'  
                    ELSE  
                        '较差'  
                    END  
                ) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',  
    (SELECT (  
                    CASE  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN  
                        '优秀'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN  
                        '良好'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN  
                        '普通'  
                    ELSE  
                        '较差'  
                    END  
                ) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'  
    FROM stdscore a  
    

    方法三:

    DROP PROCEDURE  
    IF EXISTS sp_score;  
    DELIMITER &&  
      
    CREATE PROCEDURE sp_score ()  
    BEGIN  
        #课程名称  
        DECLARE  
            cname_n VARCHAR (20) ; #所有课程数量  
            DECLARE  
                count INT ; #计数器  
                DECLARE  
                    i INT DEFAULT 0 ; #拼接SQL字符串  
                SET @s = 'SELECT sname' ;  
                SET count = (  
                    SELECT  
                        COUNT(DISTINCT cname)  
                    FROM  
                        stdscore  
                ) ;  
                WHILE i < count DO  
      
      
                SET cname_n = (  
                    SELECT  
                        cname  
                    FROM  
                        stdscore  
            GROUP BY CNAME   
                    LIMIT i, 1  
                ) ;  
                SET @s = CONCAT(  
                    @s,  
                    ', MAX(CASE cname WHEN ',  
                    '\'',  
                    cname_n,  
                    '\'',  
                    ' THEN (  
                    CASE  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 20 THEN  
                        \'优秀\'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 10 THEN  
                        \'良好\'  
                    WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') >= 0 THEN  
                        \'普通\'  
                    ELSE  
                        \'较差\'  
                    END  
                ) END)',  
                    ' AS ',  
                    '\'',  
                    cname_n,  
                    '\''  
                ) ;  
                SET i = i + 1 ;  
                END  
                WHILE ;  
                SET @s = CONCAT(  
                    @s,  
                    ' FROM stdscore GROUP BY sname'  
                ) ;   
                #用于调试  
                #SELECT @s;  
                PREPARE stmt  
                FROM  
                    @s ; EXECUTE stmt ;  
                END&&  
      
      
    CALL sp_score ();  
    

    几种方法比较分析
    第一种使用了分组,对每个课程分别处理。
    第二种方法使用了表连接。
    第三种使用了存储过程,实际上可以是第一种或第二种方法的动态化,先计算出所有课程的数量,然后对每个分组进行课程查询。这种方法的一个最大的好处是当新增了一门课程时,SQL语句不需要重写。

    小结
    关于行转列和列转行

    这个概念似乎容易弄混,有人把行转列理解为列转行,有人把列转行理解为行转列;

    这里做个定义:

    行转列:把表中特定列(如本文中的:CNAME)的数据去重后做为列名(如查询结果行中的“Java,mysql”,处理后是做为列名输出);

    列转行:可以说是行转列的反转,把表中特定列(如本文处理结果中的列名“JAVA,mysql”)做为每一行数据对应列“CNAME”的值;

    关于效率

    不知道有什么好的生成模拟数据的方法或工具,麻烦小伙伴推荐一下,抽空我做一下对比;

    还有其它更好的方法吗?

    本文使用的几种方法应该都有优化的空间,特别是使用存储过程的话会更加灵活,功能更强大;

    本文的分级只是给出一种思路,分级的方法如果学生的成绩相差较小的话将失去意义;

    如果小伙伴有更好的方法,还请不吝赐教,感激不尽!

    有些需求可能不需要联合主键

    有些需求可能不需要联合主键,因为一门课程可能允许学生考多次,取最好的一次成绩,或者取多次的平均成绩。
    最简单的case when

    SELECT
    	COUNT(*) AS num,
    	(
    		CASE PAY_TYPE
    		WHEN '0' THEN
    			'微信支付'
    		WHEN '1' THEN
    			'支付宝支付'
    		WHEN '2' THEN
    			'无感支付'
    		WHEN '3' THEN
    			'银联'
    		WHEN '4' THEN
    			'白名单'
    		WHEN '5' THEN
    			'月卡'
    		END
    	) AS type
    FROM
    	t_park_order
    GROUP BY
    	PAY_TYPE;
    
    展开全文
  • 关于oracle行转列函数

    千次阅读 2021-01-27 11:47:59
    关于oracle行转列函数LISTAGG()XMLAGG() LISTAGG() 例如: 查出每个职位的所有人名单: 但是如果遇到转为一行后的字段过于太长太长,会报错,如下: 这样只有使用另一种方法查出来转为行的结果类型转为clob ...

    关于oracle行转列函数

    LISTAGG()

    在这里插入图片描述

    例如:

    在这里插入图片描述
    查出每个职位的所有人名单:
    在这里插入图片描述


    但是如果遇到转为一行后的字段过于太长太长,会报错,如下:
    在这里插入图片描述

    在这里插入图片描述
    这样只有使用另一种方法查出来转为行的结果类型转为clob
    LISTAGG函数返回的是一个varchar2类型的数据,最大字节长度为4000
    XMLAGG函数返回的类型为CLOB,最大字节长度为32767


    XMLAGG()

    xmlagg函数

    例如:

    在这里插入图片描述
    查出每个职位的所有人名单:
    在这里插入图片描述
    点开clob每个字段内容看看:在这里插入图片描述

    在这里插入图片描述
    在这里插入图片描述

    在这里插入图片描述

    展开全文
  • oracle中的行转列函数

    万次阅读 2018-07-24 14:44:44
    最近项目需要进行行转,经过上网查资料发现了wmsys.wm_concat和LISTAGG函数,在这分享给大家 wmsys.wm_concat是oracle 10g推出的,用来连接字符串,LISTAGG是oracle 11g推出的,它的作用和wmsys.w...

    转自:https://blog.csdn.net/qq_33157666/article/details/72854801

    一、简单介绍

    最近项目需要进行行转列,经过上网查资料发现了wmsys.wm_concat和LISTAGG函数,在这分享给大家

    wmsys.wm_concat是oracle 10g推出的,用来连接字符串,LISTAGG是oracle 11g推出的,它的作用和wmsys.wm_concat是一样的,但是他不支持LISTAGG。

    二、具体用法

    现在以oracle的emp表为例,现在emp标的结构如下图:

     

     

    如果我们想要查询出每个部门都有哪些员工,如下表格的结构,就需要用wmsys.wm_concat或LISTAGG函数了。

    deptno

    ename

    10

    CLARK,MILLER,KING

    20

    SMITH,FORD,ADAMS,SCOTT,JONES

    30

    ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

    (表格1)

    1、wmsys.wm_concat的用法:

    select deptno,wmsys.wm_concat(ename) ename from emp group by deptno;

    wmsys.wm_concat()中的参数也可以使多个,使用”||”拼接,如下例子:

    select deptno,wmsys.wm_concat(ename || '-' || job) name from emp group by deptno;

    运行的结果如下表格:

    deptno

    ename

    10

    CLARK-MANAGER,MILLER-CLERK,KING-PRESIDENT

     

    20

    SMITH-CLERK,FORD-ANALYST,ADAMS-CLERK,SCOTT-ANALYST,JONES-MANAGER

    30

    ALLEN-SALESMAN,JAMES-CLERK,TURNER-SALESMAN,BLAKE-MANAGER,MARTIN-SALESMAN,WARD-SALESMAN

    (表格2)

      

    2、LISTAGG的用法:

    select deptno,LISTAGG(ename,',') withinGRO(order by ename) from emp group by deptno;

    此条sql的执行结果如表格1.

     

    展开全文
  • PIVOT 提供的语法比一系列复杂的...非透视的&gt;, [第一个透视的] AS &lt;名称&gt;, [第二个透视的] AS &lt;名称&gt;, ... [最后一个透视的] AS &lt;名称&gt;, FROM ...

    PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。

    以下是带批注的 PIVOT 语法:

    SELECT <非透视的列>,
        [第一个透视的列] AS <列名称>,
        [第二个透视的列] AS <列名称>,
        ...
        [最后一个透视的列] AS <列名称>,
    FROM
        (<生成数据的 SELECT 查询>)
        AS <源查询的别名>
    PIVOT
    (
        <聚合函数>(<要聚合的列>)
    FOR
    [<包含要成为列标题的值的列>]
        IN ( [第一个透视的列], [第二个透视的列],
        ... [最后一个透视的列])
    ) AS <透视表的别名>
    <可选的 ORDER BY 子句>;

    下面举个实例,比如要统计亚马逊9月1号一天每个小时下发的订单量,按照传统的写法如下所示:

    select Warehouseid 仓库,
    	   CustomerID 客户,
    	   SUBSTRING(CONVERT(varchar(100),OrderTime,120),0,11) WMS创建时间,
           SUBSTRING(CONVERT(varchar(100), ordertime, 120),12,2) 小时,
           count(OrderNo) 订单量
    from DOC_Order_Header
    where 
    OrderTime>='2018-09-01 00:00:00'
    and  OrderTime<='2018-09-01 23:59:59'
    and CustomerID ='YMX'
    and Warehouseid in('HKBT','FLC')
    group by Warehouseid,CustomerID,SUBSTRING(CONVERT(varchar(100),OrderTime,120),0,11),
    		SUBSTRING(CONVERT(varchar(100), ordertime, 120),12,2)
    order by 仓库,WMS创建时间
    
    

    查询出来的结果为列模式,看起来不方便,如下所示:

    现在使用 PIVOT 函数,把列转成行,看起来直观明了,语法如下所示:

    select *,
    	  [00]+[01]+[02]+[03]+[04]+[05]+[06]+[07]+[08]+[09]+[10]+[11]+
          [12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+[21]+[22]+[23]+[24] as 当天订单总数
    from
    (
    	select Warehouseid 仓库,
    		   CustomerID 客户,
    		   SUBSTRING(CONVERT(varchar(100),OrderTime,120),0,11) WMS创建时间,
    	       SUBSTRING(CONVERT(varchar(100), ordertime, 120),12,2) 小时,OrderNo
    	       from DOC_Order_Header
         where 
         OrderTime>='2018-09-01 00:00:00'
         and  OrderTime<='2018-09-01 23:59:59'
         and CustomerID ='YMX'
         and Warehouseid in('HKBT','FLC')
    )t
    PIVOT 
    (
        count(t.OrderNo) FOR t.小时 IN
        ([00],[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],
         [12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24])    
    )as a
    order by a.仓库,a.WMS创建时间
    

    查询结果如下图所示:

     

    展开全文
  • python 中pd.pivot_table行转列函数使用

    千次阅读 2018-10-25 16:30:46
  • oracle的 listagg() WITHIN GROUP () 行转列函数的使用

    万次阅读 多人点赞 2018-07-11 18:12:25
    如需转载请标明出处 1.使用条件查询 查询部门为20的员工列表 -- 查询部门为20的员工列表 SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20' ; 效果: ...2.使用 listagg() WITHIN GROUP () 将多行...
  • Oracle 行转列函数

    千次阅读 2019-05-29 15:19:29
    pivot (sum(amount) for type in( '001' 截止上月设计申请金额 , '002' 截止上月现场申请金额 )); pivot(聚合函数 for 转的字段 in (字段值 别名));
  • Hive常用函数之行转转行函数

    千次阅读 2020-02-24 20:47:16
    一、行转列函数:CONCAT,CONCAT_WS,COLLECT_SET 在HIVE实际开发过程中,我们会遇到“行转列”和“列转行”的场景。比如: 1、需要取“订单号”对应的所有商品“SKU号”,商品“sku号”放在一列,即从table1查询...
  • oracle 行转 转行函数理解

    千次阅读 2019-12-10 18:14:42
    1、pivot 行转列函数 含义:pivot 聚合函数(字段1) for 字段2 in (值1,值2,值3...) 将字段2中的值1,值2,值3...作为字段名称在对字段1根据聚合函数做聚合(SUM、max、min、avg等)。 例、原表为: 将这个表格...
  • Hive-行转转行,相关函数

    千次阅读 2020-08-03 22:43:06
    1)函数说明(可以是一行转一,多行转一) CONCAT(string A, string B...):返回输入字符串连接后的结果,支持任意个输入字符串; CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 concat()。第一个参数...
  • Sybase15实现 自定义行转列函数

    千次阅读 2016-05-04 10:43:30
    Sybase15实现 自定义行转列函数
  • Hive函数之行转

    万次阅读 2018-08-13 19:43:56
    介绍三种常用函数,空字段赋值,行转,case when, 一、NVL函数 空字段赋值, 语法:nvl(string1,replaceValue),当某值为空时调用, 二、case when 就相当于java中switch case,记得最后一定要加end,else...
  • MYSQL一个字段多个值拼接成一列函数group_concat示例函数相关配置 示例 1.单列多行合并单行单列展示 select group_concat(name) as name from student where classId = 1 执行结果如下图: 2.多列拼接,多列多行值...
  • Oracle 行转 pivot函数基本用法

    万次阅读 多人点赞 2018-09-30 23:28:05
    2018年9月30日22点,眼看着就10月份了,回头看下,8月份就写了一...所以暂时先写个Oracle自带的行转列函数,pivot的基本用法。国庆几天看下有时间的话完善一下动态转列的做法,到时候再另写一篇附链接过来。 一、运...
  • 本文主要介紹PostgreSQL 的行转转行以及字符串切割函数,实际业务中对前两个均有使用,并配有实际例子参考。 1、字符串转行 string_agg,某些地方也称为字符串聚合操作。 如果需要按照一个字符串按照某个...
  • 行转方法 及 pivot函数使用问题

    万次阅读 2017-12-14 11:19:01
    oracle 行转方法   如果有这样的需求,数据库插入的有A表这样的结果,展示要变成B表这样的结果(下边的这个图是摘自别人的,我主要说pivot函数那种方式) 对于这样的需求即需要行转了。 1、第一种...
  • 首先,通过两个表格说明本次要讲解的为什么要使用这两个函数? 图一: 时间 机构号 销售渠道 保费A 保费B 保费C 保费D statdate branch_code mgr longinsA longinsB longinsC ...
  • Oracle转行函数 Listagg() 语法详解及应用实例

    万次阅读 多人点赞 2017-01-03 22:16:54
    工作中用到一段比较复杂的...说简单点,listagg()函数可以实现多记录聚合为一条记录,从而实现数据的压缩、致密化(data densification)。以下内容转载自http://dacoolbaby.iteye.com/blog/1698957,SQL脚本做了...
  • Oracle SQL函数pivot、unpivot转置函数实现行转转行
  • oracle 转行 listagg()函数 详解

    千次阅读 2018-10-24 10:29:10
    listagg() : Oracle的转行函数; 版本要求: 11.2 以上版本. 语法:  listagg(iw.wfl_id,',') within group(order by ir.serno) listagg(列名,' 分割符号') within group(order by 值被拼接的顺序) 分组...
  • 【数据库】HIVE SQL函数之行转

    千次阅读 2019-05-25 22:08:29
    小白之前经常在工作中遇到需要行转的情况,这次就总结一下,之前遇到过的各种情况,及在各种情况下的函数应用。 场景: 现有一个表,表示一个房型在未来60天的是否可订情况,由于房型的数据量是庞大的,可能达到...
  • spark dataFrame 新增一列函数withColumn

    万次阅读 2017-05-30 11:34:02
    往一个dataframe新增某个是很常见的事情。 ...然而这个资料还是不多,很多都需要很多变换。...不过由于这回需要增加的非常简单,倒也没有必要再用UDF函数去修改。 利用withColumn函数就能实现对da
  • PIVOT用于将值旋转为列名(即行转),在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数() FOR in (…) )AS P 完整语法: table_source PIVOT( 聚合函数(value_...
  • [MSSQL]采用pivot函数实现动态行转

    万次阅读 2020-05-31 22:46:12
    在sql 2005以前处理动态行或的时候,通常采用拼接字符串的方法处理,在2005以后新增了pivot函数之后,我可以利用这样函数来处理。 1. 环境要求:2005+ 在日常需求中经常会有行转的事情需求处理,如果不是...
  • Pandas详解二十六之Apply--对行、函数处理

    万次阅读 多人点赞 2018-09-02 08:25:02
    Apply–对行、函数处理 俗话说,工欲善其事,必先利其器。在这里形容apply函数再合适不过了,apply函数,可以说是pandas中自由度最高的函数。不过,是否能发挥其巨大威力,取决于我们的创造力。 在本博文中,...
  • postgresql 自定义聚合函数实现多行数据合并成一
  • oracle 将合并成行的函数 wn_concat()

    千次阅读 2018-02-24 17:00:59
    wn_concat() 在oracle 10g 中返回的是string类型  在oracle 11g 中返回的是clob类型
  • oracle数据库 行转 用decode函数实现

    千次阅读 2017-08-17 13:36:58
    --用decode函数实现行列转换 SELECT NAME 姓名, SUM (DECODE (CLASS, '语文', score, 0)) 语文, SUM (DECODE (CLASS, '数学', score, 0)) 数学, SUM (DECODE (CLASS, '英语', score, 0)) 英语 FROM student ...
  • 多行转字符串 这个比较简单,用||或concat函数可以实现 ? 1 ... select concat(id,username) str from ... 字符串转多 实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式 ...
  • sql使用聚合函数查询多问题

    千次阅读 2017-04-11 21:42:00
    sql使用聚合函数查询多问题

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,291,865
精华内容 516,746
关键字:

列函数