sql 多字段 in

wangduqiang747

于 2021-08-18 17:33:25 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏： sql

本文链接：https://blog.youkuaiyun.com/wangduqiang747/article/details/119785305

sql 专栏收录该内容

7 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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