今天看到了一条用CASE WHEN排序的SQL语句,觉得很有用,所以作下笔记.
SQL> SELECT ORDSEQ,ITMNO,VERNO,COSTNO,CHGTY,PRD FROM ORDITMCOST WHERE RDSEQ = 3886;
ORDSEQ ITMNO VERNO COSTNO CHGTY PRD
---------------- ------ ------ ------ ----- ------------------------------
3886 1 1 1 FAB CVC-330G
3886 1 1 2 ACC ACC001
3886 1 1 3 ACC 4C11E1 -C/O BAHRAIN
3886 1 1 4 FAB FAB004
3886 1 1 5 ACC ACC004
3886 1 1 6 ACC RUBBER BAND
3886 1 1 7 ACC 4A 12B1
3886 1 1 8 ACC POLYBAG
3886 1 1 9 ACC Z-MAN5O
3886 2 1 1 FAB CHANDAR
3886 2 1 2 ACC 4A 12B1
3886 2 1 3 ACC BX-0001
12 rows selected
如果我想CHGTY为FAB的排在前面面俱到,CHGTY为ACC的排后面,CASE WHEN 就用得上了
SQL> SELECT ORDSEQ,ITMNO,VERNO,COSTNO,CHGTY,PRD FROM ORDITMCOST WHERE RDSEQ = 3886
2 ORDER BY CASE WHEN CHGTY = 'FAB' THEN CHGTY ELSE NULL END,
3 CASE WHEN CHGTY = 'ACC' THEN CHGTY ELSE NULL END,PRD
4 ;
ORDSEQ ITMNO VERNO COSTNO CHGTY PRD
---------------- ------ ------ ------ ----- ------------------------------
3886 2 1 1 FAB CHANDAR
3886 1 1 1 FAB CVC-330G
3886 1 1 4 FAB FAB004
3886 1 1 7 ACC 4A 12B1
3886 2 1 2 ACC 4A 12B1
3886 1 1 3 ACC 4C11E1 -C/O BAHRAIN
3886 1 1 2 ACC ACC001
3886 1 1 5 ACC ACC004
3886 2 1 3 ACC BX-0001
3886 1 1 8 ACC POLYBAG
3886 1 1 6 ACC RUBBER BAND
3886 1 1 9 ACC Z-MAN5O
12 rows selected
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13387766/viewspace-660914/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13387766/viewspace-660914/