php - MySql - 选择两个具有不同限制的表

Question

我有两个这样的表:

我的漫画:

id  | link      | manganame     | viewed    
---------------------------------------------
1   | naruto    | naruto        | 11215     
2   | bleach    | bleach        | 32442     
3   | one piece | one_piece     | 11215     
4   | gents     | gents         | 4334      

om_章节:

id  | manganame     | chapter   | volume
-----------------------------------------
1   | naruto        | 1         | 1
2   | naruto        | 12        | 1
3   | naruto        | 22        | 1
4   | bleach        | 10        | 1
5   | bleach        | 15        | 1
6   | gents         | 1         | 1

我想要的是选择，例如，按查看的顺序从 om_manga 中选择 10 行，然后在两个表中选择 om_chapter 中具有相同漫画名的最后一行...

我想要的查询结果是这样的:

id  | manganame     | viewed    | chapter   | volume
----------------------------------------------------
1   | naruto        | 14511     | 22        | 1
2   | bleach        | 45151     | 15        | 1

这是我的 PHP 代码:

$query  = mysql_query("SELECT `link`,`cover`,`manganame` FROM `om_manga` WHERE `Active`='1' ORDER BY `viewed` DESC LIMIT ".$options['number_of_popular_manga']); //
while($row = mysql_fetch_array($query)){ 
    $rww = mysql_fetch_array(mysql_query("SELECT `chapter`,`volume`,`manganame` FROM `om_chapter` WHERE `manganame`='".sql_quote($row['link'])."' AND `Active`='1' ORDER BY `etime` DESC LIMIT 1")); //
    if (isset($rww['chapter'])) $volchap = '<a href="read.php?manga='.$row['link'].'&amp;chap='.$rww['chapter'].'">Vol.'.$rww['volume'].' CH.'.$rww['chapter'].'</a>'; else $volchap = '';
    echo '<li class="index-right-ul-li"><img onerror="this.src=\'images/noimage.jpg\'" src="images/info/'.$row['cover'].'" width="30" height="33"/><div class="index-right-ul-div"><p class="index-right-ul-div-tital"><a href="manga.php?manga='.$row['link'].'">'.htmlspecialchars($row['manganame'], ENT_QUOTES).'</a></p><p class="index-right-ul-div-chapter">'.$volchap.'</p></div></li>';
}

我想将此代码更改为一个查询而不是两个查询..

我该怎么做？

Answer 1

SELECT id,viewed,manganame,
    (SELECT chapter
    FROM om_chapter 
    WHERE om_manga.manganame=om_chapter.manganame
    ORDER BY chapter DESC
    LIMIT 0,1) AS chapter,
    (SELECT volume
    FROM om_chapter 
    WHERE om_manga.manganame=om_chapter.manganame
    ORDER BY chapter DESC
    LIMIT 0,1) AS volume
FROM om_manga
ORDER BY viewed DESC
LIMIT 0,10