---
title: "oracle inner join 隐藏的多次连接"
author: "winzenghua"
date: "2009-09-27T16:16:00Z"
tags: []
slug: "oracle-inner-join"
draft: false
lastmod: "2009-09-27T16:16:00Z"
id: 987843
url: "https://java4gis.iteye.com/blog/987843"
---

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