select * from citiaolist where (guanjianci LIKE '%东环路%') or (guanjianci LIKE '%拍照片%') order by ((CASE WHEN guanjianci LIKE '%东环路%' THEN 1 ELSE 0 END) + (CASE WHEN guanjianci LIKE '%拍照片%' THEN 1 ELSE 0 END)) deSC这个语句是可以实现了,, 有高手看看 有比这个更好的方案吗?>
传入的词汇的个数是不确定的,所以查询需要动态处理 一种可能的做法是$word = '东环路, 拍照片, 隐蔽'; //传入的词 $ar = preg_split('/[,\s]+/', $word); $s = "select '" . join("' as ch union select '", $ar) . "'";$sql = "select id, count(*) from tbl_name T1, ($s) T2 where find_in_set(T2.ch, T1.guanjianci) group by id order by id desc"; id count(*) C 3 B 2 A 1
select * from citiaolist where (guanjianci LIKE '%东环路%') or (guanjianci LIKE '%拍照片%') order by guanjianci LIKE '%东环路%' ,guanjianci LIKE '%拍照片%' desc
你的是可以實現,但多幾個詞就需要改程序了。建議多加一個tag表,和一個tag news的對應表 tag表 id tag 1 东环路 2 拍照片 3 隐蔽 4 ...tag news對應表 id newsid tagid來對應,這樣search方便。
一种可能的做法是$word = '东环路, 拍照片, 隐蔽'; //传入的词
$ar = preg_split('/[,\s]+/', $word);
$s = "select '" . join("' as ch union select '", $ar) . "'";$sql = "select id, count(*) from tbl_name T1, ($s) T2 where find_in_set(T2.ch, T1.guanjianci) group by id order by id desc";
id count(*)
C 3
B 2
A 1
tag表
id tag
1 东环路
2 拍照片
3 隐蔽
4 ...tag news對應表
id newsid tagid來對應,這樣search方便。