php - 如何在一个查询中使用这两个选择查询

Question

$rget_comments  = mysql_query("SELECT DISTINCT comment.id, comment.comment_name, comment.comment_body FROM rs_comments AS comment WHERE comment.comment_parent ='0' ORDER BY comment.id ASC");
while( $get_comment = mysql_fetch_array( $rget_comments ) )
{

    echo '+ <a href="comment.php?id=' . $get_comment['id'] . '">' . $get_comment['comment_name'] . '</a><br />';
    echo $get_comment['body'] . '<br />';

    $rget_comments  = mysql_query("SELECT DISTINCT 
    (sub_comment.id) AS sub_id, (sub_comment.comment_name) AS sub_comment_name, (sub_comment.comment_body) AS sub_comment_body 
    FROM rs_comments AS sub_comment WHERE sub_comment ON sub_comment.comment_parent = '1'");
    while( $get_comment = mysql_fetch_array( $rget_comments ) )
    {

        echo '- <a href="comment.php?id=' . $get_comment['sub_id'] . '">' . $get_comment['sub_comment_name'] . '</a><br />';
        echo $get_comment['sub_body'] . '<br />';
    }

}

我的 table 信息:

CREATE TABLE IF NOT EXISTS `rs_comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comment_type` varchar(255) NOT NULL,
  `comment_post_ID` int(11) NOT NULL,
  `comment_parent` int(11) NOT NULL,
  `comment_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `comment_body` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `rs_comments` (`id`, `comment_type`, `comment_post_ID`, `comment_parent`, `comment_name`,`comment_body`) VALUES
(1, 'post', 1, 0, 'test comment', 'test comment body'),
(2, 'post', 1, 0, 'test comment 2', 'test comment 2 body'),
(3, 'post', 1, 1, 'sub comment 1', 'sub comment 1 body'),
(4, 'post', 1, 1, 'sub comment 2', 'sub comment 2 body');

我收到一个查询，但只会返回第一行:

  $rget_comments    = mysql_query("
    SELECT DISTINCT 
    comment.id, comment.comment_name, comment.comment_body, (sub_comment.id) AS sub_id, (sub_comment.comment_name) AS sub_comment_name, (sub_comment.comment_body) AS sub_comment_body
    FROM rs_comments AS comment
    LEFT OUTER JOIN rs_comments AS sub_comment ON sub_comment.comment_parent = comment.id
    WHERE  comment.comment_parent ='0' 
    GROUP BY comment.id
    ORDER BY comment.id ASC");
    while( $get_comment = mysql_fetch_array( $rget_comments ) )
    {
        echo '+ <a href="comment.php?id=' . $get_comment['id'] . '">' . $get_comment['comment_name'] . '</a><br />';
        echo $get_comment['body'] . '<br />';

        echo '- <a href="comment.php?id=' . $get_comment['sub_id'] . '">' . $get_comment['sub_comment_name'] . '</a><br />';
        echo $get_comment['sub_body'] . '<br />';
    }

结果:

+test comment
test comment 1 body
-sub comment 1
sub comment 1 body
+test comment 2
test comment 2 body

预期结果:

+test comment 
test comment 1 body 
-sub comment 1 
sub comment 1 body 
-sub comment 2
sub comment 2 body 
+test comment 2
test comment 2 body 

Answer 1

GROUP BY comment.id 会导致此问题。 GROUP BY 将具有相同 id 和不同 sub_id 的两条记录分组为一组。像这样删除它:

SQL:

SELECT DISTINCT 
    comment.id, comment.comment_name, comment.comment_body, (sub_comment.id) AS sub_id, (sub_comment.comment_name) AS sub_comment_name, (sub_comment.comment_body) AS sub_comment_body
    FROM rs_comments AS comment
      LEFT OUTER JOIN rs_comments AS sub_comment ON sub_comment.comment_parent = comment.id
    WHERE comment.comment_parent ='0' 
    ORDER BY comment.id ASC

PHP:

$flag = array();
while( $get_comment = mysql_fetch_array( $rget_comments ) ) {
  if(!isset($flag[$get_comment['id']])) {
      echo '+ <a href="comment.php?id=' . $get_comment['id'] . '">' . $get_comment['comment_name'] . '</a><br />';
      echo $get_comment['body'] . '<br />';
      $flag[$get_comment['id']] = true;
  }
  if($get_comment['sub_id']) {
      echo '- <a href="comment.php?id=' . $get_comment['sub_id'] . '">' . $get_comment['sub_comment_name'] . '</a><br />';
      echo $get_comment['sub_body'] . '<br />';
  }
}