被遗忘的SQL

公司平台有个系统公告模块，展示的是管理员发布的通知和公告，数据库有公告和公告阅读记录这两张表，后者存有公告 ID 和用户 ID。现在前端需要两个接口：1、当前登录用户未读公告；2、当前登录用户下所有公告，并返回已读、未读标识。需求很简单，在此记录一下。

表结构

两表结构分别如下

CREATE TABLE `sys_notice` (
  `notice_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '公告ID',
  `notice_title` varchar(50) NOT NULL COMMENT '公告标题',
  `notice_type` char(1) NOT NULL COMMENT '公告类型（1通知 2公告）',
  `notice_content` longtext COMMENT '通知公告内容',
  `status` char(1) DEFAULT '0' COMMENT '公告状态（0正常 1关闭）',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`notice_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='通知公告表';

CREATE TABLE `sys_notice_read` (
  `read_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `notice_id` int(11) NOT NULL COMMENT '公告ID',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`read_id`) USING BTREE,
  KEY `read_id` (`read_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=utf8;

刚开始想的很简单，两表数据都查出来，然后 for 循环遍历判断：

// 公告列表
List<SysNoticeDto> noticeList = noticeService.getAllNoticeList();

// 公告阅读列表
List<SysNoticeRead> noticeReadList = noticeReadService.getReadNoticeByUserId(userId);

for(SysNoticeDto noticeDto : noticeList){
  for(SysNoticeRead noticeRead : noticeReadList){
	if(noticeDto.getNoticeId.equals(noticeRead.getNoticeId)){
	  noticeDto.setIsRead(true); // 已读
	}else{
	  noticeDto.setIsRead(false); // 未读
	}
  }
}

......

双重循环，代码不太优雅，遂捣鼓着用 SQL 来实现

接口一

查询当前登录用户未读公告：

SELECT
	notice_id,
	notice_title,
	notice_content,
	remark 
FROM
	sys_notice n 
WHERE
	NOT EXISTS ( SELECT r.notice_id FROM sys_notice_read r WHERE r.notice_id = n.notice_id AND r.user_id = #{userId} )

使用 NOT EXISTS，并在子查询中用 userId 过滤

接口二

当前登录用户所有公告，并标记已读、未读的状态

SELECT
	n.notice_id,
	n.notice_title,
	n.notice_content,
	n.remark,
CASE
	WHEN r.notice_id IS NULL 
	THEN 'false' ELSE 'true' 
	END AS isRead 
FROM
	sys_notice n
	LEFT JOIN ( SELECT notice_id FROM sys_notice_read WHERE user_id = #{userId} ) r ON n.notice_id = r.notice_id

先在子查询中用 userId 过滤已读公告，再用公告表 LEFT JOIN，然后 CASE WHEN 得到 isRead 标识字段。最终得到的记录数应该与公告表的记录数一致，写 SQL 时切记不要少了数据！

写在最后

这样写完后 Controller 代码瞬间简洁多了，直接返回查询结果。再不写写 SQL，自己都快忘了，要求高的话还可以把这 SQL 优化一下，不过目前的版本先这样了。年前几天，公司同事陆续回家了，我买的是 1 月 20 号晚 9 点的票，北京西，20 小时，硬座。。这应该是猪年发的最后一篇文章了，提前祝大家鼠年快乐！！！