一条SQL查询结果集异常

Question

 SELECT b.user_id, b.campaign_name,count(bouncetype) as C_total_bounce, sum(case when bouncetype='hardbounce' then 1 else 0 end) as C_hard_bounce FROM ftbounce_campaign b,ftpush_campaign fc WHERE b.user_id=fc.user_id and b.campaign_name=fc.campaign_name and datediff(fc.pushtime,b.bouncetime)<=0 and date_sub(bouncetime,interval 24 hour)<=STR_TO_DATE(fc.pushtime,'%Y-%m-%d %H:%i:%S')  and datediff(fc.pushtime,date('2009
-02-01'))=0 GROUP BY b.user_id, b.campaign_name ORDER BY null;
+--------------------+-------------------+----------------+---------------+
| user_id            | campaign_name     | C_total_bounce | C_hard_bounce |
+--------------------+-------------------+----------------+---------------+
|                    | 20080201CMS060925 |         517911 |        220613 | 
|
mysql> select count(*) from ftpush_campaign;
+----------+
| count(*) |
+----------+
|   538120 | 
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from ftbounce_campaign;
+----------+
| count(*) |
+----------+
|    67858 | 
+----------+
1 row in set (0.00 sec)不知道为什么第一条语句结果会这么大，都超过了 ftbounce_campaign的总记录数，请各位指教，谢谢！

Answer 1

集合 A * 集合 B 的总记录数 = count(A) * count(B) 当然会大于某个记录集的记录数。检查一下你的where后的限制条件。估计有多对多的现象。

Answer 2

b.user_id=fc.user_id and b.campaign_name=fc.campaign_name