在线等 请教mysql group by 问题

Question

表：mr_cpaspreadinfo_20160531
       id cpaid type
       1   2       1
       2   3        1
       3   3        0 mysql:SELECT t1.cpaid,t2.count1 FROM (SELECT cpaid FROM mr_cpaspreadinfo_20160531 GROUP BY cpaid) as t1
 LEFT JOIN (SELECT cpaid,COUNT(*) as count1 FROM mr_cpaspreadinfo_20160531 WHERE type =1) as t2 ON t1.cpaid=t2.cpaid错误结果：cpaid count1
                     2           2
                     3         null
我想得到的正确结果： cpaid    count1
                                2             1
                                3             1 

Answer 1

 SELECT t1.cpaid,IFNULL(t2.count1,0) FROM (SELECT cpaid FROM mr_cpaspreadinfo_20160531 GROUP BY cpaid) as t1
 LEFT JOIN (SELECT cpaid,COUNT(*) as count1 FROM mr_cpaspreadinfo_20160531 WHERE type =1) as t2 ON t1.cpaid=t2.cpaid

Answer 2

改成这样，加上group by：SELECT  t1.cpaid ,
        t2.count1
FROM    ( SELECT    cpaid
          FROM      mr_cpaspreadinfo_20160531
          GROUP BY  cpaid
        ) AS t1
        LEFT JOIN ( SELECT  cpaid ,
                            COUNT(*) AS count1
                    FROM    mr_cpaspreadinfo_20160531
                    WHERE   type = 1
group by cpaid
                  ) AS t2 ON t1.cpaid = t2.cpaid

Answer 3

SELECT  cpaid ,
                            COUNT(*) AS count1
                    FROM    mr_cpaspreadinfo_20160531
                    WHERE   type = 1
group by cpaid直接就出来结果，为什么要多此一举做一次join呢