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