标签：sql

select *  from `dd` order by ELT(INTERVAL(CONV(HEX(left(CONVERT(num USING gbk),1)),16,10),

0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,

0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,

0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),

‘A‘,‘B‘,‘C‘,‘D‘,‘E‘,‘F‘,‘G‘,‘H‘,‘J‘,‘K‘,‘L‘,‘M‘,‘N‘,‘O‘,‘P‘,

‘Q‘,‘R‘,‘S‘,‘T‘,‘W‘,‘X‘,‘Y‘,‘Z‘) asc;

dd是表名，num是字段名

一按照表 jx_contents中的字段f_subject的拼音首字母排序

select * from jx_contents WHERE (f_type = ‘5‘ AND f_lang = ‘cn‘) GROUP BY binary f_subject ORDER BY f_id DESC LIMIT 0 , 8

二：按照首字母排序并且获取首字母

SELECT f_id, f_subject, ELT( INTERVAL( CONV( HEX( left( CONVERT( f_subject

USING gbk ) , 1  )  ) , 16, 10  ) , 0xB0A1, 0xB0C5, 0xB2C1, 0xB4EE, 0xB6EA, 0xB7A2, 0xB8C1, 0xB9FE, 0xBBF7, 0xBFA6, 0xC0AC, 0xC2E8, 0xC4C3, 0xC5B6, 0xC5BE, 0xC6DA, 0xC8BB, 0xC8F6, 0xCBFA, 0xCDDA, 0xCEF4, 0xD1B9, 0xD4D1  ) ,  ‘A‘,  ‘B‘,  ‘C‘,  ‘D‘,  ‘E‘,  ‘F‘,  ‘G‘,  ‘H‘,  ‘J‘,  ‘K‘,  ‘L‘,  ‘M‘,  ‘N‘,  ‘O‘,  ‘P‘,  ‘Q‘,  ‘R‘,  ‘S‘,  ‘T‘,  ‘W‘,  ‘X‘,  ‘Y‘,  ‘Z‘  )  AS PY

FROM jx_contents t

ORDER  BY f_id

LIMIT 0 , 30

标签：sql