关于 mysql group by 统计问题

Question

你好，先看数据库表数据：
表：mr_cpaspreadinfo_20160531
      id cpaid type
      1   2       1
      2   3        1
      3   3        0mysql: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