【转】 oracle中英文分开排序

中英文分开排序

http://hi.baidu.com/flowerhacker/blog/item/dfb0961e383662154034174f.html

 

我同事问了个问题：是将中文和英文分开排序。是先排中文，再排英文的，都按照拼音abc那样排的；字段都是英文打头算作英文，中文打头算作中文。如果是先排英文的话，可以直接用NLSSORT(排序字段,'NLS_SORT = SCHINESE_PINYIN_M')进行排序。如果是先中文再英文的话。我用是先区分中英文，然后再排序：

SELECT *
FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT *
                  FROM T_GYS
                 WHERE UPPER(SUBSTR(MC, 1, 1)) NOT IN
                       ('A',
                        'B',
                        'C',
                        'D',
                        'E',
                        'F',
                        'G',
                        'H',
                        'I',
                        'J',
                        'K',
                        'L',
                        'M',
                        'N',
                        'O',
                        'P',
                        'Q',
                        'R',
                        'S',
                        'T',
                        'U',
                        'V',
                        'W',
                        'X',
                        'Y',
                        'Z')
                 ORDER BY NLSSORT(MC, 'NLS_SORT = SCHINESE_PINYIN_M') ASC) A
        UNION
        SELECT B.*,
               ROWNUM + (SELECT MAX(ROWNUM) RN
                           FROM T_GYS
                          WHERE UPPER(SUBSTR(MC, 1, 1)) NOT IN
                                ('A',
                                 'B',
                                 'C',
                                 'D',
                                 'E',
                                 'F',
                                 'G',
                                 'H',
                                 'I',
                                 'J',
                                 'K',
                                 'L',
                                 'M',
                                 'N',
                                 'O',
                                 'P',
                                 'Q',
                                 'R',
                                 'S',
                                 'T',
                                 'U',
                                 'V',
                                 'W',
                                 'X',
                                 'Y',
                                 'Z')) RN
          FROM (SELECT *
                  FROM T_GYS
                 WHERE UPPER(SUBSTR(MC, 1, 1)) IN
                       ('A',
                        'B',
                        'C',
                        'D',
                        'E',
                        'F',
                        'G',
                        'H',
                        'I',
                        'J',
                        'K',
                        'L',
                        'M',
                        'N',
                        'O',
                        'P',
                        'Q',
                        'R',
                        'S',
                        'T',
                        'U',
                        'V',
                        'W',
                        'X',
                        'Y',
                        'Z')
                 ORDER BY NLSSORT(MC, 'NLS_SORT = SCHINESE_PINYIN_M') ASC) B) T
ORDER BY T.RN ASC;