sql 多字段 in

很多人不会用多字段的in

改之前

update LIInsContact lii
   set lii.relation = '01'
 where lii.contactno in (select m.contactno
                           from (select a.employeeid,
                                        a.customid,
                                        count(1) cou,
                                        wm_concat(a.contactno) contactno
                                   from LIInsContact a,
                                        (select b.employeeid em, b.customid cu
                                           from LIInsContact b
                                          where b.relation = '06') c
                                  where a.employeeid = c.em
                                    and a.customid = c.cu
                                  group by a.employeeid, a.customid) m
                          where m.cou = 1);

改之后

update LIInsContact lii
   set lii.relation = '01'
 where (lii.employeeid,lii.customid) in (select m.employeeid,m.customid
                           from (select a.employeeid,
                                        a.customid,
                                        count(1) cou
                                     --   wm_concat(a.contactno) contactno
                                   from LIInsContact a,
                                        (select b.employeeid em, b.customid cu
                                           from LIInsContact b
                                          where b.relation = '06') c
                                  where a.employeeid = c.em
                                    and a.customid = c.cu
                                  group by a.employeeid, a.customid) m
                          where m.cou = 1);

可读性就很强了. 原来的之所以可以是因为wm_concat count为1的才是筛选到的,也就是没逗号的. 可读性差