php - MySQL:获取每个选定行的相关结果

Question

我正在使用下面的基本选择查询来显示表中最新的 20 行。

SELECT * FROM my_table  ORDER BY id DESC limit 0,20

然后我想获得上面选择的每一行的相关结果...我当前正在做的是在“php”中为循环中的每一行运行以下查询..

SELECT * MATCH (title)
AGAINST('$main_title'IN NATURAL LANGUAGE MODE)AS 
score FROM my_table  MATCH(title)  AGAINST('$main_title'IN NATURAL LANGUAGE MODE)
HAVING score > 10  ORDER BY score DESC

所以完整的“PHP”代码如下所示:

<?php
// First Query 
$query= mysql_query("SELECT * FROM my_table  ORDER BY id DESC limit 0,20");  
$result = mysql_query($query) 

while($row = mysql_fetch_array($result)){
echo "Main Topic: ".$row['title'];
$main_title = $row['title'];
    echo "Related to this Topic:";

   // Second Query 
    $related_query= mysql_query("
    SELECT * MATCH (title)
    AGAINST('$main_title'IN NATURAL LANGUAGE MODE)AS 
    score FROM my_table  MATCH(title)  AGAINST('$main_title'IN NATURAL LANGUAGE MODE)
    HAVING score > 10  ORDER BY score DESC
    ");  
    $related_result = mysql_query($related_query) 

            while($related_row = mysql_fetch_array($related_result)){
            echo  "<br>". $related_row['title'];
    }


}
?>

但我知道在循环中运行第二个查询效率非常低，所以我想在主查询中也获得这些“相关结果”(或者在单独的单个查询中，而不必在循环中运行它)循环)

基本上我正在寻找一种更有效地执行相同操作的方法..

有什么办法可以做到这一点吗？

谢谢

Answer 1

假设你只想要标题和分数，这样可以吗？

SELECT MATCH(title) AGAINST('$main_title' IN NATURAL LANGUAGE MODE) as SCORE,
title FROM my_table WHERE  
MATCH(title) AGAINST('$main_title' IN NATURAL LANGUAGE MODE)
AND score > 10 AND id<=20 ORDER BY score DESC;