Oracle 查询结果去重保留一项

首先因为需要查询很多字段，也就排除了使用distinct的可能性。

1.1 原始sql

select finalSql.*
  from (select '' SMS_CONTENT,
               '2' as 短信发出类型_批量发出,
               yqdz.yqdz_hz_mc 短信接收方姓名,
               case
                 when wc.JTDH is not null then
                  wc.JTDH
                 when wc.XIAOLINGTONG is not null then
                  wc.XIAOLINGTONG
                 when wc.SJ is not null then
                  wc.SJ
               end 短信接受方手机号,
               '' 业务对象ID,
               yqdz.org_id 区分码
          from yongqidizhi yqdz
          left join kehuview wc
            on wc.Y_ID = yqdz.yqdz_kh_id
          left join ranqibiao rqb
            on rqb.RQB_YQDZ_ID = yqdz.yqdz_id
         where (wc.JTDH is not null or wc.XIAOLINGTONG is not null or
               wc.SJ is not null)
           and yqdz.yqdz_kh_lx = '2') finalSql
 where length(trim(finalSql.短信接受方手机号)) = 11

1.2 查询结果

很显然存在重复项

1.3 查询总数

2.1 修改sql

select finalSql.SMS_CONTENT,
               finalSql.短信发出类型_批量发出,
               finalSql.短信接收方姓名,
               finalSql.短信接受方手机号,
               finalSql.业务对象ID,
               finalSql.区分码,
               count(*) as 次数
          from (select '' SMS_CONTENT,
                       '2' as 短信发出类型_批量发出,
                       yqdz.yqdz_hz_mc 短信接收方姓名,
                       case
                         when wc.JTDH is not null then
                          wc.JTDH
                         when wc.XIAOLINGTONG is not null then
                          wc.XIAOLINGTONG
                         when wc.SJ is not null then
                          wc.SJ
                       end 短信接受方手机号,
                       '' 业务对象ID,
                       yqdz.org_id 区分码
                  from yongqidizhi yqdz
                  left join kehuview wc
                    on wc.Y_ID = yqdz.yqdz_kh_id
                  left join ranqibiao rqb
                    on rqb.RQB_YQDZ_ID = yqdz.yqdz_id
                 where (wc.JTDH is not null or wc.XIAOLINGTONG is not null or
                       wc.SJ is not null)
                   and yqdz.yqdz_kh_lx = '2') finalSql
         where length(trim(finalSql.短信接受方手机号)) = 11
         group by finalSql.SMS_CONTENT,
                  finalSql.短信发出类型_批量发出,
                  finalSql.短信接收方姓名,
                  finalSql.短信接受方手机号,
                  finalSql.业务对象ID,
                  finalSql.区分码
         order by 次数 desc

2.2 查询结果：

我是根据手机号进行分组的，很显然，这里存在很多的垃圾数据，我们需要将这些相同的数据只保留一条

2.3 查询总的记录数

3.1 因为返回结果并不需要“count(*)” 这个字段，因此在最外层再加一层查询，改进sql如下

select finalSql2.SMS_CONTENT,
       finalSql2.短信发出类型_批量发出,
       finalSql2.短信接收方姓名,
       finalSql2.短信接受方手机号,
       finalSql2.业务对象ID,
       finalSql2.区分码
  from (select finalSql.SMS_CONTENT,
               finalSql.短信发出类型_批量发出,
               finalSql.短信接收方姓名,
               finalSql.短信接受方手机号,
               finalSql.业务对象ID,
               finalSql.区分码,
               count(*) as 次数
          from (select '' SMS_CONTENT,
                       '2' as 短信发出类型_批量发出,
                       yqdz.yqdz_hz_mc 短信接收方姓名,
                       case
                         when wc.JTDH is not null then
                          wc.JTDH
                         when wc.XIAOLINGTONG is not null then
                          wc.XIAOLINGTONG
                         when wc.SJ is not null then
                          wc.SJ
                       end 短信接受方手机号,
                       '' 业务对象ID,
                       yqdz.org_id 区分码
                  from yongqidizhi yqdz
                  left join kehuview wc
                    on wc.Y_ID = yqdz.yqdz_kh_id
                  left join ranqibiao rqb
                    on rqb.RQB_YQDZ_ID = yqdz.yqdz_id
                 where (wc.JTDH is not null or wc.XIAOLINGTONG is not null or
                       wc.SJ is not null)
                   and yqdz.yqdz_kh_lx = '2') finalSql
         where length(trim(finalSql.短信接受方手机号)) = 11
         group by finalSql.SMS_CONTENT,
                  finalSql.短信发出类型_批量发出,
                  finalSql.短信接收方姓名,
                  finalSql.短信接受方手机号,
                  finalSql.业务对象ID,
                  finalSql.区分码
         order by 次数 desc) finalsql2