如何從有列或沒有列的表中獲取計數（*）？

我有一個如下表：

CREATE TABLE `post` (
 `p_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` smallint(5) unsigned NOT NULL,
 `cat_id` tinyint(3) unsigned NOT NULL,
 `view_count` smallint(5) unsigned NOT NULL DEFAULT '0',
 `status` tinyint(3) unsigned NOT NULL,
 `abstract` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
 `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
 `title` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `meta_description` tinytext CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
 `picture` varchar(100) NOT NULL,
 PRIMARY KEY (`p_id`),
 KEY `user_id` (`user_id`),
 KEY `date` (`date`),
 KEY `pic_id` (`picture`),
 KEY `cat_id` (`cat_id`),
 CONSTRAINT `post_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `karbar` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `post_ibfk_3` FOREIGN KEY (`cat_id`) REFERENCES `category` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

我有一個評論表如下：

CREATE TABLE `comment` (
 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `p_id` smallint(5) unsigned NOT NULL DEFAULT '0',
 `name` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
 `site` varchar(200) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
 `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `p_id` (`p_id`),
 CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `post` (`p_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

我想獲得一篇文章（例如 p_id=8）及其所有評論。怎麼做？獲取文章記錄時如何從評論表中獲取計數（*）？

這將獲得所有文章及其評論計數

SELECT p.*,IFNULL(c.comcount,0) comment_count
FROM post p LEFT JOIN
(SELECT p_id,COUNT(1) comcount FROM comment GROUP BY p_id) c
USING (p_id);

這將獲得 p_id=8 的文章及其評論數

SELECT p.*,IFNULL(c.comcount,0) comment_count
FROM (SELECT * FROM post WHERE p_id=8) p LEFT JOIN
(SELECT p_id,COUNT(1) comcount FROM comment WHERE p_id=8 GROUP BY p_id) c
USING (p_id);

SELECT p.*, (SELECT count(*) FROM Comment c WHERE c.p_ID = p.p_ID) CommentCount
FROM Post p WHERE p.p_ID = 8;

在這種情況下，不需要函式，因為嵌套查詢將始終返回一個數字，即使該數字為零。如果您想查看所有帳戶的輸出，只需刪除該WHERE子句。

引用自：https://dba.stackexchange.com/questions/17081