php - SQL中如何获取日期最早、点赞数最多的评论ID？

Question

大家好，我在查询方面有点菜鸟。我有一个喜欢的评论系统。我想做的是获取点赞数最多的最早评论的ID。

这是我的 table 。

CREATE TABLE `wallcomments` (
  `c_id` int(11) NOT NULL,
  `parent_id` bigint(20) NOT NULL,
  `userid` int(11) NOT NULL,
  `comments` text NOT NULL,
  `date_created` int(11) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  `for_image` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'no=post, yes=imagePoPup',
  `clikes` mediumint(6) NOT NULL,
  `uip` char(30) NOT NULL,
  `tagedpersons` mediumtext NOT NULL,
  `comment_image` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

这是我的 PHP 函数strong text。

public function GetMostLovedComments($CommentPostID) 
{
    global $DBConnection;

    $cond = '';
    $cond = " AND wallcomments.parent_id = 0"; // only selects comments and not replies

    $query_pc = "SELECT c_id, MAX(clikes) AS max
                FROM wallcomments
                WHERE post_id = '.$CommentPostID. $cond .'"; 

    $result_pc = mysqli_query($DBConnection, $query_pc);
    $row_pc = mysqli_fetch_array($result_pc);
    $most_likes = $row_pc['max'];
    $cc_id = $row_pc['c_id'];

    $query = "SELECT date_created, MAX(date_created) AS min
                FROM wallcomments
                WHERE post_id = '.$CommentPostID. $cond .'";

    $result = mysqli_query($DBConnection, $query);
    $row = mysqli_fetch_array($result);
    $oldest_c = $row['min'];

    $query3 = "SELECT c_id
                FROM wallcomments
                WHERE date_created = '.$oldest_c'";

    $result3 = mysqli_query($DBConnection, $query3);
    $row3 = mysqli_fetch_array($result3);
    $com_id = $row3['c_id'];

    return  $oldest_c;
}

Answer 1

取决于你的意思...

返回日期最旧的墙评论的 C_ID，并返回点击次数最多的评论

SELECT wc.c_id 
FROM wallcomments wc
INNER JOIN (SELECT min(date_created) MDC
            FROM wallcomments
            WHERE post_id = YourConditionVariable) wc2
 on WC.date_created = wc2.mdc
WHERE post_id = YourConditionVariable
ORDER BY clikes desc
LIMIT 1 

当并列时返回点击次数最多的墙评论的 C_ID 返回创建日期最早的那个

SELECT wc.c_id 
FROM wallcomments wc
INNER JOIN (SELECT max(clikes) MDC
            FROM wallcomments
            WHERE post_id = YourConditionVariable) wc2
 on WC.clikes = wc2.mdc
WHERE post_id = YourConditionVariable
ORDER BY date_created asc
LIMIT 1