mysql-统计指定用户未读消息数目

最新推荐文章于 2024-12-05 08:44:25 发布

大脸猫脸大

最新推荐文章于 2024-12-05 08:44:25 发布

阅读量2k

点赞数

分类专栏：数据库文章标签： mysql 虚表

本文链接：https://blog.youkuaiyun.com/sfmcatl/article/details/79312856

版权

数据库专栏收录该内容

14 篇文章

订阅专栏

1. 消息数据表结构如下：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `sp_message`
-- ----------------------------
DROP TABLE IF EXISTS `sp_message`;
CREATE TABLE `sp_message` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '唯一ID',
  `user_id` int(11) NOT NULL COMMENT '接收信息用户UID',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1为未读，0为已读',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2384 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sp_message
-- ----------------------------
INSERT INTO `sp_message` VALUES ('2059', '780', '0');
INSERT INTO `sp_message` VALUES ('2217', '770', '1');
INSERT INTO `sp_message` VALUES ('2061', '770', '1');
INSERT INTO `sp_message` VALUES ('2062', '780', '0');
INSERT INTO `sp_message` VALUES ('1868', '770', '0');
INSERT INTO `sp_message` VALUES ('1875', '770', '0');
INSERT INTO `sp_message` VALUES ('1952', '770', '1');
INSERT INTO `sp_message` VALUES ('2097', '770', '0');
INSERT INTO `sp_message` VALUES ('1973', '770', '1');
INSERT INTO `sp_message` VALUES ('1944', '770', '1');
INSERT INTO `sp_message` VALUES ('2069', '780', '0');
INSERT INTO `sp_message` VALUES ('2245', '770', '1');
INSERT INTO `sp_message` VALUES ('1976', '770', '1');
INSERT INTO `sp_message` VALUES ('1918', '770', '1');
INSERT INTO `sp_message` VALUES ('1960', '770', '1');
INSERT INTO `sp_message` VALUES ('1931', '770', '1');
INSERT INTO `sp_message` VALUES ('1989', '770', '1');
INSERT INTO `sp_message` VALUES ('2049', '780', '0');
INSERT INTO `sp_message` VALUES ('2210', '770', '1');
INSERT INTO `sp_message` VALUES ('2000', '770', '1');
INSERT INTO `sp_message` VALUES ('2002', '770', '1');
INSERT INTO `sp_message` VALUES ('2051', '770', '0');
INSERT INTO `sp_message` VALUES ('2014', '770', '1');
INSERT INTO `sp_message` VALUES ('2019', '770', '1');
INSERT INTO `sp_message` VALUES ('2029', '770', '0');
INSERT INTO `sp_message` VALUES ('2098', '780', '0');
INSERT INTO `sp_message` VALUES ('2038', '770', '0');
INSERT INTO `sp_message` VALUES ('2267', '770', '0');
INSERT INTO `sp_message` VALUES ('2139', '770', '0');
INSERT INTO `sp_message` VALUES ('2140', '780', '0');
INSERT INTO `sp_message` VALUES ('2221', '770', '0');
INSERT INTO `sp_message` VALUES ('2230', '780', '0');
INSERT INTO `sp_message` VALUES ('2239', '770', '0');
INSERT INTO `sp_message` VALUES ('2253', '770', '1');
INSERT INTO `sp_message` VALUES ('2262', '770', '0');
INSERT INTO `sp_message` VALUES ('2278', '770', '1');
INSERT INTO `sp_message` VALUES ('2285', '770', '0');
INSERT INTO `sp_message` VALUES ('2294', '770', '1');
INSERT INTO `sp_message` VALUES ('2302', '780', '0');
INSERT INTO `sp_message` VALUES ('2309', '770', '0');
INSERT INTO `sp_message` VALUES ('2327', '770', '0');
INSERT INTO `sp_message` VALUES ('2339', '770', '0');

想统计770,780的未读消息数目：

SELECT user_id, count(1) as count from sp_message where user_id in (780,770) and `status`= 1 group by user_id;

结果显示：

没有780用户的统计信息；

建立虚表：

SELECT C.user_id, IFNULL(b.count, 0) as count 
FROM ( SELECT A.user_id FROM sp_message AS A WHERE A.user_id IN (780, 770) GROUP BY user_id ) AS C 
LEFT JOIN ( SELECT user_id, count(1) AS count FROM sp_message WHERE user_id IN (780, 770) AND STATUS = 1 GROUP BY user_id ) AS B 
ON C.user_id = B.user_id

结果显示：