主页 / dba / 问题 / 17081
Accepted
Alireza
Asked: 2012-04-26 23:43:22 +0800 CST

如何从有或没有列的表中获取计数（*）？

  • 772

我有一个帖子表如下： 

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）及其所有评论。怎么做？如何在获取帖子记录的同时从评论表中获取计数（*）？

count mysqli

2 个回答

  1. Best Answer

    这将获得所有帖子及其评论数

    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);
    • 2 
  2. SELECT p.*, (SELECT count(*) FROM Comment c WHERE c.p_ID = p.p_ID) CommentCount
FROM Post p WHERE p.p_ID = 8;

    在这种情况下，函数不是必需的，因为嵌套查询将始终返回一个数字，即使该数字为零。如果您想查看所有帐户的输出，只需删除该WHERE子句即可。

    • 2

相关问题