精华内容
下载资源
问答
  • 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 () 将多行...

    如需转载请标明出处

    1.使用条件查询 查询部门为20的员工列表

    -- 查询部门为20的员工列表
    SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20' ;

        效果:

    2.使用  listagg() WITHIN GROUP ()  将多行合并成一行(比较常用)

    SELECT
    	T .DEPTNO,
    	listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names
    FROM
    	SCOTT.EMP T
    WHERE
    	T .DEPTNO = '20'
    GROUP BY
    	T .DEPTNO

        效果:

    3. 使用 listagg() within GROUP () over  将多行记录在一行显示(没有遇到过这种使用场景)

    SELECT
    	T .DEPTNO,
    	listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME)  over(PARTITION BY T .DEPTNO)
    FROM
    	SCOTT.EMP T
    WHERE
    	T .DEPTNO = '20' 

        效果:

            注:使用的表数据是oracle 用户scott下的emp(员工)表

     

    mysql相同效果的实现  https://blog.csdn.net/sinat_36257389/article/details/95052001

    PostgreSQL 相同效果的实现 https://blog.csdn.net/sinat_36257389/article/details/95611686

    展开全文
  • PostgreSQL中的crosstab(行转列函数

    千次阅读 2019-05-27 12:35:43
    PostgreSQL中的crosstab(行转列函数) 行转列是一个非常有用的功能,如果不适用行转列函数,则通用做法是使用 case when 。不过,接下来,介绍一下今天的新主角–crosstab 一、安装扩展 环境:centos7 、pg10(yum ...

    PostgreSQL中的crosstab(行转列函数)

    行转列是一个非常有用的功能,如果不适用行转列函数,则通用做法是使用 case when 。不过,接下来,介绍一下今天的新主角–crosstab

    一、安装扩展

    环境:centos7 、pg10(yum 安装)、扩展组件 tablefunc

    crosstab 需要按照扩展tablefunc

    [root@uzong ~]# su postgres
    bash-4.2$ psql technology postgres
    could not change directory to "/root"
    psql (9.2.24, server 10.8)
    WARNING: psql version 9.2, server version 10.0.
             Some psql features might not work.
    Type "help" for help.
    
    technology=# create extension tablefunc;
    CREATE EXTENSION
    technology=# 
    

    二、 使用入门

    2.1 crosstab

    crosstab(text sql)
    crosstab(text sql, int N)
    

    这crosstab 函数被用来生成pivot 展示,即通过横向而不是下拉展示(行转列)。例如下面这个例子。原本类型如下:

    row1    val11
    row1    val12
    row1    val13
    ...
    row2    val21
    row2    val22
    row2    val23
    ...
    

    但是我们想要的是下面这种格式:

    row1    val11   val12   val13   ...
    row2    val21   val22   val23   ...
    ...
    

    crosstab函数采用text参数,该参数是生成以第一种方式格式化的原始数据的SQL查询,并生成以第二种方式格式化的表。

    该sql参数是一个生成源数据集的SQL语句。这个语句必须返回 row_name、category 、value 列。(N这列已经过时,不讨论)

    例如:提供的查询可能会产生如下集合:

     row_name    cat    value
    ----------+-------+-------
      row1      cat1    val1
      row1      cat2    val2
      row1      cat3    val3
      row1      cat4    val4
      row2      cat1    val5
      row2      cat2    val6
      row2      cat3    val7
      row2      cat4    val8
    

    crosstab声明该函数返回setof record,因此必须在FROM在调用SELECT语句的子句中定义输出列的实际名称和类型,例如:

    SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
    

    这个例子产生一个类似于的集合:

     row_name   category_1   category_2
    ----------+------------+------------
      row1        val1         val2
      row2        val5         val6
    

    The FROM clause must define the output as one row_name column (of the same data type as the first result column of the SQL query) followed by N value columns (all of the same data type as the third result column of the SQL query)

    该crosstab函数为具有相同row_name值的每个连续输入行组生成一个输出行。它value使用value这些行中的字段从左到右填充输出列。如果组中的行少于输出value列,则额外的输出列将填充空值; 如果有更多行,则跳过额外的输入行。

    实际上,SQL查询应始终指定ORDER BY 1,2以确保输入行的顺序正确,即具有相同值的值row_name汇集在一起​​并在行中正确排序。请注意,crosstab它本身并不关注查询结果的第二列; 它只是在那里被命令,以控制第三列值在页面中出现的顺序。

    完整案例

    CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
    INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
    INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
    INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
    INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
    INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
    INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
    INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
    INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
    
    SELECT *
    FROM crosstab(
      'select rowid, attribute, value
       from ct
       where attribute = ''att2'' or attribute = ''att3''
       order by 1,2')
    AS ct(row_name text, category_1 text, category_2 text, category_3 text);
    
     row_name | category_1 | category_2 | category_3
    ----------+------------+------------+------------
     test1    | val2       | val3       |
     test2    | val6       | val7       |
    (2 rows)
    
    

    2.2 CrosstabN(Text)

    crosstabN(text sql)
    

    其中N的使用是限制数量
    实际案例,N是多少,category就输出多少列:

    SELECT *
    FROM crosstab3(
      'select rowid, attribute, value
       from ct
       where attribute = ''att2'' or attribute = ''att3''
       order by 1,2');
     row_name | category_1 | category_2 | category_3
    ----------+------------+------------+------------
     test1    | val2       | val3       |
     test2    | val6       | val7       |
    (2 rows)
    

    2.3 crosstab(text, text)

    crosstab(text source_sql, text category_sql)
    
    

    单参数形式的主要限制crosstab是它同样处理组中的所有值,将每个值插入第一个可用列。如果您希望值列对应于特定类别的数据,并且某些组可能没有某些类别的数据,那么这样做效果不佳。crosstab这种情况下的双参数形式通过提供与输出列对应的类别的显式列表来处理

    category_sql是一个生成类别集的SQL语句。该语句必须只返回一列。它必须至少生成一行,否则将生成错误。此外,它不能生成重复值,否则将生成错误。category_sql可能是这样的:

    案例一:

    create table sales(year int, month int, qty int);
    insert into sales values(2007, 1, 1000);
    insert into sales values(2007, 2, 1500);
    insert into sales values(2007, 7, 500);
    insert into sales values(2007, 11, 1500);
    insert into sales values(2007, 12, 2000);
    insert into sales values(2008, 1, 1000);
    
    select * from crosstab(
      'select year, month, qty from sales order by 1',
      'select m from generate_series(1,12) m'
    ) as (
      year int,
      "Jan" int,
      "Feb" int,
      "Mar" int,
      "Apr" int,
      "May" int,
      "Jun" int,
      "Jul" int,
      "Aug" int,
      "Sep" int,
      "Oct" int,
      "Nov" int,
      "Dec" int
    );
     year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
    ------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
     2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
     2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
    (2 rows)
    
    

    案例二:

    CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
    INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
    INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
    INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
    INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
    INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
    INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
    INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');
    
    SELECT * FROM crosstab
    (
      'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
      'SELECT DISTINCT attribute FROM cth ORDER BY 1'
    )
    AS
    (
           rowid text,
           rowdt timestamp,
           temperature int4,
           test_result text,
           test_startdate timestamp,
           volts float8
    );
     rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
    -------+--------------------------+-------------+-------------+--------------------------+--------
     test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
     test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
    (2 rows)
    
    

    您可以创建预定义函数,以避免在每个查询中写出结果列名称和类型。像上面一样,SELECT DISTINCT attribute FROM cth ORDER BY 1


    2.4 Connectby

    connectby(text relname, text keyid_fld, text parent_keyid_fld
              [, text orderby_fld ], text start_with, int max_depth
              [, text branch_delim ])
    

    该connectby函数生成存储在表中的分层数据的显示。该表必须具有唯一标识行的键字段,以及引用每行的父(如果有)的父键字段。connectby可以显示从任何行下降的子树。
    参数描述

    参数 描述
    relname 源关系的名称
    keyid_fld 关键字段的名称
    parent_keyid_fld 父键字段的名称
    orderby_fld 要对同级排序的字段的名称(可选)
    start_with 要开始的行的键值
    max_depth 下降到的最大深度,或无限深度的零
    branch_delim 用于在分支输出中分隔键的字符串(可选)

    键和父键字段可以是任何数据类型,但它们必须是相同的类型。请注意start_with,无论键字段的类型如何,都必须将该值作为文本字符串输入

    connectby声明该函数返回setof record,因此必须FROM在调用SELECT语句的子句中定义输出列的实际名称和类型,例如:

    SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
        AS t(keyid text, parent_keyid text, level int, branch text, pos int);
    

    使用注意事项

    • branch_delim 不要出现任何的键值,否者connectby 会报一个无线递归错误,默认提供 ~(It is important that the branch_delim string not appear in any key values, else connectby may incorrectly report an infinite-recursion error. Note that if branch_delim is not provided, a default value of ~ is used for recursion detection purposes.)
    • 在大表中,除非父节点有索引(In large tables, performance will be poor unless there is an index on the parent-key field.)
    • 如果同一父节点的兄弟节点的顺序很重要,请包含orderby_fld参数以指定要按兄弟节点排序的字段。该字段可以是任何可排序的数据类型。输出列列表必须包含最后一个整数序列号列,当且仅当orderby_fld指定时。
    CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
    
    INSERT INTO connectby_tree VALUES('row1',NULL, 0);
    INSERT INTO connectby_tree VALUES('row2','row1', 0);
    INSERT INTO connectby_tree VALUES('row3','row1', 0);
    INSERT INTO connectby_tree VALUES('row4','row2', 1);
    INSERT INTO connectby_tree VALUES('row5','row2', 0);
    INSERT INTO connectby_tree VALUES('row6','row4', 0);
    INSERT INTO connectby_tree VALUES('row7','row3', 0);
    INSERT INTO connectby_tree VALUES('row8','row6', 0);
    INSERT INTO connectby_tree VALUES('row9','row5', 0);
    
    -- 带分支,没有orderby_fld(不保证结果的顺序)
    SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
     AS t(keyid text, parent_keyid text, level int, branch text);
     keyid | parent_keyid | level |       branch
    -------+--------------+-------+---------------------
     row2  |              |     0 | row2
     row4  | row2         |     1 | row2~row4
     row6  | row4         |     2 | row2~row4~row6
     row8  | row6         |     3 | row2~row4~row6~row8
     row5  | row2         |     1 | row2~row5
     row9  | row5         |     2 | row2~row5~row9
    (6 rows)
    
    -- 没有分支,没有orderby_fld(不保证结果的顺序)
    SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
     AS t(keyid text, parent_keyid text, level int);
     keyid | parent_keyid | level
    -------+--------------+-------
     row2  |              |     0
     row4  | row2         |     1
     row6  | row4         |     2
     row8  | row6         |     3
     row5  | row2         |     1
     row9  | row5         |     2
    (6 rows)
    
    -- 带分支,有排序。(notice that row5 comes before row4) 
    SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
     AS t(keyid text, parent_keyid text, level int, branch text, pos int);
     keyid | parent_keyid | level |       branch        | pos
    -------+--------------+-------+---------------------+-----
     row2  |              |     0 | row2                |   1
     row5  | row2         |     1 | row2~row5           |   2
     row9  | row5         |     2 | row2~row5~row9      |   3
     row4  | row2         |     1 | row2~row4           |   4
     row6  | row4         |     2 | row2~row4~row6      |   5
     row8  | row6         |     3 | row2~row4~row6~row8 |   6
    (6 rows)
    
    -- without branch, with orderby_fld (notice that row5 comes before row4)
    SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
     AS t(keyid text, parent_keyid text, level int, pos int);
     keyid | parent_keyid | level | pos
    -------+--------------+-------+-----
     row2  |              |     0 |   1
     row5  | row2         |     1 |   2
     row9  | row5         |     2 |   3
     row4  | row2         |     1 |   4
     row6  | row4         |     2 |   5
     row8  | row6         |     3 |   6
    (6 rows)
    

    参考

    https://www.postgresql.org/docs/10/tablefunc.html

    展开全文
  • Sybase15实现 自定义行转列函数

    千次阅读 2016-05-04 10:43:30
    Sybase15实现 自定义行转列函数
    Sybase15支持自定义函数,很多时候B/S开发需要使用行转列函数,下面我举一例供大家参考!

    根据表的id实现获取表所有字段的函数,id,可以根据表名通过sysobjects系统表获取到。

    create function fn_columnnames (@tblid int)
    returns varchar(1000)
    as
    begin
    	declare  @vcolname varchar(50),@vcolnames varchar(1000)
    	select @vcolnames=''
    	declare cur_column cursor for
    		select name from syscolumns where id=@tblid
    	open cur_column
        
    	fetch next from cur_column into @vcolname
    	while (@@fetch_status = 0)
    	begin 
    	    select @vcolnames=@vcolnames+@vcolname+','
    		fetch next from cur_column into @vcolname
    	end   
    	close cur_column    
    	deallocate cur_column  
    	if len(@vcolnames) > 0 
    		select @vcolnames = substring(@vcolnames,1,len(@vcolnames) -1)
    
    	-- return the result of the function
    	return @vcolnames
    
    end

    展开全文
  • MYSQL一个字段多个值拼接成一列函数group_concat示例函数相关配置 示例 1.单列多行合并单行单列展示 select group_concat(name) as name from student where classId = 1 执行结果如下图: 2.多列拼接,多列多行值...

    MYSQL一个字段多个值拼接成一列函数group_concat

    示例

    1.单列多行合并单行单列展示

    select group_concat(name) as name from student where classId = 1
    

    执行结果如下图:
    在这里插入图片描述

    2.多列拼接,多列多行值合并成单行单列显示

    select group_concat(name,sex) as name from student where classId = 1
    

    执行结果如下图:
    在这里插入图片描述
    3. 值排序拼接

     select group_concat(name order by id desc) as name from student where classId = 1
    

    函数相关配置

    1.group_concat长度:

    group_concat默认最大长度是1024,超过截取前1024长度字符。

    2.查询group_concat长度SQL

    show variables like 'group_concat_max_len';
    

    3.更改group_concat长度配置

    ①修改MYSQL的配置文件my.ini(需要重启mysql服务):

    group_concat_max_len = 2048;
    

    ②执行语句

    SET GLOBAL group_concat_max_len=1024000;
    SET SESSION group_concat_max_len=1024000;
    

    扫二维码关注博主公众号👇

    在这里插入图片描述

    展开全文
  • Oracle 行转列函数

    千次阅读 2019-05-29 15:19:29
    pivot (sum(amount) for type in( '001' 截止上月设计申请金额 , '002' 截止上月现场申请金额 )); pivot(聚合函数 for 转的字段 in (字段值 别名));
  • 1 行转列函数: select wm_concat(username) from users 2 查询重复记录函数 select t.* from(select t.*,rownum rn from USERS t where t.username in (select username from USERS group by username having...
  • c++中队列函数queue的常用

    万次阅读 多人点赞 2018-10-22 22:55:52
    即可使用队列类,其中在面试或笔试中常用的成员函数如下(按照最常用到不常用的顺序) push() pop() size() empty() front() back() 接下来逐一举例说明: push() 队列中由于是先进先出,push即在...
  • oracle中的行转列函数

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

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

    万次阅读 2017-05-30 11:34:02
    往一个dataframe新增某个是很常见的事情。 ...然而这个资料还是不多,很多都需要很多变换。...不过由于这回需要增加的非常简单,倒也没有必要再用UDF函数去修改。 利用withColumn函数就能实现对da
  • hive的虚拟列函数

    千次阅读 2016-02-14 15:46:37
    hive的虚拟  hive 0.8版本之后提供了两个函数,其实也没什么用处,可以起到辅助函数的作用。 INPUT__FILE__NAME:函数的作用就是显示每一个map任务所对应输文件的目录以及文件名。 BLOCK__OFFSET__INSIDE__...
  • oracle行转列函数

    万次阅读 2016-12-05 18:03:39
    WMSYS.WM_CONCAT 函数的用法   select t.rank, t.Name from t_menu_item t;  10 CLARK  10 KING  10 MILLER  20 ADAMS  20 FORD  20 JONES  20 SCOTT  20 SMITH  30 ALLEN
  • PIVOT 提供的语法比一系列复杂的...非透视的>, [第一个透视的] AS <名称>, [第二个透视的] AS <名称>, ... [最后一个透视的] AS <名称>, FROM ...
  • Oracle 关于oracle自带的行转列函数

    千次阅读 2016-03-11 14:55:40
    目标是将某表中根据id分组后将name字段的值拼接到一中,且用“,“进行分割   试过用 wm_concat() 结合 group by , 在pl/sql中执行正常, 但是在java程序运行时会报: ORA-22922:不存在的 LOB 值   1....
  • python 中pd.pivot_table行转列函数使用

    千次阅读 2018-10-25 16:30:46
  • PostgreSQL的行转列函数使用一例

    千次阅读 2014-11-04 18:09:27
    开发同事说使用postgres的扩展行转应用时有一个问题,示例如下 该扩展包初步使用参考: http://my.oschina.net/Kenyon/blog/54357 一、环境 OS:CentOS 6.3 DB:PostgreSQL 9.3.0 二、场景 ...
  • WMSYS.WM_CONCAT: 依赖WMSYS 用户,不同oracle环境时可能...LISTAGG : 11g2才提供的函数,不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型,最大长度为4000.   WMSYS.WM_CONCAT()  select deptno,
  •  但在sql server里没有这个函数,那怎么办?只能另找其他方法了。   select id, [ names ] = stuff (( select ' , ' + [name ] from table1 A t where A. id =B. id for xml path( '' )...
  • 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等)。 例、原表为: 将这个表格...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 61,984
精华内容 24,793
关键字:

列函数