编辑代码

-- 创建数据库
CREATE DATABASE lease;
-- 指定数据库
use lease;
-- 创建表
CREATE TABLE attr_key (
  id bigint NOT NULL AUTO_INCREMENT,
  name varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性key',
  create_time timestamp NULL DEFAULT NULL COMMENT '创建时间',
  update_time timestamp NULL DEFAULT NULL COMMENT '更新时间',
  is_deleted tinyint DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='房间基本属性表';
-- 插入数据
INSERT INTO attr_key (name,create_time,update_time,is_deleted) VALUES
('面积','2023-06-19 01:43:37','2023-06-19 02:20:01',1),
('朝向','2023-06-19 02:06:12','2023-06-21 10:10:57',1),
('户型','2023-06-19 02:20:53','2023-06-19 02:31:14',1),
('户型','2023-06-19 02:36:20','2023-06-19 02:36:40',1),
('户型','2023-06-21 10:09:18',NULL,0),
('面积','2023-07-22 11:55:41','2023-07-22 11:58:31',1),
('面积','2023-07-22 11:58:50',NULL,0),
('朝向','2023-08-10 15:21:50','2023-08-10 15:22:04',0),
('采光','2023-08-10 18:46:45',NULL,0),
('卫所','2023-08-10 18:47:36','2023-08-14 00:11:57',0);
-- 创建表
CREATE TABLE attr_value (
  id bigint NOT NULL AUTO_INCREMENT,
  name varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性value',
  attr_key_id bigint DEFAULT NULL COMMENT '对应的属性key_id',
  create_time timestamp NULL DEFAULT NULL COMMENT '创建时间',
  update_time timestamp NULL DEFAULT NULL COMMENT '修改时间',
  is_deleted tinyint DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='房间基本属性值表';
-- 插入数据
INSERT INTO attr_value (name,attr_key_id,create_time,update_time,is_deleted) VALUES
('20平',1,'2023-06-19 01:44:17','2023-06-19 02:20:01',1),
('25平',1,'2023-06-19 01:44:23','2023-06-19 02:20:01',1),
('30平',1,'2023-06-19 01:44:28','2023-06-19 02:20:01',1),
('朝南',2,'2023-06-19 02:06:42',NULL,1),
('朝北',2,'2023-06-19 02:06:48',NULL,1),
('朝西',2,'2023-06-19 02:06:53','2023-06-21 10:10:46',1),
('朝东',2,'2023-06-19 02:06:58','2023-06-19 02:14:26',1),
('一室一厅',5,'2023-06-21 10:09:50',NULL,0),
('两室一厅',5,'2023-06-21 10:09:56',NULL,0),
('三室一厅',5,'2023-06-21 10:10:02',NULL,0),
('25平',6,'2023-07-22 11:55:58',NULL,1),
('30平',6,'2023-07-22 11:56:05',NULL,1),
('40平',6,'2023-07-22 11:56:11',NULL,1),
('20平',7,'2023-07-22 11:56:19',NULL,1),
('25平',7,'2023-07-22 11:58:58',NULL,0),
('35平',7,'2023-07-22 11:59:04',NULL,0),
('60平',7,'2023-07-22 11:59:11',NULL,0),
('80平',7,'2023-07-22 11:59:31',NULL,0),
('东',8,'2023-08-10 15:22:10',NULL,0),
('南',8,'2023-08-10 15:22:20',NULL,0),
('西',8,'2023-08-10 15:22:27',NULL,0),
('北',8,'2023-08-10 15:22:34',NULL,0),
('优',9,'2023-08-10 18:46:55',NULL,0),
('良',9,'2023-08-10 18:47:04',NULL,0),
('一般',9,'2023-08-10 18:47:14',NULL,0),
('无',9,'2023-08-10 18:47:19',NULL,0),
('独卫',10,'2023-08-10 18:47:46',NULL,0),
('公共',10,'2023-08-10 18:47:51',NULL,0);

select * from attr_key;
select * from attr_value;
-- 以上数据初始化完毕

-- 使用 inner join 关联主子表存在的问题
select * from attr_key ak
inner join attr_value av 
on ak.id = av.attr_key_id
-- 1.作为主表的 attr_key 因为用的是 inner join 和子表 attr_value 做的关联导致左表丢数据了,因为 inner join 的特性就是左表右表完全匹配才会生成最终的一条结果

select * from attr_key ak
left join attr_value av 
on ak.id = av.attr_key_id
-- 用 left join 保证主表不会丢数据,保证了数据的真实性

-- 使用 left join 怎么写过滤条件
-- 我们先来在 attr_key 表中增加一条层高的数据
inner into attr_key VALUES
 ('层高','2023-08-11 18:57:36','2023-08-11 18:57:36',0);

-- 将主子表的条件都写在 where 语句中
select * from attr_key ak
left join attr_value av 
on ak.id = av.attr_key_id
where ak.is_deleted = 0 and av.is_deleted = 0;
-- 存在的问题
-- 层高的数据又消失了?因为 where 语句中表明我要将拼接好的数据根据过滤出主表未删除的数据和子表未删除的数据
-- 问题是层高作为新增加的主表的数据,必然没有子表会与之对应,所以子表的 is_deleted 必然为 null
-- 所以拼接好的层高数据就被过滤掉了

-- 将主表的过滤条件写在 where 语句中
-- 将子表的过滤条件写在 on 语句中
select * from attr_key ak
left join attr_value av 
on ak.id = av.attr_key_id and av.is_deleted = 0
where ak.is_deleted = 0;
-- 当表都采用逻辑删除策列时,如果两个表采用left join 链接时,
-- where 的过滤条件中只能包含左表的过滤条件
-- 右表的过滤条件放在 on 的链接条件上