精华内容
下载资源
问答
  • 要将ta和tb中的数据合并,如果ta和tb中id相同的话,则把num1和num2相加,开始没想要分组, 其实很简单,sql如下: select sum(num1), id1 from (select * from TESTA a Union All select * from TESTB b) group by ...
    工作中有一需求,要根据多个表的数据统计情况来更新另外一个表中的某个字段,折腾了会才写出如下sql:
    

    --最终版更新语句
    update moka_user_usage t
    set portfolionumber = (select ta.pnum
    from (select sum(pnum) pnum, pomuid
    from ((select count(om.userid) pnum,
    om.userid pomuid
    from MOKA_PORTFOLIO p,
    MOKA_PORTFOLIO_CATEGORY c,
    om_user om
    where p.type_id = c.type_id and
    p.verify_flag = '1' and
    p.open_flag = '0' and
    om.userid = p.user_id
    -- and t.userid = om_user.userid
    group by om.userid) union all
    select *
    from (select count(mom.username) mctnum,
    mom.userid mctuid
    from mct_works_type_mapping mtp,
    mct_works mct,
    om_user mom
    where mtp.works_id =
    mct.works_id and
    mct.cp_id =
    mom.username and
    mct.state = '1'
    group by mom.userid))
    group by pomuid
    ) ta
    where ta.pomuid = t.userid)





    当然这个和业务相关,我贴出来只想记录下。

    在写这个sql时,遇到个小问题,不知该如何解决,问题意思大概如下:

    有两个表ta(num1,id1) tb(num2,id2)

    ta表数据如下:
    NUM1 ID1

    1 100
    2 101
    5 107
    4 108
    9 210

    tb的数据如下:

    NUM2 ID2
    3 100
    8 104
    5 301
    10 101
    20 710
    45 107

    要将ta和tb中的数据合并,如果ta和tb中id相同的话,则把num1和num2相加,开始没想要分组,

    其实很简单,sql如下:

    select sum(num1), id1
    from (select *
    from TESTA a
    Union All
    select * from TESTB b)
    group by id1


    顺便温故了下sql,union all 为合并两个结果集,不会合并重复的行,而union 则会合并重复的行。
    展开全文
  • 方法一:wmsys.wm_concat(column)介绍:其函数Oracle 10g推出,在10g版本中,返回字符串类型,在11g版本中返回clob类型。括号里面的参数是列,而且可以是多个列的集合,也就是说在括号里面可以自由地用‘||’合并...

    方法一:wmsys.wm_concat(column)

    介绍:其函数在Oracle 10g推出,在10g版本中,返回字符串类型,在11g版本中返回clob类型。括号里面的参数是列,而且可以是多个列的集合,也就是说在括号里面可以自由地用‘||’合并字符串。如下面的例子:

    Select u_id, wmsys.wm_concat(goods ||

    '(' || num || '斤)' ) goods_sum from shopping group by u_id

    方法二:listagg (column,[,]) within group (order by ) [over

    (partition by  )]

    介绍:其函数在Oracle 11g

    版本中推出,对分组后的数据按照一定的排序进行字符串连接。其中,“[,]”表示字符串连接的分隔符,如果选择使用[over

    (partition by )]则会使其变成分析函数;

    方法三:sys_connect_by_path(column,)

    介绍:其函数在Oracle 9i 版本中推出,用来合并链路的字符串。注意的是其一定要和connect by子句合用!第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符。

    下面是上面几种方法的实例(在Oracle

    11g版本中运行正确):

    实例:

    方法一:用listagg(,',') within

    group()

    SQL

    code

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    WITH temp1 AS(

    select 'a' as username,1 as deptid from dual union all

    select 'b',1 from dual union all

    select 'c',1 from dual union all

    select 'd',2 from dual union all

    select 'e',2 from dual

    ),

    temp2 AS(

    select 1 as deptid,'部门1' as deptname from dual union all

    select 2 ,'部门2' from dual

    )

    select p.deptid,

    listagg(t.username,',') within

    group (order by t.username) as username,

    p.deptname

    from temp1 t,temp2

    p

    where t.deptid=p.deptid

    group by p.deptid,p.deptname

    order by p.deptid

    -------------------------

    deptid userName

    deptName

    1 a,b,c 部门1

    2 d,e 部门2

    方法二:用wm_concat()

    SQL

    code

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    select p.deptid,

    wm_concat(t.username) as

    username,

    p.deptname

    from temp1 t,temp2

    p

    where t.deptid=p.deptid

    group by p.deptid,p.deptname

    order by p.deptid

    方法三:用CONNECT BY

    SQL

    code

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    select deptid,

    ltrim(max(sys_connect_by_path(username,',')),','),

    deptname

    from (

    select p.deptid,t.username,p.deptname,

    row_number()over(partition by

    t.deptid order by t.username) as ar

    from temp1 t,temp2

    p

    where t.deptid=p.deptid

    )

    start with ar=1

    connect by prior ar=ar-1

    and username=prior username

    group by deptid,deptname

    order by deptid

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    --------------如有错误,请指教!

    --------------技术交流QQ:1732035211

    -------------技术交流邮箱:1732035211@qq.com

    展开全文
  • listagg 函数------(有长度限制) SELECT listagg(字段, ',') within group(ORDER BY 字段) AS NAMES FROM 表 GROUP BY 字段 //去重:regexp_replace(listagg 函数, '([^,]+)(,\1)+', '\1') 用聚合函数,然后使用 ...

    listagg 函数------(有长度限制)

    SELECT listagg(字段, ',') within group(ORDER BY 字段) AS NAMES FROMGROUP BY 字段
    //去重:regexp_replace(listagg 函数, '([^,]+)(,\1)+', '\1')
    

    用聚合函数,然后使用 wmsys.wm_concat(拼接字段) 方法------(有长度限制,可去重)

    select wmsys.wm_concat(distinct 字段) as 字段别名 from table t group by 字段
    

    无长度限制的方法

    select XMLAGG(XMLELEMENT(E, 待拼接字段 || ',') ORDER BY 排序字段).EXTRACT('//text()').getclobval()  as  别名  from 表名  GROUP BY 分组字段
    或
    SELECT rtrim(xmlagg(xmlparse(content  待拼接字段 || ',' wellformed)  ORDER BY 排序字段).getclobval(),',')  别名  from 表名  GROUP BY 分组字段   //该方法更快
    
    展开全文
  • Oracle分组函数

    千次阅读 2018-06-05 08:34:24
    1.分组函数: 多行函数\聚合函数:就是对一组中的数据进行处理获取一个结果2. max min sum avg count 自动忽略null值 select count(comm) from emp; 43.注意: sum() avg() 只能对数值操作 max() min() 只对 日期和...
    1.分组函数:
    
    多行函数\聚合函数:就是对一组中的数据进行处理获取一个结果
    2.
    max
    min
    sum
    avg
    count
    自动忽略null值

    select count(comm) from emp;
    4
    3.注意:
    sum() avg() 只能对数值操作
    max() min() 只对 日期和数值操作
    count()  可以对任何类型
    可以在组函数中 使用 distinct关键字去除重复的数据
    select count(distinct job) from emp;
    4.组函数,不能和普通的列一起使用,
    要么该列是分组的依据或者在组函数内部

    select count(empno),ename from emp; -- 错误
    select count(empno),count(ename) from emp;
    -- 统计,同名员工数量
    select count(empno),ename from emp group by ename;
    5.在使用count 统计数量时,禁止使用 * 号
    count(*):表示统计所有列中的出现次数,返回其中次数最大的数
    建议使用 count(主键)
    6.group by 分组工具,提示按照什么把数据分成几块在统计

    group by a,b,c 
    表示 先按照 a 分成大组,在每一组中按照 b 在分组,后每一组在 按照c 细分
    最后统计的是 按照c 分组后,每一组中的数据

    select
    to_char(hiredate,'yyyy'), to_char(hiredate,'mm'),count(empno)
    from emp 
    group by to_char(hiredate,'yyyy'),to_char(hiredate,'mm')
    7.having:表示在分组后 进一步 筛选
    只要出现以下字眼:最高工资大于xx,最低工资小于xxx
    总人数xx
    平均值xxx
    总数xxx
    查询的条件中出现聚合数据了,只能使用having 处理
        having 只能跟在 group by 后面 不能单独使用
    -- 查询 部门总人数 超过 3 人的部门编号\最大工资和人数及从事工作种类;


    select
       deptno,max(sal),count(empno),count(distinct job)
    from emp 
         group by deptno
               having count(empno)>3
    -- 查询每个部门工资超过 2000 并且人数超过 2 人的 部门编号
    select
       deptno
    from emp
         where sal>2000
         group by deptno
               having count(empno)>2
       
    -- 查询平均工资超过 2000 的部门的名称
    我们查询的数据 不是来自一张表 而是从多张表中获取
    这些之间存在一定的关系(a表中某列的值等于 b表中某列的值)
    表连接:同过表与表之间的关联,查询需要的数据    
    在进行表连接查询时
    1)必须使用表名作前缀来提高查询的效率
    2)如果查询出现同名列,必须提供别名进行区分
    1.笛卡尔积:a表中的所有行与 b表中的所有行以此匹配
    a 中有 5行 b中有 6行 最终获取 30行
    2.表连接的语法
    select 子句
    from 表 a,表 b,...
    where a表和b表关联的条件  and  其他条件
    3.在表连接的情况如何编写 sql 语句
    a)清楚需要显示的是什么数据,这些放在 select 后 即可
    b)清楚显示的数据来自哪些表  放在 from 后面 使用 , 分割
    c)清楚 这些表 之间的关系  放在 where 后 多个关系使用 and 链接
    d)如果 表是通过第三张表或更多的表建立的关系
    那么 这些表 也要放在 from 中 
    并且 关联的条件 放在 where 中
    e)再考虑其他条件 再 where 中 添加即可


    --------------------------------
    1.语法:
    select 子句
    from 表 a,表 b,...
    where 各个表关联的条件
    and 其他条件
    2.编写sql语句的步骤
    a)用户要的是什么,这些内容是否需要聚合获取(考虑分组的依据)
    b)这些内容来自哪些表或相关的表
    c)确定表与表之间的关系
    d)分析是否还有其他条件
    e)如果显示的内容需要使用聚合函数,使用group by 
    f)使用 order by 排序
    注意:如果group by 或 order by 中设计的列 ,所属的表 在from 中没有,
    还要在添加
    --
    3.表连接
    a)内连接:查询表中有关联关系的数据,没有匹配的不显示
    select
    *
    from emp e,dept d 
    where e.deptno=d.deptno


    b)外连接:查询某张表中的全部数据,没有匹配的显示为null
    根据完成显示数据表的位置不同(根据 + 的位置不同)
    左外链接:把等号左边对应表中的数据完成显示
    select
    *
    from emp e,dept d 
    where e.deptno=d.deptno(+)
    右外链接:
    把等号右边对应表中的数据完成显示

    select
    *
    from emp e,dept d 
    where e.deptno(+)=d.deptno;

    select
    *
    from emp e,dept d 
    where e.deptno=d.deptno(+);
    select
    *
    from emp e,dept d 
    where d.deptno(+)=e.deptno;
    全外链接:左外+右外
    select
       e.ename,d.dname
    from emp e,dept d
    where e.deptno=d.deptno(+)
    union  -- 集合操作
    select
    e.ename,d.dname
    from dept d,emp  e
    where e.deptno(+)=d.deptno    
    union 合并查询的结果,自动去除重复数据
    union all 直接合并,不会去除重复
    注意:
    两个查询,获取的列数和对应列的数据类型必须一致
    列名无关
    自连接:通过别名把一张表 虚拟为多张表 进行表连接查询
    一张表中同时描述多类事情(上下级关系)
    关键在于,区分谁是主表 谁是从表
    展开全文
  • select m.parent_id, m.start...= '1' and t.parent_id is not null order by s.user_orderby) m group by m.parent_id, m.start_date 按parent_id和start_date分组合并每个组的cleck_name和userId 另一个实例: 来自...
  • Oracle同行合并分组

    2021-05-04 05:46:07
    继续,把上述看成一张虚拟表,用到我们的sys_connect_by_path函数取出想要的值。格式:sys_connect_by_path(column,'')start with 条件1connect by 条件2(prior 子节点=父节点)select bookid,sys_connect_by_path...
  • oracle中,使用wm_concat(column)函数,可以进行字段值合并合并的字段默认通过逗号分隔,也可自行指定。如:wm_concat(col1);replace(wm_concat(col1),',','|'); --自定义分隔号,使用replace将逗号进行替换(wm_...
  • oracle分组查询

    2015-11-28 10:04:06
    oracle分组查询数据 select BFMBIGNAME,projectaddress,BFMSMALLNAME from(select BFMBIGNAME,projectaddress,BFMSMALLNAME,row_number() over (partition by BFMBIGNAME order by REGISTERDATE) id from Project...
  • 最近在学习的过程中,发现一个挺有意思的函数,它可实现对列值的拼接。下面我们来看看其具体用法。 用法: 对其作用,官方文档的解释如下: For a specified measure,LISTAGGorders data within each group ...
  • 主要介绍了oracle实现多行合并的方法,实例讲述了oracle10g以后提供的函数WMSYS.WM_CONCAT的使用技巧,需要的朋友可以参考下
  • 按字段进行分组,没分组字段通过函数进行字符串的合并,用','分割CREATE OR REPLACE FUNCTION zh_concat(P1 VARCHAR2) RETURN clob AGGREGATE USING zh_concat_im;------------------------------------------------...
  • 要按照deptno相同的将ename以字符串形式合并,可用如下语句: select deptno,wm_concat(ename) from emp group by deptno; 查询结果: 查询结果添加 ' 用 ('''') 这里返回的是一个clob字段...
  • 使用wm_concat函数 select TLXCONTENT ,wm_concat(RADDRESS) as RADDRESS from t_sendtlx where cdat between '20200101000000' and '20200330235959' and ctyp='DEP' group by TLXCONTENT;
  • 关键字row_number() over (partition by)例如,下面的数据, 这是按照name分组后,展示property值。 我们想得到这样的值; 第一步:将每一组的property标上序号select name,property,row_number() over (partition by...
  • oracle wm_concat(column)函数使我们经常会使用到的,下面就教您如何使用oraclewm_concat(column)函数实现字段合并 如: shopping: ----------------------------------------- u_id goods num ---------...
  • 按字段进行分组,没分组字段通过函数进行字符串的合并,用','分割 CREATE OR REPLACE FUNCTION zh_concat(P1 VARCHAR2) RETURN clob AGGREGATE USING zh_concat_im;----------------------------------------------...
  • oracle递归查询函数

    2021-05-01 02:44:22
    oracle递归查询函数(2013-01-14 14:57:23)标签:杂谈select a.client_department_id,a.parent_id,level "层次",sys_connect_by_path(client_department_id, '->')"合并层次",prior a.client_department_id "父...
  • SQL 聚合函数之字符串分组合并

    千次阅读 2020-12-08 22:55:38
    本文介绍了如何通过 SQL 函数对字符串...包括 Oracle(LISTAGG 函数)、MySQL(GROUP_CONCAT 函数)、SQL Server(STRING_AGG 函数)、PostgreSQL(STRING_AGG 函数)以及 SQLite(GROUP_CONCAT 函数)的语法和差异。
  • 根据函数的返回结果,我们将函数分为单行函数和多行函数 单行函数:一条记录返回一个结果 多行函数、组函数、聚合函数(重点):多条记录返回一个结果(重点) 1.1、日期函数 sysdate/current_date 以date类型...
  • 项目内需要解决主表a和明细表b的连接问题,要求将b表中的不同列部分进行合并起来;其实也是网上搜出来的解决办法,:),只要自己理解了就成为自己的知识了,:)Create Table wbq_test (a varchar2(10),b int);Insert ...
  • 数据库的统计汇总分组合并,对表数据进行检索时,经常需要对结果进行汇总或计算,例如,在学生成绩数据库中求某门课程的总成绩,统计个分数段的人数等。1.统计函数统计函数用于计算表中的数据,返回单个计算结果SUM...
  • 什么是合并多行字符串(连接字符串)呢,例如: SQL> desc test; Name Type Nullable Default Comments ——- ———— ——– ——- ——– COUNTRY VARCHAR2(20) Y CITY VARCHAR2(20) Y SQL> select * from test; ...
  • oracle 单列数据分组合并

    千次阅读 2017-11-17 14:16:42
    oracle由于版本的问题,一些函数不会相互兼容。 当我们在不知道自己的所使用的oracle是什么版本的时候可以通过下面的这个语句查询下在使用具体的函数。select * from v$version;在oracle10g中wn_concat()函数是可以...
  • oracle 分析函数

    2021-05-02 11:24:58
    Oracle行列转换函数createtableexam_record(idintegerprimarykey,usernamevarchar2(40),classtypevarchar2(40),classnamevarchar2(200),scorenumber(20,2));insertintoexam_recordvalues(1,'张三','文科','语文',97)...
  • LISTAGG(列名,'分割符号')oracle 11g 以上的版本才有的一个将指定列名的多行查询结果,用 指定的分割符号合并成一行...license_id 数据进行分组,对 Item_Category_Name 列的数据进行 去重合并使用聚合函数LISTAG...
  • 很多场合我们都会用到oracle的列合并oracle提供了如下一些方法用来实现列合并:一、Oracle 10G以前使用WMSYS.WM_CONCAT:wmsys.wm_concat将字段的值用","来隔开。select id,wm_concat(name) from tab_name group ...

空空如也

空空如也

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

oracle分组合并函数