MySQL-两表有重复数据，如何高效的得到去掉重复条目后两表总数

Question

a表

Qid               pname       Time        
aaaa...           A组         2011-2-1
zzzz...           A组         2011-2-1
bbbb...           A组         2011-2-7
cccc..            B组         2011-2-2
dddd..            C组         2011-2-3
eeee...           B组         2011-2-1

Answer 1

 SELECT Time, panme, atmp.aCount, btmp.bCount, (atmp.aCount+btmp.bCount) AS total FROM (SELECT Time, panme,COUNT(Qid) AS aCount FROM a GROUP BY Qid) AS atmp
LEFT JOIN (SELECT Time, panme, COUNT(Qid) AS bCount FROM b GROUP BY Qid) AS btmp ON atmp.Qid = btmp.Qid
ORDET BY Time

Answer 2

下面是我测试的sql，里面包括创建临时表结构及数据：

create table a(Qid varchar(20),pname varchar(20),Time varchar(20));
create table b(Qid varchar(20),pname varchar(20),Time varchar(20));

insert into a values('aaaa...','A组','2011-2-1');
insert into a values('zzzz...','A组','2011-2-1');
insert into a values('bbbb...','A组','2011-2-7');
insert into a values('cccc...','B组','2011-2-2');
insert into a values('dddd...','C组','2011-2-3');
insert into a values('eeee...','B组','2011-2-1');

insert into b values('aaaa...','A组','2011-2-1');
insert into b values('bbbb...','A组','2011-2-7');
insert into b values('eeee...','B组','2011-2-1');
insert into b values('ffff...','W组','2011-2-5');
insert into b values('xxxx...','A组','2011-2-1');
insert into b values('yyyy...','A组','2011-2-1');

//查询sql
SELECT t.Time,t.pname,sum(t.Acount),sum(t.Bcount),t2.total 
FROM 
(
    SELECT Time,pname,count(pname) AS aCount,'0' as bCount FROM a GROUP BY pname,Time 
    UNION ALL 
    SELECT Time,pname,'0' AS aCount,count(pname) AS bCount FROM b GROUP BY pname,Time
)t 
LEFT JOIN  
(
    SELECT Time,pname,count(DISTINCT Qid) AS total FROM(SELECT * FROM a UNION ALL SELECT * FROM b)m GROUP BY pname,Time
)t2 on t.pname=t2.pname and t.Time=t2.Time 
GROUP BY pname,Time 
ORDER BY Time;

select 结果：

+----------+-------+---------------+---------------+-------+
| Time     | pname | sum(t.Acount) | sum(t.Bcount) | total |
+----------+-------+---------------+---------------+-------+
| 2011-2-1 | A组   |             2 |             3 |     4 |
| 2011-2-1 | B组   |             1 |             1 |     1 |
| 2011-2-2 | B组   |             1 |             0 |     1 |
| 2011-2-3 | C组   |             1 |             0 |     1 |
| 2011-2-5 | W组   |             0 |             1 |     1 |
| 2011-2-7 | A组   |             1 |             1 |     1 |
+----------+-------+---------------+---------------+-------+