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;
select * from attr_key ak
inner join attr_value av
on ak.id = av.attr_key_id
select * from attr_key ak
left join attr_value av
on ak.id = av.attr_key_id
inner into attr_key VALUES
('层高','2023-08-11 18:57:36','2023-08-11 18:57:36',0);
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;
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;