场景介绍
存在一个活动,比方指定的一些班级的班主任要参与活动somthing,有可能班级还没有班主任,所以,参与的状态有:未注册(-1),未参与(0),已参与(1),已完成(2)。
我们需要将参与表和班级表联结起来查询参与的状态。
查询语句
select a_class.id as class_id,
a_class.class_name,
a_class.class_teacher as teacher_user_id,
IF (COALESCE (a_class.class_teacher, 0) = 0, -1,
COALESCE (b_teacher_join_rec.join_status,0)) as join_status,
COALESCE (b_teacher_join_rec.id, 0 ) AS join_id
from a_class
inner join b_teacher_join_rec on a_class.id=b_teacher_join_rec.class_id
where a_class.id in (1,2,3) and b_teacher_join_rec.something_id="something_id_example"
COALESCE介绍
COALESCE函数语法:
COALESCE(value1,value2,...);
COALESCE函数需要许多参数,并返回第一个非NULL参数。如果所有参数都为NULL,则COALESCE函数返回NULL。
例子中有:
COALESCE (b_teacher_join_rec.id, 0 ) AS join_id
IF介绍
语法结构如下:
IF(expr,v1,v2)
其中:表达式 expr 得到不同的结果,当 expr为真是返回 v1 的值,否则返回 v2.
例子中有:
IF (COALESCE (a_class.class_teacher, 0) = 0, -1,
COALESCE (b_teacher_join_rec.join_status,0)) as join_status,
其中, expr是:
COALESCE (a_class.class_teacher, 0) = 0
v1是:-1
v2是:
COALESCE (b_teacher_join_rec.join_status,0)
表结构
老师的参与表
CREATE TABLE `b_teacher_join_rec` (
`id` BIGINT (20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`something_id` CHAR (36) DEFAULT NULL COMMENT '某id',
`class_id` CHAR (36) NOT NULL DEFAULT '' COMMENT '班级id',
`teacher_user_id` BIGINT (20) NOT NULL,
`join_status` TINYINT (4) DEFAULT NULL COMMENT '状态 0(未参与), 1 参与, 2 已完成',
PRIMARY KEY (`id`),
UNIQUE KEY `unique_idx` (
`something_id`,
`class_id`,
`teacher_user_id`
) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8mb4 COMMENT = '老师的参与情况';
班级表
CREATE TABLE `a_class` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '班级id',
`class_name` varchar(50) DEFAULT NULL COMMENT '班级名称',
`class_teacher` bigint(20) DEFAULT NULL COMMENT '班主任id,空则是未注册',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='班级表';