cykj/hzdyjs
1
0 Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
main
hzdyjs/宿舍信息同步到数据库中/批量创建宿舍房间的sql.md
T
zlwu 33d75d6d1b 提交
2026-05-07 13:44:48 +08:00

1200 lines
59 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# 目标
学校生产数据库批量创建宿舍房间，请输出插入数据的sql，务必要把每个字段都填写完整
# 学校现状
校区数据
INSERT INTO `hzdyjs_xg`.`xg_dorm_campus`(`id`, `campus_code`, `campus_name`, `campus_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1946815553291304962, 'xixi', '西溪校区', '西溪校区', 0, '0', 0, 1, 103, '2025-07-20 14:12:53', 1, '2025-07-20 14:12:53', '西溪校区');
INSERT INTO `hzdyjs_xg`.`xg_dorm_campus`(`id`, `campus_code`, `campus_name`, `campus_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1946815834653605889, 'laxq', '临安校区', '临安校区', 1, '0', 0, 1, 103, '2025-07-20 14:14:00', 1, '2025-07-20 14:14:00', '临安校区');
宿舍区默认的数据
INSERT INTO `hzdyjs_xg`.`xg_dorm_area`(`id`, `area_code`, `area_name`, `campus_id`, `area_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2015638219126755329, '0000', '临安默认宿舍区', 1946815834653605889, '默认宿舍区', 1, '0', 0, 1, 100, '2026-01-26 12:09:35', 1, '2026-01-26 12:16:16', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_area`(`id`, `area_code`, `area_name`, `campus_id`, `area_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2015639946668298241, '12313', '西溪默认宿舍区', 1946815553291304962, '123', 123, '0', 0, 1, 100, '2026-01-26 12:16:27', 1, '2026-01-26 12:16:27', NULL);
宿舍楼数据
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048578056012025858, 'xx-001', '西溪1号楼', 2015639946668298241, 1946815553291304962, 4, '2', '女生宿舍楼', 1, '0', 0, 1, 100, '2026-04-27 09:40:44', 1, '2026-04-27 09:55:07', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048578302309945345, 'xx-002', '西溪2号楼', 2015639946668298241, 1946815553291304962, 4, '2', '女生楼', 2, '0', 0, 1, 100, '2026-04-27 09:41:43', 1, '2026-04-27 09:54:54', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048580084373245954, 'xx-003', '西溪3号楼', 2015639946668298241, 1946815553291304962, 4, '1', '男生楼', 3, '0', 0, 1, 100, '2026-04-27 09:48:48', 1, '2026-04-27 09:54:44', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048580269354635265, 'xx-004', '西溪4号楼', 2015639946668298241, 1946815553291304962, 4, '1', '男生楼', 4, '0', 0, 1, 100, '2026-04-27 09:49:32', 1, '2026-04-27 09:54:33', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048580537001562113, 'xx-005', '西溪5号楼', 2015639946668298241, 1946815553291304962, 4, '2', '女生楼', 5, '0', 0, 1, 100, '2026-04-27 09:50:35', 1, '2026-04-27 09:54:21', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048580670791471106, 'xx-006', '西溪6号楼', 2015639946668298241, 1946815553291304962, 4, '2', '女生楼', 6, '0', 0, 1, 100, '2026-04-27 09:51:07', 1, '2026-04-27 09:54:11', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048581868017799170, 'la-001', '临安5号楼', 2015638219126755329, 1946815834653605889, 5, '1', '男生楼', 7, '0', 0, 1, 100, '2026-04-27 09:55:53', 1, '2026-04-27 09:55:53', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048581964566482945, 'la-002', '临安6号楼', 2015638219126755329, 1946815834653605889, 5, '1', '男生楼', 8, '0', 0, 1, 100, '2026-04-27 09:56:16', 1, '2026-04-27 09:56:16', NULL);
INSERT INTO `hzdyjs_xg`.`xg_dorm_building`(`id`, `building_code`, `building_name`, `area_id`, `campus_id`, `floors`, `building_type`, `building_description`, `sort_order`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2048582084469051394, 'la-003', '临安7号楼', 2015638219126755329, 1946815834653605889, 6, '2', '女生楼', 9, '0', 0, 1, 100, '2026-04-27 09:56:44', 1, '2026-04-27 09:56:44', NULL);
# 西溪1号楼 的信息
- 1层：1101寝室、1102寝室、1103寝室、1104寝室、1105寝室、1106寝室、1107寝室、1108寝室、1109寝室、1110寝室、1111寝室、1112寝室、1113寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 2层：1201寝室、1202寝室、1203寝室、1204寝室、1205寝室、1206寝室、1207寝室、1208寝室、1209寝室、1210寝室、1211寝室、1212寝室、1213寝室、1215寝室、
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 3层：1301寝室、1302寝室、1303寝室、1304寝室、1305寝室、1306寝室、1307寝室、1308寝室、1309寝室、1310寝室、1311寝室、1312寝室、1313寝室、1315寝室、
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 4层：1401寝室、1402寝室、1403寝室、1404寝室、1405寝室、1406寝室、1407寝室、1408寝室、1409寝室、1410寝室、1411寝室、1412寝室、1413寝室、1415寝室、
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 西溪4号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 西溪4号楼基础信息
-- building_id = 2048580269354635265
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
-- MySQL 5.7兼容版：不用WITH / ROW_NUMBER
-- 注意：严格按清单生成，房间号非连续，不做自动补齐
DROP TEMPORARY TABLE IF EXISTS tmp_xx4_room_data;
CREATE TEMPORARY TABLE tmp_xx4_room_data (
floor_num INT NOT NULL,
seq INT NOT NULL,
room_code VARCHAR(10) NOT NULL,
PRIMARY KEY (floor_num, seq)
) ENGINE=MEMORY;
INSERT INTO tmp_xx4_room_data (floor_num, seq, room_code) VALUES
-- 1层（25间）
(1,1,'4101'),(1,2,'4102'),(1,3,'4103'),(1,4,'4104'),(1,5,'4105'),
(1,6,'4106'),(1,7,'4107'),(1,8,'4108'),(1,9,'4109'),(1,10,'4110'),
(1,11,'4111'),(1,12,'4112'),(1,13,'4113'),(1,14,'4114'),(1,15,'4115'),
(1,16,'4116'),(1,17,'4117'),(1,18,'4118'),(1,19,'4119'),(1,20,'4120'),
(1,21,'4121'),(1,22,'4122'),(1,23,'4123'),(1,24,'4125'),(1,25,'4127'),
-- 2层（26间）
(2,1,'4201'),(2,2,'4202'),(2,3,'4203'),(2,4,'4204'),(2,5,'4205'),
(2,6,'4206'),(2,7,'4207'),(2,8,'4208'),(2,9,'4209'),(2,10,'4210'),
(2,11,'4211'),(2,12,'4212'),(2,13,'4213'),(2,14,'4214'),(2,15,'4215'),
(2,16,'4216'),(2,17,'4217'),(2,18,'4218'),(2,19,'4219'),(2,20,'4220'),
(2,21,'4221'),(2,22,'4222'),(2,23,'4223'),(2,24,'4225'),(2,25,'4227'),
(2,26,'4229'),
-- 3层（26间）
(3,1,'4301'),(3,2,'4302'),(3,3,'4303'),(3,4,'4304'),(3,5,'4305'),
(3,6,'4306'),(3,7,'4307'),(3,8,'4308'),(3,9,'4309'),(3,10,'4310'),
(3,11,'4311'),(3,12,'4312'),(3,13,'4313'),(3,14,'4314'),(3,15,'4315'),
(3,16,'4316'),(3,17,'4317'),(3,18,'4318'),(3,19,'4319'),(3,20,'4320'),
(3,21,'4321'),(3,22,'4322'),(3,23,'4323'),(3,24,'4325'),(3,25,'4327'),
(3,26,'4329'),
-- 4层（26间）
(4,1,'4401'),(4,2,'4402'),(4,3,'4403'),(4,4,'4404'),(4,5,'4405'),
(4,6,'4406'),(4,7,'4407'),(4,8,'4408'),(4,9,'4409'),(4,10,'4410'),
(4,11,'4411'),(4,12,'4412'),(4,13,'4413'),(4,14,'4414'),(4,15,'4415'),
(4,16,'4416'),(4,17,'4417'),(4,18,'4418'),(4,19,'4419'),(4,20,'4420'),
(4,21,'4421'),(4,22,'4422'),(4,23,'4423'),(4,24,'4425'),(4,25,'4427'),
(4,26,'4429');
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2053600000000000000 + floor_num * 100 + seq AS id,
room_code,
CONCAT(room_code, '寝室') AS room_name,
2048580269354635265 AS building_id,
floor_num,
'1' AS room_type,
8 AS actual_beds,
32.00 AS room_area,
'0' AS room_status,
seq AS sort,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:30:00' AS create_time,
1 AS update_by,
'2026-04-27 11:30:00' AS update_time,
NULL AS remark
FROM tmp_xx4_room_data;
-- 2) 批量插入床位（xg_dorm_bed）
-- 当前提示词配置：每个寝室8个床位（1上、1下、2上、2下、3上、3下、4上、4下）
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2053700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT(r.room_code, '-', b.bed_code_suffix) AS bed_code,
b.bed_name,
2053600000000000000 + r.floor_num * 100 + r.seq AS room_id,
b.bed_type,
'0' AS bed_status,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:30:00' AS create_time,
1 AS update_by,
'2026-04-27 11:30:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM tmp_xx4_room_data r
CROSS JOIN (
SELECT 1 AS bed_sort, '1S' AS bed_code_suffix, '1上' AS bed_name, '2' AS bed_type UNION ALL
SELECT 2, '1X', '1下', '1' UNION ALL
SELECT 3, '2S', '2上', '2' UNION ALL
SELECT 4, '2X', '2下', '1' UNION ALL
SELECT 5, '3S', '3上', '2' UNION ALL
SELECT 6, '3X', '3下', '1' UNION ALL
SELECT 7, '4S', '4上', '2' UNION ALL
SELECT 8, '4X', '4下', '1'
) b;
DROP TEMPORARY TABLE IF EXISTS tmp_xx4_room_data;
COMMIT;
-- 预期数据量：
-- 房间：103条（1层25 + 2层26 + 3层26 + 4层26）
-- 床位：824条（103 * 8）
```
# 西溪3号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 西溪3号楼基础信息
-- building_id = 2048580084373245954
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
-- MySQL 5.7兼容版：不用WITH / ROW_NUMBER
-- 注意：严格按清单生成，房间号非连续，不做自动补齐
DROP TEMPORARY TABLE IF EXISTS tmp_xx3_room_data;
CREATE TEMPORARY TABLE tmp_xx3_room_data (
floor_num INT NOT NULL,
seq INT NOT NULL,
room_code VARCHAR(10) NOT NULL,
PRIMARY KEY (floor_num, seq)
) ENGINE=MEMORY;
INSERT INTO tmp_xx3_room_data (floor_num, seq, room_code) VALUES
-- 1层（30间）
(1,1,'3101'),(1,2,'3102'),(1,3,'3103'),(1,4,'3104'),(1,5,'3105'),
(1,6,'3106'),(1,7,'3107'),(1,8,'3108'),(1,9,'3109'),(1,10,'3110'),
(1,11,'3111'),(1,12,'3112'),(1,13,'3113'),(1,14,'3114'),(1,15,'3115'),
(1,16,'3116'),(1,17,'3117'),(1,18,'3118'),(1,19,'3119'),(1,20,'3120'),
(1,21,'3121'),(1,22,'3122'),(1,23,'3123'),(1,24,'3124'),(1,25,'3125'),
(1,26,'3126'),(1,27,'3127'),(1,28,'3128'),(1,29,'3129'),(1,30,'3131'),
-- 2层（31间）
(2,1,'3201'),(2,2,'3202'),(2,3,'3203'),(2,4,'3204'),(2,5,'3205'),
(2,6,'3206'),(2,7,'3207'),(2,8,'3208'),(2,9,'3209'),(2,10,'3210'),
(2,11,'3211'),(2,12,'3212'),(2,13,'3213'),(2,14,'3214'),(2,15,'3215'),
(2,16,'3216'),(2,17,'3217'),(2,18,'3218'),(2,19,'3219'),(2,20,'3220'),
(2,21,'3221'),(2,22,'3222'),(2,23,'3223'),(2,24,'3224'),(2,25,'3225'),
(2,26,'3226'),(2,27,'3227'),(2,28,'3228'),(2,29,'3229'),(2,30,'3231'),
(2,31,'3233'),
-- 3层（31间）
(3,1,'3301'),(3,2,'3302'),(3,3,'3303'),(3,4,'3304'),(3,5,'3305'),
(3,6,'3306'),(3,7,'3307'),(3,8,'3308'),(3,9,'3309'),(3,10,'3310'),
(3,11,'3311'),(3,12,'3312'),(3,13,'3313'),(3,14,'3314'),(3,15,'3315'),
(3,16,'3316'),(3,17,'3317'),(3,18,'3318'),(3,19,'3319'),(3,20,'3320'),
(3,21,'3321'),(3,22,'3322'),(3,23,'3323'),(3,24,'3324'),(3,25,'3325'),
(3,26,'3326'),(3,27,'3327'),(3,28,'3328'),(3,29,'3329'),(3,30,'3331'),
(3,31,'3333'),
-- 4层（31间）
(4,1,'3401'),(4,2,'3402'),(4,3,'3403'),(4,4,'3404'),(4,5,'3405'),
(4,6,'3406'),(4,7,'3407'),(4,8,'3408'),(4,9,'3409'),(4,10,'3410'),
(4,11,'3411'),(4,12,'3412'),(4,13,'3413'),(4,14,'3414'),(4,15,'3415'),
(4,16,'3416'),(4,17,'3417'),(4,18,'3418'),(4,19,'3419'),(4,20,'3420'),
(4,21,'3421'),(4,22,'3422'),(4,23,'3423'),(4,24,'3424'),(4,25,'3425'),
(4,26,'3426'),(4,27,'3427'),(4,28,'3428'),(4,29,'3429'),(4,30,'3431'),
(4,31,'3433');
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2052600000000000000 + floor_num * 100 + seq AS id,
room_code,
CONCAT(room_code, '寝室') AS room_name,
2048580084373245954 AS building_id,
floor_num,
'1' AS room_type,
8 AS actual_beds,
32.00 AS room_area,
'0' AS room_status,
seq AS sort,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:10:00' AS create_time,
1 AS update_by,
'2026-04-27 11:10:00' AS update_time,
NULL AS remark
FROM tmp_xx3_room_data;
-- 2) 批量插入床位（xg_dorm_bed）
-- 当前提示词配置：每个寝室8个床位（1上、1下、2上、2下、3上、3下、4上、4下）
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2052700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT(r.room_code, '-', b.bed_code_suffix) AS bed_code,
b.bed_name,
2052600000000000000 + r.floor_num * 100 + r.seq AS room_id,
b.bed_type,
'0' AS bed_status,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:10:00' AS create_time,
1 AS update_by,
'2026-04-27 11:10:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM tmp_xx3_room_data r
CROSS JOIN (
SELECT 1 AS bed_sort, '1S' AS bed_code_suffix, '1上' AS bed_name, '2' AS bed_type UNION ALL
SELECT 2, '1X', '1下', '1' UNION ALL
SELECT 3, '2S', '2上', '2' UNION ALL
SELECT 4, '2X', '2下', '1' UNION ALL
SELECT 5, '3S', '3上', '2' UNION ALL
SELECT 6, '3X', '3下', '1' UNION ALL
SELECT 7, '4S', '4上', '2' UNION ALL
SELECT 8, '4X', '4下', '1'
) b;
DROP TEMPORARY TABLE IF EXISTS tmp_xx3_room_data;
COMMIT;
-- 预期数据量：
-- 房间：123条（1层30 + 2层31 + 3层31 + 4层31）
-- 床位：984条（123 * 8）
```
# 西溪1号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 西溪1号楼基础信息
-- building_id = 2048578056012025858
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2048600000000000000 + t.floor_num * 100 + t.seq AS id,
CONCAT('1', t.floor_num, LPAD(t.room_no, 2, '0')) AS room_code,
CONCAT('1', t.floor_num, LPAD(t.room_no, 2, '0'), '寝室') AS room_name,
2048578056012025858 AS building_id,
t.floor_num,
'1' AS room_type, -- 标准间
8 AS actual_beds,
32.00 AS room_area,
'0' AS room_status, -- 可用
t.seq AS sort, -- 每层从1开始
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 10:30:00' AS create_time,
1 AS update_by,
'2026-04-27 10:30:00' AS update_time,
NULL AS remark
FROM (
-- 1层：1101~1113（13间）
SELECT 1 AS floor_num, 1 AS seq, 1 AS room_no UNION ALL
SELECT 1,2,2 UNION ALL SELECT 1,3,3 UNION ALL SELECT 1,4,4 UNION ALL
SELECT 1,5,5 UNION ALL SELECT 1,6,6 UNION ALL SELECT 1,7,7 UNION ALL
SELECT 1,8,8 UNION ALL SELECT 1,9,9 UNION ALL SELECT 1,10,10 UNION ALL
SELECT 1,11,11 UNION ALL SELECT 1,12,12 UNION ALL SELECT 1,13,13 UNION ALL
-- 2层：1201~1213、1215（14间）
SELECT 2,1,1 UNION ALL SELECT 2,2,2 UNION ALL SELECT 2,3,3 UNION ALL
SELECT 2,4,4 UNION ALL SELECT 2,5,5 UNION ALL SELECT 2,6,6 UNION ALL
SELECT 2,7,7 UNION ALL SELECT 2,8,8 UNION ALL SELECT 2,9,9 UNION ALL
SELECT 2,10,10 UNION ALL SELECT 2,11,11 UNION ALL SELECT 2,12,12 UNION ALL
SELECT 2,13,13 UNION ALL SELECT 2,14,15 UNION ALL
-- 3层：1301~1313、1315（14间）
SELECT 3,1,1 UNION ALL SELECT 3,2,2 UNION ALL SELECT 3,3,3 UNION ALL
SELECT 3,4,4 UNION ALL SELECT 3,5,5 UNION ALL SELECT 3,6,6 UNION ALL
SELECT 3,7,7 UNION ALL SELECT 3,8,8 UNION ALL SELECT 3,9,9 UNION ALL
SELECT 3,10,10 UNION ALL SELECT 3,11,11 UNION ALL SELECT 3,12,12 UNION ALL
SELECT 3,13,13 UNION ALL SELECT 3,14,15 UNION ALL
-- 4层：1401~1413、1415（14间）
SELECT 4,1,1 UNION ALL SELECT 4,2,2 UNION ALL SELECT 4,3,3 UNION ALL
SELECT 4,4,4 UNION ALL SELECT 4,5,5 UNION ALL SELECT 4,6,6 UNION ALL
SELECT 4,7,7 UNION ALL SELECT 4,8,8 UNION ALL SELECT 4,9,9 UNION ALL
SELECT 4,10,10 UNION ALL SELECT 4,11,11 UNION ALL SELECT 4,12,12 UNION ALL
SELECT 4,13,13 UNION ALL SELECT 4,14,15
) t;
-- 2) 批量插入床位（xg_dorm_bed）
-- 床位顺序：1上、1下、2上、2下、3上、3下、4上、4下
-- bed sort在名称中体现，且每房间顺序固定为1~8
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2048700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT('1', r.floor_num, LPAD(r.room_no, 2, '0'), '-', b.bed_code_suffix) AS bed_code,
b.bed_name AS bed_name,
2048600000000000000 + r.floor_num * 100 + r.seq AS room_id,
b.bed_type,
'0' AS bed_status, -- 空闲
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 10:30:00' AS create_time,
1 AS update_by,
'2026-04-27 10:30:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM (
-- 与房间插入保持同一套房间清单
SELECT 1 AS floor_num, 1 AS seq, 1 AS room_no UNION ALL
SELECT 1,2,2 UNION ALL SELECT 1,3,3 UNION ALL SELECT 1,4,4 UNION ALL
SELECT 1,5,5 UNION ALL SELECT 1,6,6 UNION ALL SELECT 1,7,7 UNION ALL
SELECT 1,8,8 UNION ALL SELECT 1,9,9 UNION ALL SELECT 1,10,10 UNION ALL
SELECT 1,11,11 UNION ALL SELECT 1,12,12 UNION ALL SELECT 1,13,13 UNION ALL
SELECT 2,1,1 UNION ALL SELECT 2,2,2 UNION ALL SELECT 2,3,3 UNION ALL
SELECT 2,4,4 UNION ALL SELECT 2,5,5 UNION ALL SELECT 2,6,6 UNION ALL
SELECT 2,7,7 UNION ALL SELECT 2,8,8 UNION ALL SELECT 2,9,9 UNION ALL
SELECT 2,10,10 UNION ALL SELECT 2,11,11 UNION ALL SELECT 2,12,12 UNION ALL
SELECT 2,13,13 UNION ALL SELECT 2,14,15 UNION ALL
SELECT 3,1,1 UNION ALL SELECT 3,2,2 UNION ALL SELECT 3,3,3 UNION ALL
SELECT 3,4,4 UNION ALL SELECT 3,5,5 UNION ALL SELECT 3,6,6 UNION ALL
SELECT 3,7,7 UNION ALL SELECT 3,8,8 UNION ALL SELECT 3,9,9 UNION ALL
SELECT 3,10,10 UNION ALL SELECT 3,11,11 UNION ALL SELECT 3,12,12 UNION ALL
SELECT 3,13,13 UNION ALL SELECT 3,14,15 UNION ALL
SELECT 4,1,1 UNION ALL SELECT 4,2,2 UNION ALL SELECT 4,3,3 UNION ALL
SELECT 4,4,4 UNION ALL SELECT 4,5,5 UNION ALL SELECT 4,6,6 UNION ALL
SELECT 4,7,7 UNION ALL SELECT 4,8,8 UNION ALL SELECT 4,9,9 UNION ALL
SELECT 4,10,10 UNION ALL SELECT 4,11,11 UNION ALL SELECT 4,12,12 UNION ALL
SELECT 4,13,13 UNION ALL SELECT 4,14,15
) r
CROSS JOIN (
SELECT 1 AS bed_sort, '1S' AS bed_code_suffix, '1上' AS bed_name, '2' AS bed_type UNION ALL
SELECT 2, '1X', '1下', '1' UNION ALL
SELECT 3, '2S', '2上', '2' UNION ALL
SELECT 4, '2X', '2下', '1' UNION ALL
SELECT 5, '3S', '3上', '2' UNION ALL
SELECT 6, '3X', '3下', '1' UNION ALL
SELECT 7, '4S', '4上', '2' UNION ALL
SELECT 8, '4X', '4下', '1'
) b;
COMMIT;
-- 预期数据量：
-- 房间：55条（13 + 14 + 14 + 14）
-- 床位：440条（55 * 8）
```
# 西溪2号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 西溪2号楼基础信息
-- building_id = 2048578302309945345
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2049600000000000000 + t.floor_num * 100 + t.seq AS id,
CONCAT('2', t.floor_num, LPAD(t.room_no, 2, '0')) AS room_code,
CONCAT('2', t.floor_num, LPAD(t.room_no, 2, '0'), '寝室') AS room_name,
2048578302309945345 AS building_id,
t.floor_num,
'1' AS room_type, -- 标准间
8 AS actual_beds,
32.00 AS room_area,
'0' AS room_status, -- 可用
t.seq AS sort, -- 每层从1开始
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 10:45:00' AS create_time,
1 AS update_by,
'2026-04-27 10:45:00' AS update_time,
NULL AS remark
FROM (
-- 1层：2101~2113（13间）
SELECT 1 AS floor_num, 1 AS seq, 1 AS room_no UNION ALL
SELECT 1,2,2 UNION ALL SELECT 1,3,3 UNION ALL SELECT 1,4,4 UNION ALL
SELECT 1,5,5 UNION ALL SELECT 1,6,6 UNION ALL SELECT 1,7,7 UNION ALL
SELECT 1,8,8 UNION ALL SELECT 1,9,9 UNION ALL SELECT 1,10,10 UNION ALL
SELECT 1,11,11 UNION ALL SELECT 1,12,12 UNION ALL SELECT 1,13,13 UNION ALL
-- 2层：2201~2213、2215（14间）
SELECT 2,1,1 UNION ALL SELECT 2,2,2 UNION ALL SELECT 2,3,3 UNION ALL
SELECT 2,4,4 UNION ALL SELECT 2,5,5 UNION ALL SELECT 2,6,6 UNION ALL
SELECT 2,7,7 UNION ALL SELECT 2,8,8 UNION ALL SELECT 2,9,9 UNION ALL
SELECT 2,10,10 UNION ALL SELECT 2,11,11 UNION ALL SELECT 2,12,12 UNION ALL
SELECT 2,13,13 UNION ALL SELECT 2,14,15 UNION ALL
-- 3层：2301~2313、2315（14间）
SELECT 3,1,1 UNION ALL SELECT 3,2,2 UNION ALL SELECT 3,3,3 UNION ALL
SELECT 3,4,4 UNION ALL SELECT 3,5,5 UNION ALL SELECT 3,6,6 UNION ALL
SELECT 3,7,7 UNION ALL SELECT 3,8,8 UNION ALL SELECT 3,9,9 UNION ALL
SELECT 3,10,10 UNION ALL SELECT 3,11,11 UNION ALL SELECT 3,12,12 UNION ALL
SELECT 3,13,13 UNION ALL SELECT 3,14,15 UNION ALL
-- 4层：2401~2413、2415（14间）
SELECT 4,1,1 UNION ALL SELECT 4,2,2 UNION ALL SELECT 4,3,3 UNION ALL
SELECT 4,4,4 UNION ALL SELECT 4,5,5 UNION ALL SELECT 4,6,6 UNION ALL
SELECT 4,7,7 UNION ALL SELECT 4,8,8 UNION ALL SELECT 4,9,9 UNION ALL
SELECT 4,10,10 UNION ALL SELECT 4,11,11 UNION ALL SELECT 4,12,12 UNION ALL
SELECT 4,13,13 UNION ALL SELECT 4,14,15
) t;
-- 2) 批量插入床位（xg_dorm_bed）
-- 床位顺序：1上、1下、2上、2下、3上、3下、4上、4下
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2049700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT('2', r.floor_num, LPAD(r.room_no, 2, '0'), '-', b.bed_code_suffix) AS bed_code,
b.bed_name AS bed_name,
2049600000000000000 + r.floor_num * 100 + r.seq AS room_id,
b.bed_type,
'0' AS bed_status, -- 空闲
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 10:45:00' AS create_time,
1 AS update_by,
'2026-04-27 10:45:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM (
-- 与房间插入保持同一套房间清单
SELECT 1 AS floor_num, 1 AS seq, 1 AS room_no UNION ALL
SELECT 1,2,2 UNION ALL SELECT 1,3,3 UNION ALL SELECT 1,4,4 UNION ALL
SELECT 1,5,5 UNION ALL SELECT 1,6,6 UNION ALL SELECT 1,7,7 UNION ALL
SELECT 1,8,8 UNION ALL SELECT 1,9,9 UNION ALL SELECT 1,10,10 UNION ALL
SELECT 1,11,11 UNION ALL SELECT 1,12,12 UNION ALL SELECT 1,13,13 UNION ALL
SELECT 2,1,1 UNION ALL SELECT 2,2,2 UNION ALL SELECT 2,3,3 UNION ALL
SELECT 2,4,4 UNION ALL SELECT 2,5,5 UNION ALL SELECT 2,6,6 UNION ALL
SELECT 2,7,7 UNION ALL SELECT 2,8,8 UNION ALL SELECT 2,9,9 UNION ALL
SELECT 2,10,10 UNION ALL SELECT 2,11,11 UNION ALL SELECT 2,12,12 UNION ALL
SELECT 2,13,13 UNION ALL SELECT 2,14,15 UNION ALL
SELECT 3,1,1 UNION ALL SELECT 3,2,2 UNION ALL SELECT 3,3,3 UNION ALL
SELECT 3,4,4 UNION ALL SELECT 3,5,5 UNION ALL SELECT 3,6,6 UNION ALL
SELECT 3,7,7 UNION ALL SELECT 3,8,8 UNION ALL SELECT 3,9,9 UNION ALL
SELECT 3,10,10 UNION ALL SELECT 3,11,11 UNION ALL SELECT 3,12,12 UNION ALL
SELECT 3,13,13 UNION ALL SELECT 3,14,15 UNION ALL
SELECT 4,1,1 UNION ALL SELECT 4,2,2 UNION ALL SELECT 4,3,3 UNION ALL
SELECT 4,4,4 UNION ALL SELECT 4,5,5 UNION ALL SELECT 4,6,6 UNION ALL
SELECT 4,7,7 UNION ALL SELECT 4,8,8 UNION ALL SELECT 4,9,9 UNION ALL
SELECT 4,10,10 UNION ALL SELECT 4,11,11 UNION ALL SELECT 4,12,12 UNION ALL
SELECT 4,13,13 UNION ALL SELECT 4,14,15
) r
CROSS JOIN (
SELECT 1 AS bed_sort, '1S' AS bed_code_suffix, '1上' AS bed_name, '2' AS bed_type UNION ALL
SELECT 2, '1X', '1下', '1' UNION ALL
SELECT 3, '2S', '2上', '2' UNION ALL
SELECT 4, '2X', '2下', '1' UNION ALL
SELECT 5, '3S', '3上', '2' UNION ALL
SELECT 6, '3X', '3下', '1' UNION ALL
SELECT 7, '4S', '4上', '2' UNION ALL
SELECT 8, '4X', '4下', '1'
) b;
COMMIT;
-- 预期数据量：
-- 房间：55条（13 + 14 + 14 + 14）
-- 床位：440条（55 * 8）
```
# 西溪2号楼 的信息
- 1层：2101寝室、2102寝室、2103寝室、2104寝室、2105寝室、2106寝室、2107寝室、2108寝室、2109寝室、2110寝室、2111寝室、2112寝室、2113寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 2层：2201寝室、2202寝室、2203寝室、2204寝室、2205寝室、2206寝室、2207寝室、2208寝室、2209寝室、2210寝室、2211寝室、2212寝室、2213寝室、2215寝室、
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 3层：2301寝室、2302寝室、2303寝室、2304寝室、2305寝室、2306寝室、2307寝室、2308寝室、2309寝室、2310寝室、2311寝室、2312寝室、2313寝室、2315寝室、
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 4层：2401寝室、2402寝室、2403寝室、2404寝室、2405寝室、2406寝室、2407寝室、2408寝室、2409寝室、2410寝室、2411寝室、2412寝室、2413寝室、2415寝室、
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 西溪5号楼 的信息
- 1层：5100寝室、5101寝室、5102寝室、5103寝室、5104寝室、5105寝室、5106寝室、5107寝室、5109寝室、
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 2层：5200寝室、5201寝室、5202寝室、5203寝室、5204寝室、5205寝室、5206寝室、5207寝室、5209寝室、5211寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 3层：5300寝室、5301寝室、5302寝室、5303寝室、5304寝室、5305寝室、5306寝室、5307寝室、5309寝室、5311寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 4层：5400寝室、5401寝室、5402寝室、5403寝室、5404寝室、5405寝室、5406寝室、5407寝室、5409寝室、5411寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 西溪5号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 西溪5号楼基础信息
-- building_id = 2048580537001562113
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
-- 注意：严格按提供清单生成，房间号非连续，不做自动补齐
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2050600000000000000 + t.floor_num * 100 + t.seq AS id,
CONCAT('5', t.floor_num, LPAD(t.room_no, 2, '0')) AS room_code,
CONCAT('5', t.floor_num, LPAD(t.room_no, 2, '0'), '寝室') AS room_name,
2048580537001562113 AS building_id,
t.floor_num,
'1' AS room_type, -- 标准间
8 AS actual_beds,
32.00 AS room_area,
'0' AS room_status, -- 可用
t.seq AS sort, -- 每层从1开始
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 10:50:00' AS create_time,
1 AS update_by,
'2026-04-27 10:50:00' AS update_time,
NULL AS remark
FROM (
-- 1层：5100、5101、5102、5103、5104、5105、5106、5107、5109（9间）
SELECT 1 AS floor_num, 1 AS seq, 0 AS room_no UNION ALL
SELECT 1,2,1 UNION ALL SELECT 1,3,2 UNION ALL SELECT 1,4,3 UNION ALL
SELECT 1,5,4 UNION ALL SELECT 1,6,5 UNION ALL SELECT 1,7,6 UNION ALL
SELECT 1,8,7 UNION ALL SELECT 1,9,9 UNION ALL
-- 2层：5200、5201、5202、5203、5204、5205、5206、5207、5209、5211（10间）
SELECT 2,1,0 UNION ALL SELECT 2,2,1 UNION ALL SELECT 2,3,2 UNION ALL
SELECT 2,4,3 UNION ALL SELECT 2,5,4 UNION ALL SELECT 2,6,5 UNION ALL
SELECT 2,7,6 UNION ALL SELECT 2,8,7 UNION ALL SELECT 2,9,9 UNION ALL
SELECT 2,10,11 UNION ALL
-- 3层：5300、5301、5302、5303、5304、5305、5306、5307、5309、5311（10间）
SELECT 3,1,0 UNION ALL SELECT 3,2,1 UNION ALL SELECT 3,3,2 UNION ALL
SELECT 3,4,3 UNION ALL SELECT 3,5,4 UNION ALL SELECT 3,6,5 UNION ALL
SELECT 3,7,6 UNION ALL SELECT 3,8,7 UNION ALL SELECT 3,9,9 UNION ALL
SELECT 3,10,11 UNION ALL
-- 4层：5400、5401、5402、5403、5404、5405、5406、5407、5409、5411（10间）
SELECT 4,1,0 UNION ALL SELECT 4,2,1 UNION ALL SELECT 4,3,2 UNION ALL
SELECT 4,4,3 UNION ALL SELECT 4,5,4 UNION ALL SELECT 4,6,5 UNION ALL
SELECT 4,7,6 UNION ALL SELECT 4,8,7 UNION ALL SELECT 4,9,9 UNION ALL
SELECT 4,10,11
) t;
-- 2) 批量插入床位（xg_dorm_bed）
-- 床位顺序：1上、1下、2上、2下、3上、3下、4上、4下（每房间固定1~8）
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2050700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT('5', r.floor_num, LPAD(r.room_no, 2, '0'), '-', b.bed_code_suffix) AS bed_code,
b.bed_name AS bed_name,
2050600000000000000 + r.floor_num * 100 + r.seq AS room_id,
b.bed_type,
'0' AS bed_status, -- 空闲
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 10:50:00' AS create_time,
1 AS update_by,
'2026-04-27 10:50:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM (
-- 与房间插入保持同一套非连续清单
SELECT 1 AS floor_num, 1 AS seq, 0 AS room_no UNION ALL
SELECT 1,2,1 UNION ALL SELECT 1,3,2 UNION ALL SELECT 1,4,3 UNION ALL
SELECT 1,5,4 UNION ALL SELECT 1,6,5 UNION ALL SELECT 1,7,6 UNION ALL
SELECT 1,8,7 UNION ALL SELECT 1,9,9 UNION ALL
SELECT 2,1,0 UNION ALL SELECT 2,2,1 UNION ALL SELECT 2,3,2 UNION ALL
SELECT 2,4,3 UNION ALL SELECT 2,5,4 UNION ALL SELECT 2,6,5 UNION ALL
SELECT 2,7,6 UNION ALL SELECT 2,8,7 UNION ALL SELECT 2,9,9 UNION ALL
SELECT 2,10,11 UNION ALL
SELECT 3,1,0 UNION ALL SELECT 3,2,1 UNION ALL SELECT 3,3,2 UNION ALL
SELECT 3,4,3 UNION ALL SELECT 3,5,4 UNION ALL SELECT 3,6,5 UNION ALL
SELECT 3,7,6 UNION ALL SELECT 3,8,7 UNION ALL SELECT 3,9,9 UNION ALL
SELECT 3,10,11 UNION ALL
SELECT 4,1,0 UNION ALL SELECT 4,2,1 UNION ALL SELECT 4,3,2 UNION ALL
SELECT 4,4,3 UNION ALL SELECT 4,5,4 UNION ALL SELECT 4,6,5 UNION ALL
SELECT 4,7,6 UNION ALL SELECT 4,8,7 UNION ALL SELECT 4,9,9 UNION ALL
SELECT 4,10,11
) r
CROSS JOIN (
SELECT 1 AS bed_sort, '1S' AS bed_code_suffix, '1上' AS bed_name, '2' AS bed_type UNION ALL
SELECT 2, '1X', '1下', '1' UNION ALL
SELECT 3, '2S', '2上', '2' UNION ALL
SELECT 4, '2X', '2下', '1' UNION ALL
SELECT 5, '3S', '3上', '2' UNION ALL
SELECT 6, '3X', '3下', '1' UNION ALL
SELECT 7, '4S', '4上', '2' UNION ALL
SELECT 8, '4X', '4下', '1'
) b;
COMMIT;
-- 预期数据量：
-- 房间：39条（9 + 10 + 10 + 10）
-- 床位：312条（39 * 8）
```
# 西溪6号楼 的信息
- 1层：6101寝室、6102寝室、6103寝室、6104寝室、6105寝室、6106寝室、6107寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上
- 2层：6201寝室、6202寝室、6203寝室、6204寝室、6205寝室、6206寝室、6207寝室、6209寝室
每个寝室八个床位，分别为1上、1下、2上、2下
- 3层：6301寝室、6302寝室、6303寝室、6304寝室、6305寝室、6306寝室、6307寝室、6309寝室
每个寝室八个床位，分别为1上、1下、2上、2下
- 4层：6401寝室、6402寝室、6403寝室、6404寝室、6405寝室、6406寝室、6407寝室、6409寝室
每个寝室八个床位，分别为1上、1下、2上、2下
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 西溪6号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 西溪6号楼基础信息
-- building_id = 2048580670791471106
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
-- 注意：严格按提供清单生成，房间号非连续，不做自动补齐
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2051600000000000000 + t.floor_num * 100 + t.seq AS id,
CONCAT('6', t.floor_num, LPAD(t.room_no, 2, '0')) AS room_code,
CONCAT('6', t.floor_num, LPAD(t.room_no, 2, '0'), '寝室') AS room_name,
2048580670791471106 AS building_id,
t.floor_num,
'1' AS room_type, -- 标准间
CASE WHEN t.floor_num = 1 THEN 5 ELSE 4 END AS actual_beds,
32.00 AS room_area,
'0' AS room_status, -- 可用
t.seq AS sort, -- 每层从1开始
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:00:00' AS create_time,
1 AS update_by,
'2026-04-27 11:00:00' AS update_time,
NULL AS remark
FROM (
-- 1层：6101~6107（7间）
SELECT 1 AS floor_num, 1 AS seq, 1 AS room_no UNION ALL
SELECT 1,2,2 UNION ALL SELECT 1,3,3 UNION ALL SELECT 1,4,4 UNION ALL
SELECT 1,5,5 UNION ALL SELECT 1,6,6 UNION ALL SELECT 1,7,7 UNION ALL
-- 2层：6201~6207、6209（8间）
SELECT 2,1,1 UNION ALL SELECT 2,2,2 UNION ALL SELECT 2,3,3 UNION ALL
SELECT 2,4,4 UNION ALL SELECT 2,5,5 UNION ALL SELECT 2,6,6 UNION ALL
SELECT 2,7,7 UNION ALL SELECT 2,8,9 UNION ALL
-- 3层：6301~6307、6309（8间）
SELECT 3,1,1 UNION ALL SELECT 3,2,2 UNION ALL SELECT 3,3,3 UNION ALL
SELECT 3,4,4 UNION ALL SELECT 3,5,5 UNION ALL SELECT 3,6,6 UNION ALL
SELECT 3,7,7 UNION ALL SELECT 3,8,9 UNION ALL
-- 4层：6401~6407、6409（8间）
SELECT 4,1,1 UNION ALL SELECT 4,2,2 UNION ALL SELECT 4,3,3 UNION ALL
SELECT 4,4,4 UNION ALL SELECT 4,5,5 UNION ALL SELECT 4,6,6 UNION ALL
SELECT 4,7,7 UNION ALL SELECT 4,8,9
) t;
-- 2) 批量插入床位（xg_dorm_bed）
-- 1层每房间5个床位：1上、1下、2上、2下、3上
-- 2-4层每房间4个床位：1上、1下、2上、2下
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2051700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT('6', r.floor_num, LPAD(r.room_no, 2, '0'), '-', b.bed_code_suffix) AS bed_code,
b.bed_name AS bed_name,
2051600000000000000 + r.floor_num * 100 + r.seq AS room_id,
b.bed_type,
'0' AS bed_status, -- 空闲
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:00:00' AS create_time,
1 AS update_by,
'2026-04-27 11:00:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM (
-- 与房间插入保持同一套非连续清单
SELECT 1 AS floor_num, 1 AS seq, 1 AS room_no UNION ALL
SELECT 1,2,2 UNION ALL SELECT 1,3,3 UNION ALL SELECT 1,4,4 UNION ALL
SELECT 1,5,5 UNION ALL SELECT 1,6,6 UNION ALL SELECT 1,7,7 UNION ALL
SELECT 2,1,1 UNION ALL SELECT 2,2,2 UNION ALL SELECT 2,3,3 UNION ALL
SELECT 2,4,4 UNION ALL SELECT 2,5,5 UNION ALL SELECT 2,6,6 UNION ALL
SELECT 2,7,7 UNION ALL SELECT 2,8,9 UNION ALL
SELECT 3,1,1 UNION ALL SELECT 3,2,2 UNION ALL SELECT 3,3,3 UNION ALL
SELECT 3,4,4 UNION ALL SELECT 3,5,5 UNION ALL SELECT 3,6,6 UNION ALL
SELECT 3,7,7 UNION ALL SELECT 3,8,9 UNION ALL
SELECT 4,1,1 UNION ALL SELECT 4,2,2 UNION ALL SELECT 4,3,3 UNION ALL
SELECT 4,4,4 UNION ALL SELECT 4,5,5 UNION ALL SELECT 4,6,6 UNION ALL
SELECT 4,7,7 UNION ALL SELECT 4,8,9
) r
JOIN (
-- 1层可用床位（5个）
SELECT 1 AS floor_num, 1 AS bed_sort, '1S' AS bed_code_suffix, '1上' AS bed_name, '2' AS bed_type UNION ALL
SELECT 1, 2, '1X', '1下', '1' UNION ALL
SELECT 1, 3, '2S', '2上', '2' UNION ALL
SELECT 1, 4, '2X', '2下', '1' UNION ALL
SELECT 1, 5, '3S', '3上', '2' UNION ALL
-- 2-4层可用床位（4个）
SELECT 2, 1, '1S', '1上', '2' UNION ALL
SELECT 2, 2, '1X', '1下', '1' UNION ALL
SELECT 2, 3, '2S', '2上', '2' UNION ALL
SELECT 2, 4, '2X', '2下', '1' UNION ALL
SELECT 3, 1, '1S', '1上', '2' UNION ALL
SELECT 3, 2, '1X', '1下', '1' UNION ALL
SELECT 3, 3, '2S', '2上', '2' UNION ALL
SELECT 3, 4, '2X', '2下', '1' UNION ALL
SELECT 4, 1, '1S', '1上', '2' UNION ALL
SELECT 4, 2, '1X', '1下', '1' UNION ALL
SELECT 4, 3, '2S', '2上', '2' UNION ALL
SELECT 4, 4, '2X', '2下', '1'
) b ON r.floor_num = b.floor_num;
COMMIT;
-- 预期数据量：
-- 房间：31条（7 + 8 + 8 + 8）
-- 床位：131条（1层7*5=35，2-4层24*4=96，总计131）
```
# 西溪3号楼 的信息
- 1层：3101寝室、3102寝室、3103寝室、3104寝室、3105寝室、3106寝室、3107寝室、3108寝室、3109寝室、3110寝室、3111寝室、3112寝室、3113寝室、3114寝室、3115寝室、3116寝室、3117寝室、3118寝室、3119寝室、3120寝室、3121寝室、3122寝室、3123寝室、3124寝室、3125寝室、3126寝室、3127寝室、3128寝室、3129寝室、3131寝室、
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 2层：3201寝室、3202寝室、3203寝室、3204寝室、3205寝室、3206寝室、3207寝室、3208寝室、3209寝室、3210寝室、3211寝室、3212寝室、3213寝室、3214寝室、3215寝室、3216寝室、3217寝室、3218寝室、3219寝室、3220寝室、3221寝室、3222寝室、3223寝室、3224寝室、3225寝室、3226寝室、3227寝室、3228寝室、3229寝室、3231寝室、3233寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 3层：3301寝室、3302寝室、3303寝室、3304寝室、3305寝室、3306寝室、3307寝室、3308寝室、3309寝室、3310寝室、3311寝室、3312寝室、3313寝室、3314寝室、3315寝室、3316寝室、3317寝室、3318寝室、3319寝室、3320寝室、3321寝室、3322寝室、3323寝室、3324寝室、3325寝室、3326寝室、3327寝室、3328寝室、3329寝室、3331寝室、3333寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 4层：3401寝室、3402寝室、3403寝室、3404寝室、3405寝室、3406寝室、3407寝室、3408寝室、3409寝室、3410寝室、3411寝室、3412寝室、3413寝室、3414寝室、3415寝室、3416寝室、3417寝室、3418寝室、3419寝室、3420寝室、3421寝室、3422寝室、3423寝室、3424寝室、3425寝室、3426寝室、3427寝室、3428寝室、3429寝室、3431寝室、3433寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 西溪4号楼 的信息
- 1层：4101寝室、4102寝室、4103寝室、4104寝室、4105寝室、4106寝室、4107寝室、4108寝室、4109寝室、4110寝室、4111寝室、4112寝室、4113寝室、4114寝室、4115寝室、4116寝室、4117寝室、4118寝室、4119寝室、4120寝室、4121寝室、4122寝室、4123寝室、4125寝室、4127寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 2层：4201寝室、4202寝室、4203寝室、4204寝室、4205寝室、4206寝室、4207寝室、4208寝室、4209寝室、4210寝室、4211寝室、4212寝室、4213寝室、4214寝室、4215寝室、4216寝室、4217寝室、4218寝室、4219寝室、4220寝室、4221寝室、4222寝室、4223寝室、4225寝室、4227寝室、4229寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 3层：4301寝室、4302寝室、4303寝室、4304寝室、4305寝室、4306寝室、4307寝室、4308寝室、4309寝室、4310寝室、4311寝室、4312寝室、4313寝室、4314寝室、4315寝室、4316寝室、4317寝室、4318寝室、4319寝室、4320寝室、4321寝室、4322寝室、4323寝室、4325寝室、4327寝室、4329寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
- 4层：4401寝室、4402寝室、4403寝室、4404寝室、4405寝室、4406寝室、4407寝室、4408寝室、4409寝室、4410寝室、4411寝室、4412寝室、4413寝室、4414寝室、4415寝室、4416寝室、4417寝室、4418寝室、4419寝室、4420寝室、4421寝室、4422寝室、4423寝室、4425寝室、4427寝室、4429寝室
每个寝室八个床位，分别为1上、1下、2上、2下、3上、3下、4上、4下
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 临安5号楼的信息
- 1层：5101、5102、5103、5104、5105、5106、5107、5108、5109、5110、5111、5112、5113
每个寝室八个床位，分别为1、2、3、4、5、6
- 2层：5201、5202、5203、5204、5205、5206、5207、5208、5209、5210、5211、522、5213
每个寝室八个床位，分别为1、2、3、4、5、6
- 3层：5301、5302、5303、5304、5305、5306、5307、5308、5309、5310、5311、5312、5313
每个寝室八个床位，分别为1、2、3、4、5、6
- 4层：5401、5402、5403、5404、5405、5406、5407、5408、5409、5410、5411、5412、5413
每个寝室八个床位，分别为1、2、3、4、5、6
- 5层：5501、5502、5503、5504、5505、5506、5507、5508、5509、5510、5511、5512、5513
每个寝室八个床位，分别为1、2、3、4、5、6
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 临安5号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 临安5号楼基础信息
-- building_id = 2048581868017799170
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
-- MySQL 5.7兼容版：不用WITH / ROW_NUMBER
DROP TEMPORARY TABLE IF EXISTS tmp_la5_room_data;
CREATE TEMPORARY TABLE tmp_la5_room_data (
floor_num INT NOT NULL,
seq INT NOT NULL,
room_code VARCHAR(10) NOT NULL,
PRIMARY KEY (floor_num, seq)
) ENGINE=MEMORY;
INSERT INTO tmp_la5_room_data (floor_num, seq, room_code) VALUES
-- 1层（13间）
(1,1,'5101'),(1,2,'5102'),(1,3,'5103'),(1,4,'5104'),(1,5,'5105'),
(1,6,'5106'),(1,7,'5107'),(1,8,'5108'),(1,9,'5109'),(1,10,'5110'),
(1,11,'5111'),(1,12,'5112'),(1,13,'5113'),
-- 2层（13间，按提示词原样保留522）
(2,1,'5201'),(2,2,'5202'),(2,3,'5203'),(2,4,'5204'),(2,5,'5205'),
(2,6,'5206'),(2,7,'5207'),(2,8,'5208'),(2,9,'5209'),(2,10,'5210'),
(2,11,'5211'),(2,12,'522'),(2,13,'5213'),
-- 3层（13间）
(3,1,'5301'),(3,2,'5302'),(3,3,'5303'),(3,4,'5304'),(3,5,'5305'),
(3,6,'5306'),(3,7,'5307'),(3,8,'5308'),(3,9,'5309'),(3,10,'5310'),
(3,11,'5311'),(3,12,'5312'),(3,13,'5313'),
-- 4层（13间）
(4,1,'5401'),(4,2,'5402'),(4,3,'5403'),(4,4,'5404'),(4,5,'5405'),
(4,6,'5406'),(4,7,'5407'),(4,8,'5408'),(4,9,'5409'),(4,10,'5410'),
(4,11,'5411'),(4,12,'5412'),(4,13,'5413'),
-- 5层（13间）
(5,1,'5501'),(5,2,'5502'),(5,3,'5503'),(5,4,'5504'),(5,5,'5505'),
(5,6,'5506'),(5,7,'5507'),(5,8,'5508'),(5,9,'5509'),(5,10,'5510'),
(5,11,'5511'),(5,12,'5512'),(5,13,'5513');
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2054600000000000000 + floor_num * 100 + seq AS id,
room_code,
CONCAT(room_code, '寝室') AS room_name,
2048581868017799170 AS building_id,
floor_num,
'1' AS room_type,
6 AS actual_beds,
28.00 AS room_area,
'0' AS room_status,
seq AS sort,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:35:00' AS create_time,
1 AS update_by,
'2026-04-27 11:35:00' AS update_time,
NULL AS remark
FROM tmp_la5_room_data;
-- 2) 批量插入床位（xg_dorm_bed）
-- 特别注意：床位名称按提示词使用“1、2、3、4、5、6”
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2054700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT(r.room_code, '-', b.bed_code_suffix) AS bed_code,
b.bed_name,
2054600000000000000 + r.floor_num * 100 + r.seq AS room_id,
'4' AS bed_type, -- 统一按“独立床”处理
'0' AS bed_status,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:35:00' AS create_time,
1 AS update_by,
'2026-04-27 11:35:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM tmp_la5_room_data r
CROSS JOIN (
SELECT 1 AS bed_sort, '01' AS bed_code_suffix, '1' AS bed_name UNION ALL
SELECT 2, '02', '2' UNION ALL
SELECT 3, '03', '3' UNION ALL
SELECT 4, '04', '4' UNION ALL
SELECT 5, '05', '5' UNION ALL
SELECT 6, '06', '6'
) b;
DROP TEMPORARY TABLE IF EXISTS tmp_la5_room_data;
COMMIT;
-- 预期数据量：
-- 房间：65条（5层 * 13间）
-- 床位：390条（65 * 6）
```
# 临安6号楼的信息
- 1层：6101、6102、6103、6104、6105、6106、6107、6108、6109、6110、6111、6112、6113
每个寝室八个床位，分别为1、2、3、4、5、6
- 2层：6201、6202、6203、6204、6205、6206、6207、6208、6209、6210、6211、6212、6213
每个寝室八个床位，分别为1、2、3、4、5、6
- 3层：6301、6302、6303、6304、6305、6306、6307、6308、6309、6310、6311、6312、6313
每个寝室八个床位，分别为1、2、3、4、5、6
- 4层：6401、6402、6403、6404、6405、6406、6407、6408、6409、6410、6411、6412、6413
每个寝室八个床位，分别为1、2、3、4、5、6
- 5层：6501、6502、6503、6504、6505、6506、6507、6508、6509、6510、6511、6512、6513
每个寝室八个床位，分别为1、2、3、4、5、6
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 临安6号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 临安6号楼基础信息
-- building_id = 2048581964566482945
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
-- MySQL 5.7兼容版：不用WITH / ROW_NUMBER
DROP TEMPORARY TABLE IF EXISTS tmp_la6_room_data;
CREATE TEMPORARY TABLE tmp_la6_room_data (
floor_num INT NOT NULL,
seq INT NOT NULL,
room_code VARCHAR(10) NOT NULL,
PRIMARY KEY (floor_num, seq)
) ENGINE=MEMORY;
INSERT INTO tmp_la6_room_data (floor_num, seq, room_code) VALUES
-- 1层（13间）
(1,1,'6101'),(1,2,'6102'),(1,3,'6103'),(1,4,'6104'),(1,5,'6105'),
(1,6,'6106'),(1,7,'6107'),(1,8,'6108'),(1,9,'6109'),(1,10,'6110'),
(1,11,'6111'),(1,12,'6112'),(1,13,'6113'),
-- 2层（13间）
(2,1,'6201'),(2,2,'6202'),(2,3,'6203'),(2,4,'6204'),(2,5,'6205'),
(2,6,'6206'),(2,7,'6207'),(2,8,'6208'),(2,9,'6209'),(2,10,'6210'),
(2,11,'6211'),(2,12,'6212'),(2,13,'6213'),
-- 3层（13间）
(3,1,'6301'),(3,2,'6302'),(3,3,'6303'),(3,4,'6304'),(3,5,'6305'),
(3,6,'6306'),(3,7,'6307'),(3,8,'6308'),(3,9,'6309'),(3,10,'6310'),
(3,11,'6311'),(3,12,'6312'),(3,13,'6313'),
-- 4层（13间）
(4,1,'6401'),(4,2,'6402'),(4,3,'6403'),(4,4,'6404'),(4,5,'6405'),
(4,6,'6406'),(4,7,'6407'),(4,8,'6408'),(4,9,'6409'),(4,10,'6410'),
(4,11,'6411'),(4,12,'6412'),(4,13,'6413'),
-- 5层（13间）
(5,1,'6501'),(5,2,'6502'),(5,3,'6503'),(5,4,'6504'),(5,5,'6505'),
(5,6,'6506'),(5,7,'6507'),(5,8,'6508'),(5,9,'6509'),(5,10,'6510'),
(5,11,'6511'),(5,12,'6512'),(5,13,'6513');
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2055600000000000000 + floor_num * 100 + seq AS id,
room_code,
CONCAT(room_code, '寝室') AS room_name,
2048581964566482945 AS building_id,
floor_num,
'1' AS room_type,
6 AS actual_beds,
28.00 AS room_area,
'0' AS room_status,
seq AS sort,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:40:00' AS create_time,
1 AS update_by,
'2026-04-27 11:40:00' AS update_time,
NULL AS remark
FROM tmp_la6_room_data;
-- 2) 批量插入床位（xg_dorm_bed）
-- 特别注意：床位名称按提示词使用“1、2、3、4、5、6”
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2055700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT(r.room_code, '-', b.bed_code_suffix) AS bed_code,
b.bed_name,
2055600000000000000 + r.floor_num * 100 + r.seq AS room_id,
'4' AS bed_type, -- 统一按“独立床”处理
'0' AS bed_status,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:40:00' AS create_time,
1 AS update_by,
'2026-04-27 11:40:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM tmp_la6_room_data r
CROSS JOIN (
SELECT 1 AS bed_sort, '01' AS bed_code_suffix, '1' AS bed_name UNION ALL
SELECT 2, '02', '2' UNION ALL
SELECT 3, '03', '3' UNION ALL
SELECT 4, '04', '4' UNION ALL
SELECT 5, '05', '5' UNION ALL
SELECT 6, '06', '6'
) b;
DROP TEMPORARY TABLE IF EXISTS tmp_la6_room_data;
COMMIT;
-- 预期数据量：
-- 房间：65条（5层 * 13间）
-- 床位：390条（65 * 6）
```
# 临安7号楼的信息
- 1层：7101、7102、7103、7104、7105、7106、7107、7108、7109、7110、7111、7112、7113
每个寝室八个床位，分别为1、2、3、4、5、6
- 2层：7201、7202、7203、7204、7205、7206、7207、7208、7209、7210、7211、7212、7213
每个寝室八个床位，分别为1、2、3、4、5、6
- 3层：7301、7302、7303、7304、7305、7306、7307、7308、7309、7310、7311、7312、7313
每个寝室八个床位，分别为1、2、3、4、5、6
- 4层：7401、7402、7403、7404、7405、7406、7407、7408、7409、7410、7411、7412、7413
每个寝室八个床位，分别为1、2、3、4、5、6
- 5层：7501、7502、7503、7504、7505、7506、7507、7508、7509、7510、7511、7512、7513
每个寝室八个床位，分别为1、2、3、4、5、6
特别注意，要将排序号按照顺序填写啊，每层楼寝室从1开始，每个房间的床位也是从1开始
# 临安7号楼 房间与床位批量SQL（xg_dorm_room / xg_dorm_bed）
```sql
-- =============================================
-- 临安7号楼基础信息
-- building_id = 2048582084469051394
-- tenant_id = 0
-- create_by = 1
-- create_dept = 100
-- =============================================
START TRANSACTION;
-- 1) 批量插入房间（xg_dorm_room）
-- MySQL 5.7兼容版：不用WITH / ROW_NUMBER
-- 注意：按提示词仅生成1~5层（未包含6层清单）
DROP TEMPORARY TABLE IF EXISTS tmp_la7_room_data;
CREATE TEMPORARY TABLE tmp_la7_room_data (
floor_num INT NOT NULL,
seq INT NOT NULL,
room_code VARCHAR(10) NOT NULL,
PRIMARY KEY (floor_num, seq)
) ENGINE=MEMORY;
INSERT INTO tmp_la7_room_data (floor_num, seq, room_code) VALUES
-- 1层（13间）
(1,1,'7101'),(1,2,'7102'),(1,3,'7103'),(1,4,'7104'),(1,5,'7105'),
(1,6,'7106'),(1,7,'7107'),(1,8,'7108'),(1,9,'7109'),(1,10,'7110'),
(1,11,'7111'),(1,12,'7112'),(1,13,'7113'),
-- 2层（13间）
(2,1,'7201'),(2,2,'7202'),(2,3,'7203'),(2,4,'7204'),(2,5,'7205'),
(2,6,'7206'),(2,7,'7207'),(2,8,'7208'),(2,9,'7209'),(2,10,'7210'),
(2,11,'7211'),(2,12,'7212'),(2,13,'7213'),
-- 3层（13间）
(3,1,'7301'),(3,2,'7302'),(3,3,'7303'),(3,4,'7304'),(3,5,'7305'),
(3,6,'7306'),(3,7,'7307'),(3,8,'7308'),(3,9,'7309'),(3,10,'7310'),
(3,11,'7311'),(3,12,'7312'),(3,13,'7313'),
-- 4层（13间）
(4,1,'7401'),(4,2,'7402'),(4,3,'7403'),(4,4,'7404'),(4,5,'7405'),
(4,6,'7406'),(4,7,'7407'),(4,8,'7408'),(4,9,'7409'),(4,10,'7410'),
(4,11,'7411'),(4,12,'7412'),(4,13,'7413'),
-- 5层（13间）
(5,1,'7501'),(5,2,'7502'),(5,3,'7503'),(5,4,'7504'),(5,5,'7505'),
(5,6,'7506'),(5,7,'7507'),(5,8,'7508'),(5,9,'7509'),(5,10,'7510'),
(5,11,'7511'),(5,12,'7512'),(5,13,'7513');
INSERT INTO `hzdyjs_xg`.`xg_dorm_room`
(`id`, `room_code`, `room_name`, `building_id`, `floor_num`, `room_type`, `actual_beds`, `room_area`, `room_status`, `sort`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2056600000000000000 + floor_num * 100 + seq AS id,
room_code,
CONCAT(room_code, '寝室') AS room_name,
2048582084469051394 AS building_id,
floor_num,
'1' AS room_type,
6 AS actual_beds,
28.00 AS room_area,
'0' AS room_status,
seq AS sort,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:45:00' AS create_time,
1 AS update_by,
'2026-04-27 11:45:00' AS update_time,
NULL AS remark
FROM tmp_la7_room_data;
-- 2) 批量插入床位（xg_dorm_bed）
-- 特别注意：床位名称按提示词使用“1、2、3、4、5、6”
INSERT INTO `hzdyjs_xg`.`xg_dorm_bed`
(`id`, `bed_code`, `bed_name`, `room_id`, `bed_type`, `bed_status`, `deleted`, `tenant_id`, `create_by`, `create_dept`, `create_time`, `update_by`, `update_time`, `remark`)
SELECT
2056700000000000000 + (r.floor_num * 100 + r.seq) * 10 + b.bed_sort AS id,
CONCAT(r.room_code, '-', b.bed_code_suffix) AS bed_code,
b.bed_name,
2056600000000000000 + r.floor_num * 100 + r.seq AS room_id,
'4' AS bed_type, -- 统一按“独立床”处理
'0' AS bed_status,
'0' AS deleted,
0 AS tenant_id,
1 AS create_by,
100 AS create_dept,
'2026-04-27 11:45:00' AS create_time,
1 AS update_by,
'2026-04-27 11:45:00' AS update_time,
CONCAT('床位顺序=', b.bed_sort) AS remark
FROM tmp_la7_room_data r
CROSS JOIN (
SELECT 1 AS bed_sort, '01' AS bed_code_suffix, '1' AS bed_name UNION ALL
SELECT 2, '02', '2' UNION ALL
SELECT 3, '03', '3' UNION ALL
SELECT 4, '04', '4' UNION ALL
SELECT 5, '05', '5' UNION ALL
SELECT 6, '06', '6'
) b;
DROP TEMPORARY TABLE IF EXISTS tmp_la7_room_data;
COMMIT;
-- 预期数据量：
-- 房间：65条（5层 * 13间）
-- 床位：390条（65 * 6）
```
Reference in new issue View Git Blame Copy Permalink