oracle inner join 隐藏的多次连接

最新推荐文章于 2022-01-22 18:47:32 发布

最新推荐文章于 2022-01-22 18:47:32 发布 · 120 阅读

文章标签：

本文探讨了两个SQL查询在相同条件下返回不同结果的原因。通过对具体数据样例的分析，指出了问题所在，并提供了一个修正后的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