115 lines
3.6 KiB
MySQL
115 lines
3.6 KiB
MySQL
|
-- ============================================================
|
|||
|
|
-- 清理没有关联机场的大疆无人机
|
|||
|
|
-- ============================================================
|
|||
|
|
-- 问题：大疆的机场是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';
|