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

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