一条长sql的排错过程

java达人

发布于 2018-01-31 16:11:47

7310

发布于 2018-01-31 16:11:47

过程

有这样一条长sql，由于环境原因，对select.....in...... 语法限制使用，因此以left join语法代替，原来只需要统计一天的数据汇总结果，因为特殊需要，需要一次性统计各天的结果，于是首先将子查询中时间过滤的部分提取到外层。

修改前sql1：

select count(*) from (SELECT DISTINCT t.con_no AS con_no
    , CASE 
 WHEN tgua.atime IS NOT NULL THEN tgua.atime
 WHEN tcc.CTIME IS NOT NULL THEN tcc.CTIME
 WHEN bb.CTIME IS NOT NULL THEN bb.CTIME
  END AS CTIME, t.PRO_METHOD AS pro_method
 FROM my_customer t
 LEFT OUTER JOIN first_ auth tgua
 ON t.con_no = tgua.con_no
 AND tgua.stat = 1
 AND tgua.type = 'C' and 
(tgua.atime>= '2017-06-22' and tgua.atime< '2017-06-23')
 LEFT OUTER JOIN my_ contact tcc
 ON t.con_no = tcc.con_no
 AND tcc.NAME IS NOT NULL and 
(tcc.ctime>= '2017-06-22' and tcc.ctime< '2017-06-23')
 LEFT OUTER JOIN (
 SELECT tcp.con_no AS con_no, tcai.CTIME AS CTIME
 FROM my_ person tcp
 INNER JOIN my_auth_info tcai
 ON tcp.CNO = tcai.CNO and 
(tcai.ctime>= '2017-06-22' and tcai.ctime< '2017-06-23')
 ) bb
 ON t.con_no = bb.con_no 
 WHERE t.PRO_METHOD = 'uc' and (tgua.con_no IS NOT NULL
 OR tcc.con_no IS NOT NULL
 OR bb.con_no IS NOT NULL)) pp order by pp.con_no
sql2：
select count(*) from 
 (SELECT DISTINCT t.con_no AS con_no
 , CASE 
                               WHEN tgua.atime IS NOT NULL THEN tgua.atime
             WHEN tcc.CTIME IS NOT NULL THEN tcc.CTIME
             WHEN bb.CTIME IS NOT NULL THEN bb.CTIME
 END AS CTIME, t.PRO_METHOD AS pro_method

 FROM my_customer t
 LEFT OUTER JOIN first_ auth tgua
 ON t.con_no = tgua.con_no
 AND tgua.status = 1
 AND tgua.type = 'C'
 LEFT OUTER JOIN my_ contact tcc
 ON t.con_no = tcc.con_no
 AND tcc.NAME IS NOT NULL
 LEFT OUTER JOIN (
 SELECT tcp.con_no AS con_no, tcai.CTIME AS CTIME
 FROM my_ person tcp
 INNER JOIN my_auth_info tcai
 ON tcp.CNO = tcai.CNO
 ) bb
 ON t.con_no = bb.con_no
 WHERE t.pro_method = 'uc' and (tgua.con_no IS NOT NULL
 OR tcc.con_no IS NOT NULL
 OR bb.con_no IS NOT NULL)) p where CTIME>= '2017-06-22' and CTIME< '2017-06-23'
 order by con_no

试着执行了两条sql，发现sql2查询结果数量总比sql1要少，于是用*代替count(*),打印出具体查询结果，

sql3：

select * from (SELECT DISTINCT t.con_no AS con_no
 , CASE 
 WHEN tgua.atime IS NOT NULL THEN tgua.atime
 WHEN tcc.CTIME IS NOT NULL THEN tcc.CTIME
 WHEN bb.CTIME IS NOT NULL THEN bb.CTIME
 END AS CTIME, t.PRO_METHOD AS pro_method
     FROM my_customer t
 LEFT OUTER JOIN first_ auth tgua
 ON t.con_no = tgua.con_no
 AND tgua.stat = 1
 AND tgua.type = 'C' and 
(tgua.atime>= '2017-06-22' and tgua.atime< '2017-06-23')
 LEFT OUTER JOIN my_ contact tcc
 ON t.con_no = tcc.con_no
 AND tcc.NAME IS NOT NULL and 
(tcc.ctime>= '2017-06-22' and tcc.ctime< '2017-06-23')
 LEFT OUTER JOIN (
 SELECT tcp.con_no AS con_no, tcai.CTIME AS CTIME
 FROM my_ person tcp
 INNER JOIN my_auth_info tcai
 ON tcp.CNO = tcai.CNO and 
(tcai.ctime>= '2017-06-22' and tcai.ctime< '2017-06-23')
 ) bb
 ON t.con_no = bb.con_no 
 WHERE t.PRO_METHOD = 'uc' and (tgua.con_no IS NOT NULL
 OR tcc.con_no IS NOT NULL
 OR bb.con_no IS NOT NULL)) pp order by pp.con_no

sql4：

select * from 
 (SELECT DISTINCT t.con_no AS con_no
 , CASE 
                             WHEN tgua.atime IS NOT NULL THEN tgua.atime
 WHEN tcc.CTIME IS NOT NULL THEN tcc.CTIME
 WHEN bb.CTIME IS NOT NULL THEN bb.CTIME
     END AS CTIME, t.PRO_METHOD AS pro_method
 FROM my_customer t
 LEFT OUTER JOIN first_ auth tgua
 ON t.con_no = tgua.con_no
 AND tgua.status = 1
 AND tgua.type = 'C'
 LEFT OUTER JOIN my_ contact tcc
 ON t.con_no = tcc.con_no
 AND tcc.NAME IS NOT NULL
 LEFT OUTER JOIN (
 SELECT tcp.con_no AS con_no, tcai.CTIME AS CTIME
 FROM my_ person tcp
 INNER JOIN my_auth_info tcai
 ON tcp.CNO = tcai.CNO
 ) bb
 ON t.con_no = bb.con_no
 WHERE t.pro_method = 'uc' and (tgua.con_no IS NOT NULL
 OR tcc.con_no IS NOT NULL
 OR bb.con_no IS NOT NULL)) p where CTIME>= '2017-06-22' and CTIME< '2017-06-23'
 order by con_no

找出sql3比sql4多出的执行结果记录，如编号为4534的一条记录, 这条记录在sql4中很有可能被过滤掉了，因此修改sql4外层的where查询条件，如下：

select * from 
 (SELECT DISTINCT t.con_no AS con_no
 , CASE 
  WHEN tgua.atime IS NOT NULL THEN tgua.atime
    WHEN tcc.CTIME IS NOT NULL THEN tcc.CTIME
    WHEN bb.CTIME IS NOT NULL THEN bb.CTIME
  END AS CTIME, t.PRO_METHOD AS pro_method
  FROM my_customer t
 LEFT OUTER JOIN first_ auth tgua
 ON t.con_no = tgua.con_no
 AND tgua.status = 1
 AND tgua.type = 'C'
 LEFT OUTER JOIN my_ contact tcc
 ON t.con_no = tcc.con_no
 AND tcc.NAME IS NOT NULL
 LEFT OUTER JOIN (
 SELECT tcp.con_no AS con_no, tcai.CTIME AS CTIME
 FROM my_ person tcp
 INNER JOIN my_auth_info tcai
 ON tcp.CNO = tcai.CNO
 ) bb
 ON t.con_no = bb.con_no
 WHERE t.pro_method = 'uc' and (tgua.con_no IS NOT NULL
 OR tcc.con_no IS NOT NULL
 OR bb.con_no IS NOT NULL)) p where con_no='4534'

查询结果发现CTIME对应的值竟是“2017-06-23 00:18:07“。而在sql3执行结果中，CTIME值为2017-06-22 00:16:08。问题出在这一sql片段中：

 CASE 
            WHEN tgua.atime IS NOT NULL THEN tgua.atime
   WHEN tcc.CTIME IS NOT NULL THEN tcc.CTIME
             WHEN bb.CTIME IS NOT NULL THEN bb.CTIME
          END AS CTIME, t.PRO_METHOD AS pro_method

在sql3的执行结果中，由于时间筛选在内层的left join后面，时间不是2017-06-22的记录字段值都用null代替，因此在结果集中，无论是tgua.atime，tcc.CTIME 还是bb.CTIME，时间不是null就是2017-06-22 ，而在sql4中，tgua.atime、tcc.CTIME 、bb.CTIME的时间是不一致的，因此假如case when选择一个not null的时间值，且该时间刚好不是2017-06-22，那么这条记录会在外层的where筛选中被过滤掉。而我们希望的却是tgua.atime、tcc.CTIME 、bb.CTIME只要有一个时间是在2017-06-22，那这条记录就应该出现在结果集中。使用union语法，修改如下：

select distinct * from 
 ((SELECT DISTINCT t.con_no AS con_no 
 , DATE_FORMAT(tgua.atime,"%Y-%m-%d") AS ctime, t.PRO_METHOD AS pro_method
 FROM my_customer t
 inner JOIN first_ auth tgua
 ON t.con_no = tgua.con_no
 AND tgua.status = 1
 AND tgua.type = 'C')
 union
 (SELECT DISTINCT t.con_no AS con_no 
 , DATE_FORMAT(tcc.ctime,"%Y-%m-%d") AS ctime, t.PRO_METHOD AS pro_method
 FROM my_customer t
 inner JOIN
  my_ contact tcc
 ON t.con_no = tcc.con_no
 AND tcc.NAME IS NOT NULL)
 union
 (SELECT DISTINCT t.con_no AS con_no 
 ,DATE_FORMAT(bb.ctime,"%Y-%m-%d") AS ctime, t.PRO_METHOD AS pro_method
 FROM my_customer t
 inner JOIN (
 SELECT tcp.con_no AS con_no, tcai.ctime AS ctime
 FROM my_ person tcp
 INNER JOIN my_auth_info tcai
 ON tcp.CNO = tcai.CNO
 ) bb
 ON t.con_no = bb.con_no)) p where pro_method = 'uc' and ctime>= '2017-06-22' and ctime< '2017-06-23'
 order by con_no

查出的记录数恰好与sql1中的一致，在此基础上进行group by分组统计处理。

总结

多表关联查询排错遇到问题时，要通过select *打印出具体的明细，进行对比，分析其特征，再反观sql思考其错误的可能原因，再修改，再排查，这是一个不断从现象到本质，从思考到实践的循环上升的过程，这也是修改一切bug的要旨所在。另外，越复杂的sql越容易出错，而且不利于修改维护，因此，在应用层编写代码时，sql越简单越好。

参考文章：mysql left( right ) join使用on 与where 筛选的差异

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2017-07-02，如有侵权请联系 cloudcommunity@tencent.com 删除

sql