115 lines
3.6 KiB
SQL
115 lines
3.6 KiB
SQL
-- ============================================================
|
||
-- 清理没有关联机场的大疆无人机
|
||
-- ============================================================
|
||
-- 问题：大疆的机场是7个，无人机是10个，有3个无人机没有关联机场
|
||
-- 解决方案：删除没有关联机场的大疆无人机记录
|
||
-- ============================================================
|
||
|
||
-- 第一步：查询所有大疆厂商的无人机（用于确认）
|
||
SELECT
|
||
a.aircraft_id,
|
||
a.aircraft_name,
|
||
d.device_manufacturer,
|
||
d.device_sn
|
||
FROM device_aircraft a
|
||
LEFT JOIN device_device d ON a.device_id = d.device_id
|
||
WHERE d.device_manufacturer = 'dajiang'
|
||
ORDER BY a.aircraft_id;
|
||
|
||
-- 第二步：查询没有关联机场的大疆无人机
|
||
SELECT
|
||
a.aircraft_id,
|
||
a.aircraft_name,
|
||
d.device_manufacturer,
|
||
d.device_sn,
|
||
d.iot_device_id
|
||
FROM device_aircraft a
|
||
INNER JOIN device_device d ON a.device_id = d.device_id
|
||
LEFT JOIN device_dock_aircraft da ON a.aircraft_id = da.aircraft_id
|
||
WHERE d.device_manufacturer = 'dajiang'
|
||
AND da.id IS NULL
|
||
ORDER BY a.aircraft_id;
|
||
|
||
-- 第三步：确认删除前，再次检查（谨慎操作）
|
||
-- 执行此查询后，请确认这些无人机确实需要删除
|
||
SELECT
|
||
a.aircraft_id,
|
||
a.aircraft_name,
|
||
d.device_manufacturer,
|
||
d.device_sn,
|
||
d.iot_device_id,
|
||
'将被删除' AS action
|
||
FROM device_aircraft a
|
||
INNER JOIN device_device d ON a.device_id = d.device_id
|
||
LEFT JOIN device_dock_aircraft da ON a.aircraft_id = da.aircraft_id
|
||
WHERE d.device_manufacturer = 'dajiang'
|
||
AND da.id IS NULL;
|
||
|
||
-- 第四步：删除没有关联机场的大疆无人机（谨慎操作！）
|
||
-- 删除顺序：
|
||
-- 1. 先删除无人机挂载关联表中的记录
|
||
-- 2. 再删除机场无人机关联表中的记录
|
||
-- 3. 最后删除无人机表中的记录
|
||
|
||
-- 4.1 删除无人机挂载关联表中的记录
|
||
DELETE FROM device_aircraft_payload
|
||
WHERE aircraft_id IN (
|
||
SELECT a.aircraft_id
|
||
FROM device_aircraft a
|
||
INNER JOIN device_device d ON a.device_id = d.device_id
|
||
LEFT JOIN device_dock_aircraft da ON a.aircraft_id = da.aircraft_id
|
||
WHERE d.device_manufacturer = 'dajiang'
|
||
AND da.id IS NULL
|
||
);
|
||
|
||
-- 4.2 删除机场无人机关联表中的记录（如果有）
|
||
DELETE FROM device_dock_aircraft
|
||
WHERE aircraft_id IN (
|
||
SELECT a.aircraft_id
|
||
FROM device_aircraft a
|
||
INNER JOIN device_device d ON a.device_id = d.device_id
|
||
LEFT JOIN device_dock_aircraft da ON a.aircraft_id = da.aircraft_id
|
||
WHERE d.device_manufacturer = 'dajiang'
|
||
AND da.id IS NULL
|
||
);
|
||
|
||
-- 4.3 删除无人机表中的记录
|
||
DELETE FROM device_aircraft
|
||
WHERE aircraft_id IN (
|
||
SELECT temp.aircraft_id
|
||
FROM (
|
||
SELECT a.aircraft_id
|
||
FROM device_aircraft a
|
||
INNER JOIN device_device d ON a.device_id = d.device_id
|
||
LEFT JOIN device_dock_aircraft da ON a.aircraft_id = da.aircraft_id
|
||
WHERE d.device_manufacturer = 'dajiang'
|
||
AND da.id IS NULL
|
||
) AS temp
|
||
);
|
||
|
||
-- 第五步：验证删除结果
|
||
SELECT
|
||
a.aircraft_id,
|
||
a.aircraft_name,
|
||
d.device_manufacturer,
|
||
d.device_sn
|
||
FROM device_aircraft a
|
||
INNER JOIN device_device d ON a.device_id = d.device_id
|
||
WHERE d.device_manufacturer = 'dajiang'
|
||
ORDER BY a.aircraft_id;
|
||
|
||
-- 统计验证
|
||
SELECT
|
||
'大疆无人机总数' AS statistic_name,
|
||
COUNT(*) AS count
|
||
FROM device_aircraft a
|
||
INNER JOIN device_device d ON a.device_id = d.device_id
|
||
WHERE d.device_manufacturer = 'dajiang'
|
||
UNION ALL
|
||
SELECT
|
||
'大疆机场总数' AS statistic_name,
|
||
COUNT(*) AS count
|
||
FROM device_dock dock
|
||
INNER JOIN device_device d ON dock.device_id = d.device_id
|
||
WHERE d.device_manufacturer = 'dajiang';
|