Oracle中nullsfirst和nullslast在排序中的应用与区别

Nulls first和nulls last是Oracle Order by支持的语法

如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)

如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)

使用语法如下&＃xff1a;

--将nulls始终放在最前

select* from baseinfo_operator  order by name  nulls first

--将倒序时合计放在最下面

select case when grouping(clr.cardNo)&＃61;1 then &＃39;合计&＃39; else to_char(clr.cardNo) end cardNo,case when grouping(clr.cardCSN)&＃61;1 then to_char(count(*)) || &＃39;条&＃39; 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) &＃43; F2Y (clr.monthTicketBalances))as sumBalances from CardLossRecords clr
LEFT JOIN CODE_Content cc ON cc.oid &＃61; clr.cardType LEFT JOIN BASEINFO_Operator o ON o.oid &＃61; clr.operatorOID
 LEFT JOIN CODE_Content cc2 ON cc2.oid &＃61; clr.lossType LEFT JOIN BASEINFO_RechargeTerminals rt ON rt.oid &＃61; clr.createLossAreaOID
 LEFT JOIN BASEINFO_RechargeCenters rc ON rc.oid &＃61; rt.rechargeCenterOID
 where accountsTransfer&＃61;1 and isOtherOperate&＃61;0 and clr.valid&＃61;1  and clr.operatorOID &＃61;&＃39;0&＃39;  and to_char(clr.accountsTransferDate,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;) >&＃61; &＃39;2017-09-06 00:00:00&＃39;
 and to_char(clr.accountsTransferDate,&＃39;yyyy-mm-dd hh24:mi:ss&＃39;)<&＃61;&＃39;2017-09-06 23:59:59&＃39;
 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)&＃61;1 or( grouping(clr.cardNo)&＃61;0 and grouping(clr.cardCSN)&＃61;0 and grouping(clr.newCardNo)&＃61;0 and grouping(clr.newCardCSN)&＃61;0
 and grouping(cc.nameCN)&＃61;0 and grouping(rc.nameCN)&＃61;0 and grouping(rt.nameCN)&＃61;0 and grouping(o.nameCN)&＃61;0 and grouping(clr.accountsTransferDate)&＃61;0
  and grouping(clr.customerName)&＃61;0 and grouping(clr.papersCard)&＃61;0 and grouping(cc2.nameCN)&＃61;0 ))
 
   order by accountsTransferDate desc nulls last