oracle inner join 隐藏的多次连接

最新推荐文章于 2023-10-31 14:33:42 发布

原创最新推荐文章于 2023-10-31 14:33:42 发布 · 2.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#join #oracle #date #user #c

Oracle 开发专栏收录该内容

129 篇文章

订阅专栏

本文探讨了两段SQL查询在处理相同数据集时产生的不同结果。主要关注人数和支付金额的统计差异，尤其是在涉及多个ID的情况下。通过对SQL语句的调整，最终解决了统计不一致的问题。

Select bu.f_Comechannel,Count(Distinct x.f_username) x ,Sum(bs.f_Paymoney) pmoney
From T_BASE_USER_FIRST_TRADE_LOTTY x
Inner Join t_Base_Userinfo bu On bu.f_Username = x.f_username
Inner Join t_Base_Succprouser bs On bs.f_Username = x.f_username
Where x.f_lotid IN (9)
And bs.f_lotid IN (9)
And x.f_tradetime Between to_date('2009-01-01','yyyy-mm-dd') And to_date('2009-09-26','yyyy-mm-dd')
And bs.f_Expectenddate Between to_date('2009-01-01','yyyy-mm-dd') And to_date('2009-09-26','yyyy-mm-dd')
Group By bu.f_Comechannel

和

SELECT COUNT (distinct b.f_username) num, SUM (b.f_paymoney) allmoney
FROM t_base_succprouser b
WHERE b.f_lotid IN (9)
   AND b.f_username IN
    (
          SELECT c.f_username
            FROM t_base_user_first_trade_lotty c
           WHERE c.f_tradetime BETWEEN TO_DATE ('2009/01/01-00:00:00', 'YYYY/MM/DD-HH24:MI:SS')
                                       AND TO_DATE ('2009/09/26-00:00:00','YYYY/MM/DD-HH24:MI:SS' )
             AND c.f_lotid IN (9)
                        )
   AND b.f_expectenddate BETWEEN TO_DATE ('2009/01/01-00:00:00''YYYY/MM/DD-HH24:MI:SS' )
                             AND TO_DATE ('2009/09/26-00:00:00', 'YYYY/MM/DD-HH24:MI:SS')

人数和金额一样

当是以下就不一样了

SELECT COUNT (distinct b.f_username) num, SUM (b.f_paymoney) allmoney
FROM t_base_succprouser b
WHERE b.f_lotid IN (1, 15, 17, 10000)
   AND b.f_username IN
    (
          SELECT c.f_username
            FROM t_base_user_first_trade_lotty c
           WHERE c.f_tradetime BETWEEN TO_DATE ('2009/01/01-00:00:00', 'YYYY/MM/DD-HH24:MI:SS')
                                       AND TO_DATE ('2009/09/26-00:00:00','YYYY/MM/DD-HH24:MI:SS' )
             AND c.f_lotid IN (1, 15, 17, 10000)
                        )
   AND b.f_expectenddate BETWEEN TO_DATE ('2009/01/01-00:00:00''YYYY/MM/DD-HH24:MI:SS' )
                             AND TO_DATE ('2009/09/26-00:00:00', 'YYYY/MM/DD-HH24:MI:SS')

和

Select bu.f_Comechannel,Count(Distinct x.f_username) x ,Sum(bs.f_Paymoney) pmoney
From T_BASE_USER_FIRST_TRADE_LOTTY x
Inner Join t_Base_Userinfo bu On bu.f_Username = x.f_username
Inner Join t_Base_Succprouser bs On bs.f_Username = x.f_username
Where x.f_lotid IN (1, 15, 17, 10000)
And bs.f_lotid IN (1, 15, 17, 10000)
And x.f_tradetime Between to_date('2009-01-01','yyyy-mm-dd') And to_date('2009-09-26','yyyy-mm-dd')
And bs.f_Expectenddate Between to_date('2009-01-01','yyyy-mm-dd') And to_date('2009-09-26','yyyy-mm-dd')
Group By bu.f_Comechannel

人数一样，金额差别很多！统计的表一样，时间条件一样，ID也一样，上面语句产生同样的结构，为什么多增加几个ID就不一样呢？

为什么呢？因为根据f_username 连接

USERNAME   ID   MONEY   USERNAME   ID   MONEY
1   a   17   700   a   1   200
2   a   15   600   a   1   200
3   a   1   500   a   1   200
4   a   17   700   a   15   300
5   a   15   600   a   15   300
6   a   1   500   a   15   300
7   a   17   700   a   17   400
8   a   15   600   a   17   400
9   a   1   500   a   17   400
10   b   15   200   b   1   400
11   b   1   100   b   1   400
12   b   15   200   b   15   400
13   b   1   100   b   15   400

最后改成

Select
   bu.f_Comechannel,Count(Distinct bs.f_username) x ,Sum(bs.f_Paymoney) pmoney
From t_Base_Succprouser bs
Inner Join t_Base_Userinfo bu On   bu.f_Username = bs.f_username

Where bs.f_lotid IN (1, 15, 17, 10000)
And bs.f_Expectenddate Between to_date('2009-01-01','yyyy-mm-dd') And to_date('2009-09-26','yyyy-mm-dd')
And bs.f_username In(
        SELECT c.f_username
            FROM t_base_user_first_trade_lotty c
           WHERE c.f_tradetime BETWEEN TO_DATE ('2009/01/01-00:00:00', 'YYYY/MM/DD-HH24:MI:SS')
                                       AND TO_DATE ('2009/09/26-00:00:00','YYYY/MM/DD-HH24:MI:SS' )
             AND c.f_lotid IN (1, 15, 17, 10000)
                        )
Group By bu.f_Comechannel