一条长sql的排错过程

过程

有这样一条长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 筛选的差异

java达人

ID:java_daren

(长按或扫码识别)

                   

友情推荐

虾说十话

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值