mysql - 使用另一个表中的计数进行排序

Question

您好，我需要使用另一个表中的计数来订购。我发现了这个很好的例子，我将它用作我需要的查询的模型。 SQL - How To Order Using Count From Another Table

我用于查询的模型是:

SELECT bloggers.*, COUNT(post_id) AS post_count
    FROM bloggers LEFT JOIN blogger_posts 
    ON bloggers.blogger_id = blogger_posts.blogger_id
    GROUP BY bloggers.blogger_id
    ORDER BY post_count

但是我有一个语法问题，我想，我试图用计算另一个表的查询替换下一个查询......但我无法做到这一点。原始查询:

$res3=$db->execute_query("select id,scode,sname from ".TABLE_PREFIX."states where ccode=? order by sname asc",array($country));

尝试用此查询替换..

 $res3=$db->execute_query("select ".TABLE_PREFIX."states.* , COUNT(".TABLE_PREFIX."items.state) AS state_count FROM ".TABLE_PREFIX."states LEFT JOIN ".TABLE_PREFIX."items ON ".TABLE_PREFIX."states.id = ".TABLE_PREFIX."items.state GROUP BY ".TABLE_PREFIX."states.id  ORDER BY state_count DESC",array($country));

Answer 1

试试这个:

$res3=$db->execute_query("select a.* , COUNT(b.state) AS state_count FROM states a 
LEFT JOIN items b ON a.id = b.state 
GROUP BY a.id 
ORDER BY state_count DESC",array($country));