Mysql-按照首字母查询

最新推荐文章于 2022-11-24 17:16:26 发布

chouzhangci9262

最新推荐文章于 2022-11-24 17:16:26 发布

阅读量640

点赞数 2

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://my.oschina.net/easy3399/blog/3024943

背景：公司要求根据名字【汉字】的首字母排序

测试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

转载于:https://my.oschina.net/easy3399/blog/3024943