Oracle 排序中 nulls first 与 nulls last 的用法
Nulls first和nulls last是Oracle Order by支持的语法
如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
使用语法如下：
--将nulls始终放在最前
select* from baseinfo_operator order by name nulls first
--将倒序时合计放在最下面
select case when grouping(clr.cardNo)=1 then '合计' else to_char(clr.cardNo) end cardNo,case when grouping(clr.cardCSN)=1 then to_char(count(*)) || '条' else clr.cardCSN end cardCSN ,
clr.newCardNo as newCardNo,clr.newCardCSN as newCardCSN, cc.nameCN AS cardType,rc.nameCN AS RechargeCenters,rt.nameCN AS RechargeTerminals,o.nameCN AS operatorName,
clr.accountsTransferDate AS accountsTransferDate,clr.customerName AS customerName,clr.papersCard AS papersCard,cc2.nameCN AS transferCardType,
SUM (F2Y (nvl(clr.walletBalances, 0))) AS walletBalances,SUM (nvl(clr.monthTicketNums, 0)) AS monthTicketNums,SUM (F2Y (nvl(clr.monthTicketBalances, 0))) AS monthTicketBalances,
SUM (F2Y (nvl(clr.disCountRechargeMoney,0))) AS disCountRechargeMoney,SUM (nvl(clr.creditScore, 0)) AS creditScore,SUM (nvl(clr.creditNums, 0)) AS creditNums,
SUM (F2Y (nvl(clr.factorage, 0))) AS factorage,sum(F2Y (clr.walletBalances) + F2Y (clr.monthTicketBalances))as sumBalances from CardLossRecords clr
LEFT JOIN CODE_Content cc ON cc.oid = clr.cardType LEFT JOIN BASEINFO_Operator o ON o.oid = clr.operatorOID
LEFT JOIN CODE_Content cc2 ON cc2.oid = clr.lossType LEFT JOIN BASEINFO_RechargeTerminals rt ON rt.oid = clr.createLossAreaOID
LEFT JOIN BASEINFO_RechargeCenters rc ON rc.oid = rt.rechargeCenterOID
where accountsTransfer=1 and isOtherOperate=0 and clr.valid=1 and clr.operatorOID ='0' and to_char(clr.accountsTransferDate,'yyyy-mm-dd hh24:mi:ss') >= '2017-09-06 00:00:00'
and to_char(clr.accountsTransferDate,'yyyy-mm-dd hh24:mi:ss')<='2017-09-06 23:59:59'
group by rollup(clr.cardNo,clr.cardCSN,clr.newCardNo,clr.newCardCSN,cc.nameCN,rc.nameCN,rt.nameCN,o.nameCN,clr.accountsTransferDate,clr.customerName,clr.papersCard,cc2.nameCN)
having( grouping(clr.cardNo)=1 or( grouping(clr.cardNo)=0 and grouping(clr.cardCSN)=0 and grouping(clr.newCardNo)=0 and grouping(clr.newCardCSN)=0
and grouping(cc.nameCN)=0 and grouping(rc.nameCN)=0 and grouping(rt.nameCN)=0 and grouping(o.nameCN)=0 and grouping(clr.accountsTransferDate)=0
and grouping(clr.customerName)=0 and grouping(clr.papersCard)=0 and grouping(cc2.nameCN)=0 ))
order by accountsTransferDate desc nulls last
clr.newCardNo as newCardNo,clr.newCardCSN as newCardCSN, cc.nameCN AS cardType,rc.nameCN AS RechargeCenters,rt.nameCN AS RechargeTerminals,o.nameCN AS operatorName,
clr.accountsTransferDate AS accountsTransferDate,clr.customerName AS customerName,clr.papersCard AS papersCard,cc2.nameCN AS transferCardType,
SUM (F2Y (nvl(clr.walletBalances, 0))) AS walletBalances,SUM (nvl(clr.monthTicketNums, 0)) AS monthTicketNums,SUM (F2Y (nvl(clr.monthTicketBalances, 0))) AS monthTicketBalances,
SUM (F2Y (nvl(clr.disCountRechargeMoney,0))) AS disCountRechargeMoney,SUM (nvl(clr.creditScore, 0)) AS creditScore,SUM (nvl(clr.creditNums, 0)) AS creditNums,
SUM (F2Y (nvl(clr.factorage, 0))) AS factorage,sum(F2Y (clr.walletBalances) + F2Y (clr.monthTicketBalances))as sumBalances from CardLossRecords clr
LEFT JOIN CODE_Content cc ON cc.oid = clr.cardType LEFT JOIN BASEINFO_Operator o ON o.oid = clr.operatorOID
LEFT JOIN CODE_Content cc2 ON cc2.oid = clr.lossType LEFT JOIN BASEINFO_RechargeTerminals rt ON rt.oid = clr.createLossAreaOID
LEFT JOIN BASEINFO_RechargeCenters rc ON rc.oid = rt.rechargeCenterOID
where accountsTransfer=1 and isOtherOperate=0 and clr.valid=1 and clr.operatorOID ='0' and to_char(clr.accountsTransferDate,'yyyy-mm-dd hh24:mi:ss') >= '2017-09-06 00:00:00'
and to_char(clr.accountsTransferDate,'yyyy-mm-dd hh24:mi:ss')<='2017-09-06 23:59:59'
group by rollup(clr.cardNo,clr.cardCSN,clr.newCardNo,clr.newCardCSN,cc.nameCN,rc.nameCN,rt.nameCN,o.nameCN,clr.accountsTransferDate,clr.customerName,clr.papersCard,cc2.nameCN)
having( grouping(clr.cardNo)=1 or( grouping(clr.cardNo)=0 and grouping(clr.cardCSN)=0 and grouping(clr.newCardNo)=0 and grouping(clr.newCardCSN)=0
and grouping(cc.nameCN)=0 and grouping(rc.nameCN)=0 and grouping(rt.nameCN)=0 and grouping(o.nameCN)=0 and grouping(clr.accountsTransferDate)=0
and grouping(clr.customerName)=0 and grouping(clr.papersCard)=0 and grouping(cc2.nameCN)=0 ))
order by accountsTransferDate desc nulls last
