情况是这样的,我想要一个MYSQL联合搜索的语句。返回页面的总搜索条数永远为10条。1:当搜索关键字返回结果大于10条时,全部返回结果显示为关键字搜索结果的前10条。
2:当搜索关键字返回结果小于10条时,不足的条数用其他方式补足,且关键字结果总是排列在最前面,后面跟着补足部分。补足部分的结果要求不和前面搜索部分的结果重复。比如:返回结果只有7条符合搜索关键字,那么前7条显示关键字搜索结果,后3条由数据库按时间排列最新、且不和前7条结果重复的数据进行补足。基础语句如下,如何进行UNION或者GROUP BY,达到我需要的要求?谢谢。(SELECT * FROM table WHERE WHERE MATCH (title,content) AGAINST ('+$boolean' IN BOOLEAN MODE) LIMIT 0,10 ORDER BY date)
UNION
(SELECT * FROM table limit 0,10 ORDER BY date)
limit 0,10 ORDER BY date
2:当搜索关键字返回结果小于10条时,不足的条数用其他方式补足,且关键字结果总是排列在最前面,后面跟着补足部分。补足部分的结果要求不和前面搜索部分的结果重复。比如:返回结果只有7条符合搜索关键字,那么前7条显示关键字搜索结果,后3条由数据库按时间排列最新、且不和前7条结果重复的数据进行补足。基础语句如下,如何进行UNION或者GROUP BY,达到我需要的要求?谢谢。(SELECT * FROM table WHERE WHERE MATCH (title,content) AGAINST ('+$boolean' IN BOOLEAN MODE) LIMIT 0,10 ORDER BY date)
UNION
(SELECT * FROM table limit 0,10 ORDER BY date)
limit 0,10 ORDER BY date
(SELECT *,1 AS BZ FROM table WHERE WHERE MATCH (title,content) AGAINST ('+$boolean' IN BOOLEAN MODE) ORDER BY date limit 0,10) b
UNION
(SELECT *,2 FROM table ORDER BY date limit 0,10) a) c
ORDER BY bz,date limit 0,10
from table
order by MATCH (title,content) AGAINST ('+$boolean' IN BOOLEAN MODE) desc
limi 10;
字段名:id owner link title content date且已经建立了fulltext操作 键名 类型 唯一 紧凑 字段 基数 整理 空 注释
PRIMARY BTREE 是 否 id 32466 A
index FULLTEXT 否 否 title 1
content 1
沙发的代码不通啊: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a) c ORDER BY bz,date limit 0,10' at line 1
select *
from table
order by MATCH (title,content) AGAINST ('+$boolean' IN BOOLEAN MODE) desc, date
limit 10;
复习了许多教程,自己东拼西凑,写了一个可以执行的语句。SELECT IF(m.id,1,0) AS has_match, t.* FROM `table` t LEFT JOIN `table` m ON m.id = t.id AND MATCH (m.title,m.content) AGAINST ('+$boolean' IN BOOLEAN MODE) GROUP BY t.id ORDER BY has_match DESC, date LIMIT 10帮忙分析一下有没有问题?还有效率如何?刚运行了一下,速度好像不快,32466条记录要2.3572秒。谢谢。
AS has_match, t.*
FROM `table` t
LEFT JOIN `table` m
ON m.id = t.id
AND MATCH (m.title,m.content)
AGAINST ('+$boolean' IN BOOLEAN MODE)
GROUP BY t.id
ORDER BY has_match DESC, date
LIMIT 10嗨:有人吗?帮忙看看,1楼代码为什么报错?You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a) c ORDER BY bz,date limit 0,10' at line 1
或者:我的代码有没有问题?
SELECT * FROM (SELECT *,1 AS BZ FROM `table` WHERE MATCH (title,content) AGAINST ('AA' IN BOOLEAN MODE) ORDER BY DATE LIMIT 0,10) b
UNION
SELECT * FROM (SELECT *,2 FROM `table` ORDER BY `date` LIMIT 0,10) a) c
ORDER BY bz,`date` LIMIT 0,10