准备工作:
1、编写删除脏数据的sql
DELETE
FROM
equipment_info
WHERE
id IN (
SELECT
t.id
FROM
(
SELECT
i.id
FROM
equipment_info i
LEFT JOIN base_district d ON i.district_id = d.id
LEFT JOIN base_street s ON i.street_id = s.id
LEFT JOIN equipment_machine_room r ON i.machine_room_id = r.id
WHERE
i.NAME = '多级离心泵'
AND d.NAME = '淄博碧桂园项目一期别墅苑区'
AND s.NAME = '别墅区8街'
AND r.NAME = '别墅区1#供水泵房机房'
) t
);
2.了解Excel中=CONCATENATE()的用法(CONCATENATE()是字符串连接或合并函数)
在做完准备工作后,需要数据在Excel中可以自动填充,语句如下:
=CONCATENATE("DELETE
FROM
equipment_info
WHERE
id IN (
SELECT
t.id
FROM
(
SELECT
i.id
FROM
equipment_info i
LEFT JOIN base_district d ON i.district_id = d.id
LEFT JOIN base_street s ON i.street_id = s.id
LEFT JOIN equipment_machine_room r ON i.machine_room_id = r.id
WHERE
i.NAME = '" & A2 & "'
AND d.NAME = '" & B2 & "'
AND s.NAME = '" & C2 & "'
AND r.NAME = '" & D2 & "'
) t
);")
可是把这一串东西放进Excel的单元格时会发现,字符串长度竟然超过255个字符了,真是可恶啊。所以只能进行拆分了,我们拆分成两部分,分别为
(1)
DELETE
FROM
equipment_info
WHERE
id IN (
SELECT
t.id
FROM
(
SELECT
i.id
FROM
equipment_info i
LEFT JOIN base_district d ON i.district_id = d.id
LEFT JOIN base_street s ON i.street_id = s.id
LEFT JOIN equipment_machine_room r ON i.machine_room_id = r.id
以上的sql片段作为参数传进去(在这里我把sql片段放到E2单元格上)
(2)
WHERE
i.NAME = '" & A2 & "'
AND d.NAME = '" & B2 & "'
AND s.NAME = '" & C2 & "'
AND r.NAME = '" & D2 & "'
) t
接下来在Excel单元格进行sql的拼接,如下:
=CONCATENATE(E2,"
WHERE
i.NAME = '" & A2 & "'
AND d.NAME = '" & B2 & "'
AND s.NAME = '" & C2 & "'
AND r.NAME = '" & D2 & "'
) t
);")
以上的组装字符串作为参数传进去(在这里我把组装字符串的函数放到F2单元格上)
这样就会在Excel中自动生成所需的sql语句了。
"DELETE
FROM
equipment_info
WHERE
id IN (
SELECT
t.id
FROM
(
SELECT
i.id
FROM
equipment_info i
LEFT JOIN base_district d ON i.district_id = d.id
LEFT JOIN base_street s ON i.street_id = s.id
LEFT JOIN equipment_machine_room r ON i.machine_room_id = r.id
WHERE
i.NAME = '室内配电柜'
AND d.NAME = '淄博碧桂园项目二期流苏园苑区'
AND s.NAME = '流苏园3街'
AND r.NAME = '流苏园9栋1单元1#电梯机房'
) t
);"
此时的sql多了"",需要进行替换处理,把""替换掉
DELETE
FROM
equipment_info
WHERE
id IN (
SELECT
t.id
FROM
(
SELECT
i.id
FROM
equipment_info i
LEFT JOIN base_district d ON i.district_id = d.id
LEFT JOIN base_street s ON i.street_id = s.id
LEFT JOIN equipment_machine_room r ON i.machine_room_id = r.id
WHERE
i.NAME = '室内配电柜'
AND d.NAME = '淄博碧桂园项目二期流苏园苑区'
AND s.NAME = '流苏园3街'
AND r.NAME = '流苏园9栋1单元1#电梯机房'
) t
);
这条sql就是我们所需要的sql啦!
当然,这只是生成一条数据的sql,如果要生成所有的sql该如何做呢?其实也很简单
经过以上的步骤,删除2500条数据的sql就写好啦,接下来只需要把这些sql丢到navicat执行就万事大吉啦。
如有不懂得可以加V:Lemon0493