oracle数据库的sql平时遇到的一些小问题union，to_char(),null last,group by

最新推荐文章于 2022-02-07 01:37:02 发布

summer_zdp

最新推荐文章于 2022-02-07 01:37:02 发布

阅读量950

点赞数

分类专栏：数据库文章标签：数据库 oracle

本文链接：https://blog.csdn.net/summer_zdp/article/details/78737906

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.在oracle中as只能对字段起别名，不能对表起别名，表的别名只能用空格（字段 as 别名/ 表名表别名）

2.如果使用group by 则group by 后的字段除了函数以外，别的都要有

3.union是把相同的union在一起，union all是把所有都union在一起，可能会出现相同的数据

4.sql排序，如果order by的字段有null值，可在最后加null last（null值得排在最后）null first(null值得排最后面)

记录一下之前写的较长的一条sql（里面的条件是mybatis里面直接贴出来的）

to_char(count (1)) AS TRANS_NUM 是对count(1)进行转换因为 count(1)的类型为int类型，sql里面有union要求union的两张表的类型要完全一致

select  sum(TXN_AMT) as TXN_AMT, sum(ACCT_AMT) as ACCT_AMT ,
        sum(TOTAL_FEE) as TOTAL_FEE,sum(TRANS_NUM) as  TRANS_NUM,
        sum(MCHNT_FEE) as MCHNT_FEE, sum(MCHNT_PRIVILEGE_FEE) as MCHNT_PRIVILEGE_FEE,
        MER_INNER_CODE, MER_NAME_CH,REC_CRT_TMS,
        CUST_MGR_NAME,MER_MANAGE_ORG,
        SETTLE_ACCOUNT,SETTLE_PRI_ACCOUNT,JGMC from(
        select
        TabRslt.TXN_AMT, TabRslt.ACCT_AMT,
        TabRslt.TOTAL_FEE, TabRslt.TRANS_NUM ,
        TabRslt.MCHNT_FEE, TabRslt.MCHNT_PRIVILEGE_FEE,
        TabRslt.SETTLE_DATE, TabBase.MER_INNER_CODE,
        TabBase.MER_NAME_CH, TabBase.REC_CRT_TMS,
        TabBase.CUST_MGR_NAME, TabBase.MER_MANAGE_ORG,
        TabOffline.SETTLE_ACCOUNT, TabOffline.SETTLE_PRI_ACCOUNT, ip.JGMC
        from CMCP_TBL_MER_BASE_INFO_R TabBase,        
        (select MCHNT_INNER_CD, SETTLE_DATE,
        sum(TXN_AMT) as TXN_AMT , sum(ACCT_AMT) as ACCT_AMT ,
        sum(TOTAL_FEE) as TOTAL_FEE ,sum(TRANS_NUM) as TRANS_NUM,
        sum(MCHNT_FEE) as MCHNT_FEE, sum(MCHNT_PRIVILEGE_FEE) as MCHNT_PRIVILEGE_FEE from (
        (select MCHNT_INNER_CD, SETTLE_DATE, TXN_AMT,
        ACCT_AMT, TRANS_NUM, MCHNT_FEE,TOTAL_FEE, MCHNT_PRIVILEGE_FEE
        from CMCP_CSMC_TBL_MCHNT_ACCT_RSLT
        where MCHNT_INNER_CD like '30904%' and task_param='B006'
        ) union all (
        select  MCHNT_INNER_CD,SETTLE_DATE, sum(TXN_AMT) as TXN_AMT,  
        sum(MCHNT_ACCT_AMT)as  ACCT_AMT ,to_char(count (1)) AS TRANS_NUM, sum(MCHNT_FEE) as MCHNT_FEE,    
        sum(MCHNT_TOTAL_FEE) as TOTAL_FEE, sum(MCHNT_PRIVILEGE_FEE) as MCHNT_PRIVILEGE_FEE
        from CMCP_CSMC_TBL_DZ_RSLT
        where task_param='B010'
        group by MCHNT_INNER_CD,SETTLE_DATE)    
        ) group by MCHNT_INNER_CD,SETTLE_DATE) TabRslt,
        CMCP_TBL_MER_OFFLINE_R TabOffline, IP_UNIT ip
        where TabBase.MER_INNER_CODE = TabOffline.MER_INNER_CODE
        and TabRslt.MCHNT_INNER_CD = TabOffline.MER_INNER_CODE
        and Tabbase.MER_MANAGE_ORG=ip.zj  
        and TabBase.MER_MANAGE_ORG in (select ivo.zj from IP_Unit  ivo where 1=1 and ivo.xzcxlj like '%${xzcxlj}%' and ivo.jlzt = '1')
        group by
        TabBase.MER_INNER_CODE,TabRslt.TXN_AMT, TabRslt.ACCT_AMT,
        TabRslt.TOTAL_FEE, TabRslt.TRANS_NUM , TabRslt.MCHNT_FEE,
        TabRslt.MCHNT_PRIVILEGE_FEE, TabRslt.SETTLE_DATE,
        TabBase.MER_NAME_CH, TabBase.REC_CRT_TMS,
        TabBase.CUST_MGR_NAME, TabBase.MER_MANAGE_ORG,
        TabOffline.SETTLE_ACCOUNT, TabOffline.SETTLE_PRI_ACCOUNT, ip.JGMC)        
        GROUP by MER_INNER_CODE,
        MER_NAME_CH,REC_CRT_TMS,
        CUST_MGR_NAME,MER_MANAGE_ORG,
        SETTLE_ACCOUNT,SETTLE_PRI_ACCOUNT,JGMC