mysql - 选择 COUNT 个非零单元 mysql

Question

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

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

标签： mysqlsqlcount

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;

尽管我简化了您的查询，但基本相同。还有其他写这个的方法，不涉及任何子查询。