tatle1 ,table2 结构相同,分别储存2种不同类型的产品。
id | title | model | description | date | fullindex有这样一个mysql两表联合全文分页查询的语句。(
select 'a' as tablename,*
from table1
where MATCH (fullindex)
AGAINST ('".mysql_real_escape_string($bool)."' IN BOOLEAN MODE)
)
UNIONE
(
select 'b' as tablename,*
from table2
where MATCH (fullindex)
AGAINST ('".mysql_real_escape_string($bool)."' IN BOOLEAN MODE)
)
Order By date DESC
limit ".$_POST['number'].",10
现在要加一个功能,即结果中相同名字的model,最多只能出现5条。(group by model的话,相同名字的model只能出现一次)高手有什么方法吗?最好效率高一点,PS: fullindex加了全文索引,model已经加了普通索引。
id | title | model | description | date | fullindex有这样一个mysql两表联合全文分页查询的语句。(
select 'a' as tablename,*
from table1
where MATCH (fullindex)
AGAINST ('".mysql_real_escape_string($bool)."' IN BOOLEAN MODE)
)
UNIONE
(
select 'b' as tablename,*
from table2
where MATCH (fullindex)
AGAINST ('".mysql_real_escape_string($bool)."' IN BOOLEAN MODE)
)
Order By date DESC
limit ".$_POST['number'].",10
现在要加一个功能,即结果中相同名字的model,最多只能出现5条。(group by model的话,相同名字的model只能出现一次)高手有什么方法吗?最好效率高一点,PS: fullindex加了全文索引,model已经加了普通索引。
[征集]分组取最大N条记录方法征集,及散分....
SELECT * FROM VIEW1 A1 WHERE 5>(SELECT COUNT(*) FROM VIEW1 WHERE A1.model=model AND
A1.ID<=ID
版主,你确信你的博文就是我要的效果?http://stackoverflow.com/questions/4775820/sql-give-me-3-hits-for-each-type-only
这个代码有点像我要的要求,即每种type最大只返回3个值。
不过那么代码有点看不懂。@是防止报错,那么 := 代表什么?
老大去掉UNION,先理简单的单表查询,一个表大概有30万条记录,单纯fulltext搜索,只要0.13秒,用了
SELECT 'a' as t_name,id,title,model,description,date,fullindex
FROM table1 a1
WHERE MATCH (a1.fullindex) AGAINST ('+bool' IN BOOLEAN MODE) and 6>(select count(*) from table1 where source=a1.source and date>a1.date) 要花费7.6秒
想尝试LEFT JOIN HAVING COUNT的方法,但是返回数值是0.
SELECT 'a' as t_name,a1.id,a1.title,a1.model,a1.description,a1.date,a1.fullindex
FROM table1 a1
LEFT JOIN table1 b1
ON a1.id=b1.id AND a1.date<=b1.date
HAVING COUNT(b1.source)<=5
WHERE MATCH (a1.fullindex) AGAINST ('+bool' IN BOOLEAN MODE)
ORDER BY a1.date desc
和版主的博文相去甚远。
没有满意答案。