mysql多级like模糊匹配，根据关键字出现顺序排序

在数据库查询中，经常会用到根据用户输入的关键字，进行模糊查询，而且需要根据匹配的关键字，进行排序。这其中，一般还要和其它的排序条件进行组合。

数据库表中三列，name，order_num，description三个字段，要搜索name、description中包含“大”的数据，且要按照order_num降序排列，并且name要根据匹配度升序排列，如“大雁塔”要排在“乐山大佛前面”，最后再加一个分页。

在mysql中，我们都知道模糊查询用like，那where条件就很好写了--》where (t.name LIKE '%大%' OR t.description LIKE '%大%')，那么怎么根据order_num降序排列呢？

一开始本人将查询分为三部分，1：name like ‘大%’，2：name LIKE '%大%'，3：description LIKE '%大%'，最后用union连接起来，最后加上order by order_num，但是将order by加上去，查询又没有按照既定的顺序：name， order_num排列了。然后我又将语句改进，在每一个union中加入分页limit，这样乍一看还是那么回事，但还是有问题：当name中包含“大”，描述中没有“大”的时候，到最后一页时，由于nam中的分页过滤掉了union 1中那么匹配的结果，导致结果显示不完全。

 

就在准备放弃用存储过程+临时表的时候，想到了case when，皇天不负有心人，用一个小技巧，将name和description匹配结果做一个相加，然后再用order_num降序，结果完全正确。

 

CREATE TABLE `t_test` (
  `name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_num` int(11) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

INSERT INTO `t_test` VALUES ('水洞沟', '4', '中国史前考古发祥地');
INSERT INTO `t_test` VALUES ('镇北堡西部影视城', '5', '中国一绝，宁夏之宝');
INSERT INTO `t_test` VALUES ('贺兰山苏峪口国家森林公园', '4', '中温带干旱地区的森林生态旅游区');
INSERT INTO `t_test` VALUES ('西夏王陵', '4', '神秘的奇迹，东方金字塔');
INSERT INTO `t_test` VALUES ('贺兰山岩画', '4', '游牧民族的艺术画廊');
INSERT INTO `t_test` VALUES ('马步芳公馆景区', '4', '全国唯一一座选用玉石建造的官邸');
INSERT INTO `t_test` VALUES ('塔尔寺景区', '5', '传承藏传佛教文化，接受灵魂净化');
INSERT INTO `t_test` VALUES ('大通县老爷山风景名胜区', '4', '苍松蓊翳，石磴盘梯，川流萦带，风景佳丽');
INSERT INTO `t_test` VALUES ('泽普金湖杨景区', '5', '胡杨、水、绿洲、戈壁四位一体的美景');
INSERT INTO `t_test` VALUES ('太阳岛景区', '5', '质朴、粗犷的北方原野风光特色');
INSERT INTO `t_test` VALUES ('长影世纪城', '4', '中国第一家电影主题公园');
INSERT INTO `t_test` VALUES ('净月潭景区', '5', '亚洲第一大人工林海');
INSERT INTO `t_test` VALUES ('伪满皇宫博物院', '5', '日本帝国主义武力侵占中国东北的历史见证');
INSERT INTO `t_test` VALUES ('沈阳故宫博物馆', '4', '清代初期营建和使用的皇家宫苑');
INSERT INTO `t_test` VALUES ('张氏帅府博物馆', '4', '东北第一名人故居');
INSERT INTO `t_test` VALUES ('沈阳植物园', '5', '植物王国、游艺桥天地');
INSERT INTO `t_test` VALUES ('辽宁省博物馆', '4', '新中国第一座博物馆');
INSERT INTO `t_test` VALUES ('布达拉宫', '5', '世界屋脊上的明珠');
INSERT INTO `t_test` VALUES ('大昭寺', '5', '拉萨的中心，西藏的心脏');
INSERT INTO `t_test` VALUES ('西藏博物馆', '4', '西藏第一座具有现代化功能的博物馆');
INSERT INTO `t_test` VALUES ('四姑娘山', '4', '中国的阿尔卑斯山');
INSERT INTO `t_test` VALUES ('黄龙风景名胜区', '5', '中国唯一保护完好的高原湿地');
INSERT INTO `t_test` VALUES ('九寨沟旅游景区', '5', '中国最美的水景，美丽的童话世界');
INSERT INTO `t_test` VALUES ('汶川特别旅游区', '5', '世界汶川、水墨桃源');
INSERT INTO `t_test` VALUES ('崇圣寺三塔文化旅游区', '5', '万古云霄三塔影，诸天风雨一楼钟');
INSERT INTO `t_test` VALUES ('宾川鸡足山景区', '4', '云雾缭绕，气象万千，不是仙境胜似仙境');
INSERT INTO `t_test` VALUES ('百里杜鹃风景名胜', '5', '千峰叠起嶂，乌金地下埋。杜鹃花似海，满山留异香\r\n');
INSERT INTO `t_test` VALUES ('大足石刻景区', '5', '摩崖造像的石窟艺术');
INSERT INTO `t_test` VALUES ('武隆喀斯特旅游区', '5', '天上广寒宫，人间清暑殿');
INSERT INTO `t_test` VALUES ('黑山谷景区', '5', '中国最美养生峡谷');
INSERT INTO `t_test` VALUES ('酉阳桃花源旅游景区', '5', '洞前流水渺漫漫，洞里桃花渐渐残。曼倩不来渔父去，道人闲依石栏杆');
INSERT INTO `t_test` VALUES ('南川金佛山-神龙峡景区', '5', '山即是佛，佛即是山。');
INSERT INTO `t_test` VALUES ('巫山小三峡-小小三峡', '5', '且看九龙盘玉柱，更喜观音裹轻纱，赤壁摩天无觅处，天泉飞雨独一家 ');
INSERT INTO `t_test` VALUES ('天坛公园', '5', '日出日落三百六，周而复始从头来。草木枯荣分四时，一岁月有十二圆');

 

   完整的sql：

 

SELECT
	A.*
FROM
	(
		SELECT
			t.name,
			t.order_num,
			t.description
		FROM
			t_test AS t
		WHERE
			(
				t.name LIKE '%大%'
				OR t.description LIKE '%大%'
			)
		ORDER BY
			(
				(
					CASE
					WHEN t.name LIKE '大%' THEN
						3
					ELSE
						0
					END
				) + (
					CASE
					WHEN t.name LIKE '%大%' THEN
						1
					ELSE
						0
					END
				)
			) DESC,
			t.order_num DESC
	) A 
LIMIT 0, 4

 

 

 

评论

2 楼 serkeys 2014-11-17  

zhaohuakun 写道

楼主拷贝我的把！

那你的原创地址在哪儿呢？
别空口说白话，小心风大闪了舌头。

1 楼 zhaohuakun 2014-11-17  

楼主拷贝我的把！