mysql - SELECT COUNT 非零单元格 mysql

Question

如何计算此查询中非零的 cc.audio 值的 COUNT？

SELECT cc.audio FROM
(SELECT cid, SUM(mp='audio') audio, SUM(mp='text') texts, SUM(mp='video') video
FROM
  ( SELECT ccm.cid, cmp.cmid, cmp.mp
    FROM a as ccm
    INNER JOIN b as cmp
    ON ccm.mid = cmp.cmid) AS tmp
    GROUP BY cid) AS cc

示例:

cid audio texts video   
----------------------
1    1      1     1
2    1      1     1 

Expected results: 2

Answer 1

您的问题表明:

SELECT COUNT(*)
FROM (
      SELECT cid, SUM(mp='audio') as audio, SUM(mp='text') as texts, SUM(mp='video') as video
      FROM a ccm INNER JOIN
           b cmp
           ON ccm.mid = cmp.cmid
      GROUP BY cid
    ) cc
WHERE audio > 0;

虽然我简化了你的查询，但基本上是一样的。还有其他不涉及任何子查询的方法可以编写此代码。