我有一个这样的全文查询,它得到了我期望的结果
SELECT id,'', 'blogs' as mytable,title, content,
MATCH(title) AGAINST('keywords*' IN BOOLEAN MODE) * 8 +
MATCH(content) AGAINST('keywords*' IN BOOLEAN MODE) * 4
as score FROM blogs WHERE archived = 'N' AND MATCH(title, content)
AGAINST('keywords*' IN BOOLEAN MODE)
ORDER BY score DESC, id DESC
如果我搜索一个特定的关键字,我会得到一个ID为4的结果。 问题是我需要额外的列,以便我可以搜索该 ID 以获取该 ID 的行位置。
如果我自己运行此查询,我会得到正确的结果,即 8
SELECT position FROM
(
SELECT
id, @rownum:=@rownum+1 position, archived
FROM blogs, (SELECT @rownum:=0) r WHERE archived ='N'
ORDER BY id DESC
)
AS position
WHERE id = 4
我想做的是将这些查询合并到一个查询中,以便第一个查询的 ID 传递到第二个查询的 WHERE 中,这样我的附加列"position"的值将为 8
我试图组合查询,但这绝对是不对的
像这样的东西?
SELECT position FROM
(
SELECT
id, @rownum:=@rownum+1 position, archived
FROM blogs, (SELECT @rownum:=0) r WHERE archived ='N'
ORDER BY id DESC
)
AS position
WHERE id IN (SELECT id FROM(SELECT id,'', 'blogs' as mytable,title, content,
MATCH(title) AGAINST('keywords*' IN BOOLEAN MODE) * 8 +
MATCH(content) AGAINST('keywords*' IN BOOLEAN MODE) * 4
as score FROM blogs WHERE archived = 'N' AND MATCH(title, content)
AGAINST('keywords*' IN BOOLEAN MODE)
ORDER BY score DESC, id DESC)x)