sql查询 case 进行已读未读消息的处理

消息分为系统消息与个人消息。

思路：消息统一发送，用户点击某条消息后生成点击记录，代表已读，通过消息点击记录来区分已读未读。

1、数据库结构

CREATE TABLE `sh_push` (`id` int(11) NOT NULL AUTO_INCREMENT,`type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0  系统消息   1 个人消息',`user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id  配合type类型等于1使用',`product_id` int(11) NOT NULL DEFAULT '0' COMMENT '产品ID',`name` varchar(100) NOT NULL COMMENT 'push名称',`describe` varchar(500) NOT NULL COMMENT '描述',`notice` varchar(500) NOT NULL DEFAULT '' COMMENT '通知',`is_send` enum('1','0') NOT NULL DEFAULT '0' COMMENT '发送状态 1为已发送 0为未发送',`state` enum('1','0') DEFAULT '1' COMMENT '状态 1显示 0隐藏',`send_time` varchar(50) DEFAULT '' COMMENT '最后一次发送时间',`create_time` datetime NOT NULL COMMENT '创建时间',`update_time` datetime NOT NULL COMMENT '修改时间',PRIMARY KEY (`id`),KEY `IDX_product_id` (`product_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `sh_push_click` (`id` int(11) NOT NULL AUTO_INCREMENT,`product_id` int(11) NOT NULL DEFAULT '0' COMMENT '产品ID',`channel_id` int(11) NOT NULL DEFAULT '0' COMMENT '渠道编号',`push_id` int(11) NOT NULL DEFAULT '0' COMMENT 'push编号',`member_id` int(11) NOT NULL DEFAULT '0' COMMENT '会员ID',`click_time` datetime NOT NULL COMMENT '点击时间',PRIMARY KEY (`id`),KEY `IDX_MEMBER_ID_PUSH_ID` (`member_id`,`push_id`) USING BTREE,KEY `IDX_MEMBER_ID_PRODUCT_ID` (`member_id`,`product_id`) USING BTREE,KEY `IDX_ME_PU_PR` (`member_id`,`push_id`,`product_id`) USING BTREE,KEY `push_id` (`push_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

sql查询语句

select id,name,type,user_id,case when id in(select push_id from sh_push_click where member_id = 1) then 1 else 0  end click_type from sh_push a where type = 0 or user_id = 1;

解释：case when 查询sh_push表中id 是否在记录表sh_push_click中，如果有值记为1 如果没用记为0 给定字段名为 click_type；