sql 多字段 in

最新推荐文章于 2024-06-04 09:06:32 发布

原创最新推荐文章于 2024-06-04 09:06:32 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

7 篇文章

订阅专栏

本文讲解了如何将一个使用复杂子查询的SQL更新语句优化为更易读的形式，通过替换子查询with in关键字，使得代码清晰，便于理解。重点在于解释了原代码中wm_concat和count为1筛选条件的作用，并强调了代码可读性的改进。

很多人不会用多字段的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);