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