DB2中没有像ORDER BY ord_col NULLS FIRST这样的东西,但你可以使用函数来获得相同的行为:
CREATE TABLE TV84 (
CORN CHAR(2) NOT NULL
,COUAG CHAR(4) NOT NULL
,DDFA DATE NOT NULL
,MTFAC1 INTEGER
);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('01','0309',TO_DATE('10.05.2008','MM.DD.YYYY'),2520);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('01','0309',TO_DATE('12.14.2008','MM.DD.YYYY'),NULL);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('01','0309',TO_DATE('10.15.2009','MM.DD.YYYY'),6452);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('02','0309',TO_DATE('06.16.2010','MM.DD.YYYY'),1283);
INSERT INTO TV84 (CORN,COUAG,DDFA,MTFAC1)
VALUES ('02','0309',TO_DATE('08.28.2010','MM.DD.YYYY'),NULL);
使用COALESCE标量函数:
SELECT CORN, COUAG, DDFA, MTFAC1
FROM TV84
ORDER BY COALESCE(MTFAC1,0);
CORN COUAG DDFA MTFAC1
---- ----- ---------- -----------
01 0309 14.12.2008 -
02 0309 28.08.2010 -
02 0309 16.06.2010 1283
01 0309 05.10.2008 2520
01 0309 15.10.2009 6452
或更明确地使用ROW_NUMBER OLAP函数(请参阅OLAP functions):
SELECT CORN, COUAG, DDFA, MTFAC1
,ROW_NUMBER() OVER (ORDER BY MTFAC1 NULLS FIRST) RN
FROM TV84
ORDER BY RN;
CORN COUAG DDFA MTFAC1 RN
---- ----- ---------- ----------- --------------------
01 0309 14.12.2008 - 1
02 0309 28.08.2010 - 2
02 0309 16.06.2010 1283 3
01 0309 05.10.2008 2520 4
01 0309 15.10.2009 6452 5
更新:对于OLAP功能解决方案,如果您需要按其他列进行排序,最好使用RANK或DENSE_RANK函数:
SELECT CORN, COUAG, DDFA, MTFAC1
,RANK() OVER (ORDER BY MTFAC1 NULLS FIRST) RNK
FROM TV84
ORDER BY RNK;
CORN COUAG DDFA MTFAC1 RNK
---- ----- ---------- ----------- --------------------
01 0309 14.12.2008 - 1
02 0309 28.08.2010 - 1
02 0309 16.06.2010 1283 3
01 0309 05.10.2008 2520 4
01 0309 15.10.2009 6452 5