往期回顾

在我们探讨"精兵简政"系列的最后一篇，我们将聚焦于SQL中常见的"多余写法"问题。冗余代码不仅影响可读性，更会导致数据库做许多不必要的工作，从而影响性能。让我们通过实际案例来学习如何精简SQL，让代码更加高效。

案例8 一个有着多处多余写法的经典脚本

select *
  from (select rownum num, h.*
          from (select a.ne_Id,
                       a.GATHER_BATCH_ID,
                       b.REGION_NAME,
                       c.ne_Name,
                       d.SUBJECT,
                       (select list_label
                          from TP_DOMAIN_LISTVALUES
                         where domain_code = 'DOMAIN_TA_TASK_TYPE'
                           and list_value = e.TASK_TYPE) TASK_TYPE,
                       '<div style=''line-height:20px;''>' ||
                       to_char(a.SOURCE_BEGIN_DATE, 'yyyy-mm-dd hh24:mi:ss') ||
                       '<BR/>至<BR/>' ||
                       to_char(a.SOURCE_END_DATE, 'yyyy-mm-dd hh24:mi:ss') ||
                       '</div>' gather_Begin_Date,
                       to_char(a.GATHER_END_DATE, 'yyyy-mm-dd hh24:mi:ss') gather_End_Date,
                       (case
                         when a.REC_CNT = null then
                          '程序出错'
                         when a.REC_CNT = -2 then
                          '程序出错'
                         when a.REC_CNT = -1 then
                          '配置脚本出错'
                         when a.REC_CNT >= 0 then
                          '正常'
                         else
                          '程序出错'
                       end) as rec_cnt,
                       decode(d.DR_ID,
                              0,
                              ' ',
                              (select list_label
                                 from TP_DOMAIN_LISTVALUES
                                where domain_code = 'DOMAIN_DR_ID_FLAG'
                                  and list_value = d.DR_ID)) DR_ID
                  from (select a1.*
                          from TA_GATHER_TASK_BATCH a1,
                               (select gather_batch_id,
                                       (lag(gather_batch_id, 1, 0)
                                        over(PARTITION BY gather_config_id,
                                             source_begin_date_new,
                                             source_end_date_new ORDER BY
                                             source_begin_date desc)) flag
                                  from (select ne_id,
                                               region_Id,
                                               GATHER_BATCH_ID,
                                               gather_config_id,
                                               to_char(source_begin_date,
                                                       'yyyy-MM-dd') source_begin_date_new,
                                               to_char(source_end_date,
                                                       'yyyy-MM-dd') source_end_date_new,
                                               source_begin_date,
                                               source_end_date,
                                               GATHER_BEGIN_DATE,
                                               GATHER_END_DATE
                                          from TA_GATHER_TASK_BATCH) a
                                 where 1 = 1) a2
                         where a1.gather_batch_id = a2.gather_batch_id
                           and a2.flag = 0) a,
                       MANAGE_REGION b,
                       NET_ELEMENT c,
                       LOGIC_DATA_GATHER d,
                       LOGIC_GATHER_TASK e
                 where 1 = 1
                   and a.region_Id = b.REGION_ID
                   and a.ne_Id = c.NE_ID
                   and a.GATHER_CONFIG_ID = d.LOGIC_DATA_GATHER_ID
                   and a.TASK_ID = e.TASK_ID
                   and d.GATHER_TYPE <> 2
                   and d.report_Type = '数据处理'
                   and d.REPORT_GATHER_FLAG = '0'
                 order by ne_Id, SUBJECT, gather_Begin_Date) h)
where num >= 1
   and num <= 10
order by ne_Id, SUBJECT, gather_Begin_Date

这段SQL复杂而冗长，充斥着多余的写法。让我们重点分析其中最突出的问题：

问题分析

1. 复杂的子查询逻辑

最明显的问题出现在以下部分：

select a1.*
                          from TA_GATHER_TASK_BATCH a1,
                               (select gather_batch_id,
                                       (lag(gather_batch_id, 1, 0)
                                        over(PARTITION BY gather_config_id,
                                             source_begin_date_new,
                                             source_end_date_new ORDER BY
                                             source_begin_date desc)) flag
                                  from (select ne_id,
                                               region_Id,
                                               GATHER_BATCH_ID,
                                               gather_config_id,
                                               to_char(source_begin_date,
                                                       'yyyy-MM-dd') source_begin_date_new,
                                               to_char(source_end_date,
                                                       'yyyy-MM-dd') source_end_date_new,
                                               source_begin_date,
                                               source_end_date,
                                               GATHER_BEGIN_DATE,
                                               GATHER_END_DATE
                                          from TA_GATHER_TASK_BATCH) a
                                 where 1 = 1) a2
                         where a1.gather_batch_id = a2.gather_batch_id
                           and a2.flag = 0

可等价修正为如下：

SELECT *
  FROM (select a.*,
               row_number() over(PARTITION BY gather_config_id, TRUNC(source_begin_date), TRUNC(source_end_date) ORDER BY source_begin_date desc) rn
          from TA_GATHER_TASK_BATCH a
         where 1 = 1)
where RN = 1

这段代码的问题在于：

多次扫描同一表：TA_GATHER_TASK_BATCH表被扫描了两次，一次在内层子查询中，一次在外层查询中
复杂的LAG函数使用：使用LAG构造出0值，然后在后面条件中筛选flag=0，实际上只是为了获取分组内的第一条记录
不必要的TO_CHAR转换：为分组而对日期进行TO_CHAR转换，然后再按日期排序，明显多此一举
无意义的条件：where 1 = 1这样的条件毫无意义，只会增加解析负担

2. 重复的排序操作

SQL最后的order by ne_Id, SUBJECT, gather_Begin_Date是完全多余的，因为内层查询已经按相同条件排序过了。

优化方案

针对上述问题，我们可以进行以下优化：

1. 简化复杂子查询

最关键的优化是将复杂的LAG函数逻辑替换为更直接的ROW_NUMBER函数：

SELECT *
  FROM (select a.*,
               row_number() over(PARTITION BY gather_config_id, TRUNC(source_begin_date), TRUNC(source_end_date) ORDER BY source_begin_date desc) rn
          from TA_GATHER_TASK_BATCH a
         where 1 = 1)
where RN = 1

这样的改写具有以下优势：

只扫描TA_GATHER_TASK_BATCH表一次
直接使用TRUNC函数处理日期，避免了TO_CHAR转换和额外的字段
使用ROW_NUMBER直接筛选每组第一条记录，逻辑清晰明了

2. 移除重复排序

删除最外层的order by ne_Id, SUBJECT, gather_Begin_Date，因为内层查询已经排序过了。

3. 优化后的完整SQL

select *
  from (select rownum num, h.*
          from (select a.ne_Id,
                       a.GATHER_BATCH_ID,
                       b.REGION_NAME,
                       c.ne_Name,
                       d.SUBJECT,
                       (select list_label
                          from TP_DOMAIN_LISTVALUES
                         where domain_code = 'DOMAIN_TA_TASK_TYPE'
                           and list_value = e.TASK_TYPE) TASK_TYPE,
                       '<div style=''line-height:20px;''>' ||
                       to_char(a.SOURCE_BEGIN_DATE, 'yyyy-mm-dd hh24:mi:ss') ||
                       '<BR/>至<BR/>' ||
                       to_char(a.SOURCE_END_DATE, 'yyyy-mm-dd hh24:mi:ss') ||
                       '</div>' gather_Begin_Date,
                       to_char(a.GATHER_END_DATE, 'yyyy-mm-dd hh24:mi:ss') gather_End_Date,
                       (case
                         when a.REC_CNT = null then
                          '程序出错'
                         when a.REC_CNT = -2 then
                          '程序出错'
                         when a.REC_CNT = -1 then
                          '配置脚本出错'
                         when a.REC_CNT >= 0 then
                          '正常'
                         else
                          '程序出错'
                       end) as rec_cnt,
                       decode(d.DR_ID,
                              0,
                              ' ',
                              (select list_label
                                 from TP_DOMAIN_LISTVALUES
                                where domain_code = 'DOMAIN_DR_ID_FLAG'
                                  and list_value = d.DR_ID)) DR_ID
                  from (SELECT *
                          FROM (select a.*,
                                       row_number() over(PARTITION BY gather_config_id, TRUNC(source_begin_date), TRUNC(source_end_date) ORDER BY source_begin_date desc) rn
                                  from TA_GATHER_TASK_BATCH a
                                 where 1 = 1)
                         where RN = 1) a,
                       MANAGE_REGION b,
                       NET_ELEMENT c,
                       LOGIC_DATA_GATHER d,
                       LOGIC_GATHER_TASK e
                 where 1 = 1
                   and a.region_Id = b.REGION_ID
                   and a.ne_Id = c.NE_ID
                   and a.GATHER_CONFIG_ID = d.LOGIC_DATA_GATHER_ID
                   and a.TASK_ID = e.TASK_ID
                   and d.GATHER_TYPE <> 2
                   and d.report_Type = '数据处理'
                   and d.REPORT_GATHER_FLAG = '0'
                 order by ne_Id, SUBJECT, gather_Begin_Date) h)
 where num >= 1
   and num <= 10

```

案例9 多了解ORACLE特性，不要多虑

我们再来看一个简单的例子，来源于之前案例的原始脚本：

(to_char(nvl((select count(*)
                                           from dcc_ne_log
                                          where dcc_ne_log.result <> 1
                                            and peer_id = ne_state.peer_id
                                            and log_time between
                                                trunc(sysdate) and sysdate
                                          group by (peer_id)),
                                         0)))
                         end) err_cnt,

这段代码中，开发人员对COUNT()函数的结果使用了NVL处理，这是完全不必要的。因为**COUNT()无论何时都不会返回NULL值**——即使没有匹配的记录，COUNT(*)也会返回0而不是NULL。

这个案例虽然简单，但很有代表性——它反映了开发人员对数据库特性了解不足而导致的多余写法。正确的写法应该是：

(to_char((select count(*)
                                      from dcc_ne_log
                                     where dcc_ne_log.result <> 1
                                       and peer_id = ne_state.peer_id
                                       and log_time between
                                           trunc(sysdate) and sysdate
                                     group by (peer_id))))
                    end) err_cnt,

实际上，由于GROUP BY子句的存在，这个子查询可能返回多行或者没有行，所以更严谨的写法是：

(to_char((select count(*)
                                      from dcc_ne_log
                                     where dcc_ne_log.result <> 1
                                       and peer_id = ne_state.peer_id
                                       and log_time between
                                           trunc(sysdate) and sysdate)))
                    end) err_cnt,

去掉GROUP BY子句，因为在这种情况下它是多余的，COUNT(*)已经是一个聚合结果。

总结：精简之道

通过这两个案例，我们可以总结出避免SQL多余写法的几个关键原则：

了解数据库特性：充分了解SQL函数的特性和返回值规则，避免不必要的NULL处理
避免重复扫描：尽量避免多次扫描同一个表，可以使用CTE或子查询优化
使用合适的函数：选择最直接的函数来实现目标，如案例中用ROW_NUMBER替代复杂的LAG逻辑
消除冗余操作：避免重复排序、重复转换等操作

"精兵简政"的核心是减少不必要的复杂性，让SQL既简洁又高效。这不仅能提升查询性能，还能使代码更易于理解和维护。

在SQL优化的道路上，"去繁就简"是永恒的主题。我们应该时刻保持批判思维，不断反思自己的代码中是否存在可以精简的部分。正如本系列所展示的，往往最优的解决方案也是最简洁的那一个。

通过本系列的学习，希望大家能够掌握SQL优化的思路和方法，写出既高效又优雅的SQL代码。记住，在SQL开发中，少即是多，简即是美。

未完待续...

等价改写优化案例精选第三部【顺势而为】① 列的类型转换

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

基于达梦的SQL等价改写优化案例精选第二部【精兵简政】④ 多余写法