编写sql语句技巧

复杂sql语句：case语句、exists语句

  

一、今天遇到一个帅选数据的问题，sqlserver存储过程中，加了一个新的筛选数据的条件，不打乱原来的取数据的模式下把新的功能添加进去，用一段复合型的exists语句就可以解决问题

 

   and 原先的sql 语句

   --过滤自助机不显示的科室、专家数据 add by aorigel 20101020
   and not exists( select 1

                              from GH_YY_PBZB aa,GH_YY_PBZBMX bb 
                           where aa.pbzbid = bb.pbzbid and bb.pbmxid = a.pbmxxh
                               and 1 =
                                     (
                                      case a.pbkmlx
                                      --科室
                                      when 1 then
                                                 case when

                                                        exists
                                                        (
                                                           select 1 from YY_KSBMK cc
                                                          where cc.id = aa.pbmk and isnull(cc.zzghbz,0) =  1
                                                       )
                                                 then 1

                                                 else 0

                                                 end
                                      --专家
                                      when 0 then
                                                 case when

                                                        exists
                                                        (
                                                         select 1 from YY_ZGBMK dd
                                                         where dd.id = aa.pbmk and isnull(dd.zzghbz,0) =  1
                                                         )
                                                 then 1

                                                 else 0

                                                 end
                                   end
                               )           
                 )

解释：

      1、exists 语句的使用

      2、case 语句的使用，嵌套使用

      3、1=1 逻辑使用