背景:公司要求根据名字【汉字】的首字母排序
测试sql如图
sql:
SELECT
c.brand,
c.uppercase,
c.lowercase
FROM
(
SELECT
*,
CASE
WHEN LENGTH( LEFT ( brand, 1 ) ) > 1 THEN
CHAR (
INTERVAL (
CONV( HEX( CONVERT ( LEFT ( brand, 1 ) USING gbk ) ), 16, 10 ),
0 xB0A1,
0 xB0C5,
0 xB2C1,
0 xB4EE,
0 xB6EA,
0 xB7A2,
0 xB8C1,
0 xB9FE,
0 xBBF7,
0 xBBF7,
0 xBFA6,
0 xC0AC,
0 xC2E8,
0 xC4C3,
0 xC5B6,
0 xC5BE,
0 xC6DA,
0 xC8BB,
0 xC8F6,
0 xCBFA,
0 xCDDA,
0 xCDDA,
0 xCDDA,
0 xCEF4,
0 xD1B9,
0 xD4D1
) + 64
) ELSE LEFT ( brand, 1 )
END AS uppercase,
CASE
WHEN LENGTH( LEFT ( brand, 1 ) ) > 1 THEN
CHAR (
INTERVAL (
CONV( HEX( CONVERT ( LEFT ( brand, 1 ) USING gbk ) ), 16, 10 ),
0 xB0A1,
0 xB0C5,
0 xB2C1,
0 xB4EE,
0 xB6EA,
0 xB7A2,
0 xB8C1,
0 xB9FE,
0 xBBF7,
0 xBBF7,
0 xBFA6,
0 xC0AC,
0 xC2E8,
0 xC4C3,
0 xC5B6,
0 xC5BE,
0 xC6DA,
0 xC8BB,
0 xC8F6,
0 xCBFA,
0 xCDDA,
0 xCDDA,
0 xCDDA,
0 xCEF4,
0 xD1B9,
0 xD4D1
) + 96
) ELSE LEFT ( brand, 1 )
END AS lowercase
FROM
product
) c
WHERE
c.state = 1
GROUP BY
brand