如何在4万条数据中快速高效删除excel表指定的2500条数据？

    准备工作：
    1、编写删除脏数据的sql

DELETE 
FROMequipment_info 
WHEREid IN (SELECTt.id FROM(SELECTi.id FROMequipment_info iLEFT JOIN base_district d ON i.district_id = d.idLEFT JOIN base_street s ON i.street_id = s.idLEFT JOIN equipment_machine_room r ON i.machine_room_id = r.id WHEREi.NAME = '多级离心泵' AND d.NAME = '淄博碧桂园项目一期别墅苑区' AND s.NAME = '别墅区8街' AND r.NAME = '别墅区1#供水泵房机房' ) t );

    2.了解Excel中=CONCATENATE()的用法(CONCATENATE()是字符串连接或合并函数)

    在做完准备工作后,需要数据在Excel中可以自动填充，语句如下：

=CONCATENATE("DELETE 
FROMequipment_info 
WHEREid IN (SELECTt.id FROM(SELECTi.id FROMequipment_info iLEFT JOIN base_district d ON i.district_id = d.idLEFT JOIN base_street s ON i.street_id = s.idLEFT JOIN equipment_machine_room r ON i.machine_room_id = r.id WHEREi.NAME = '" & A2 & "' AND d.NAME = '" & B2 & "' AND s.NAME = '" & C2 & "' AND r.NAME = '" & D2 & "' ) t );")

    可是把这一串东西放进Excel的单元格时会发现，字符串长度竟然超过255个字符了，真是可恶啊。所以只能进行拆分了，我们拆分成两部分，分别为

(1)

DELETE 
FROMequipment_info 
WHEREid IN (SELECTt.id FROM(SELECTi.id FROMequipment_info iLEFT JOIN base_district d ON i.district_id = d.idLEFT JOIN base_street s ON i.street_id = s.idLEFT JOIN equipment_machine_room r ON i.machine_room_id = r.id

       以上的sql片段作为参数传进去(在这里我把sql片段放到E2单元格上)
 (2)

  WHEREi.NAME = '" & A2 & "' AND d.NAME = '" & B2 & "' AND s.NAME = '" & C2 & "' AND r.NAME = '" & D2 & "' ) t 

    接下来在Excel单元格进行sql的拼接，如下：

=CONCATENATE(E2,"WHEREi.NAME = '" & A2 & "' AND d.NAME = '" & B2 & "' AND s.NAME = '" & C2 & "' AND r.NAME = '" & D2 & "' ) t );")

    以上的组装字符串作为参数传进去(在这里我把组装字符串的函数放到F2单元格上)
    这样就会在Excel中自动生成所需的sql语句了。

"DELETE 
FROMequipment_info 
WHEREid IN (SELECTt.id FROM(SELECTi.id FROMequipment_info iLEFT JOIN base_district d ON i.district_id = d.idLEFT JOIN base_street s ON i.street_id = s.idLEFT JOIN equipment_machine_room r ON i.machine_room_id = r.idWHEREi.NAME = '室内配电柜' AND d.NAME = '淄博碧桂园项目二期流苏园苑区' AND s.NAME = '流苏园3街' AND r.NAME = '流苏园9栋1单元1#电梯机房' ) t );"

     此时的sql多了"",需要进行替换处理，把""替换掉

DELETE 
FROMequipment_info 
WHEREid IN (SELECTt.id FROM(SELECTi.id FROMequipment_info iLEFT JOIN base_district d ON i.district_id = d.idLEFT JOIN base_street s ON i.street_id = s.idLEFT JOIN equipment_machine_room r ON i.machine_room_id = r.idWHEREi.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