mysql - 再次mysql "count distinct"

Question

我有一个关于 mysql count 的奇怪问题。当我执行

   SELECT a.inc AS inc,
       a.cls AS cls,
       a.ord AS ord,
       a.fam AS fam,
       a.subfam AS subfam,
       a.gen AS gen,
       aspec AS spec,
       asubspec AS subspec
    FROM a
    WHERE (ainc = 11)

我得到:

没关系，因为我有 2 条记录。

当我执行

 SELECT COUNT(DISTINCT a.inc) AS inc,
       COUNT(DISTINCT a.cls) AS cls,
       COUNT(DISTINCT a.ord) AS ord,
       COUNT(DISTINCT a.fam) AS fam,
       COUNT(DISTINCT asubfam) AS subfam,
       COUNT(DISTINCT a.gen) AS gen,
       COUNT(DISTINCT a.spec) AS spec,
       COUNT(DISTINCT a.subspec) AS subspec
    FROM a
    WHERE (a.inc = 11)
    GROUP BY a.inc

我得到

这很奇怪，因为如你所见gen, spec and subspec在一行上有 0 个值。我知道count distinct不计算零值。我想计算所有值!= 0 并且在计算不同之后我想得到

`1 | 2 | 2 | 2 | 2 | 1 | 1 | 1 |`

我也试试:

SELECT COUNT(DISTINCT a.inc) AS inc,
       SUM(if(a.cls <> 0, 1, 0)) AS cls,
       SUM(if(a.ord <> 0, 1, 0)) AS ord,
       SUM(if(a.fam <> 0, 1, 0)) AS fam,
       SUM(if(a.subfam <> 0, 1, 0)) AS subfam,
       SUM(if(a.gen <> 0, 1, 0)) AS gen,
       SUM(if(a.spec <> 0, 1, 0)) AS spec,
       SUM(if(a.subspec <> 0, 1, 0)) AS subspec
  FROM a
GROUP BY a.inc

和

SELECT COUNT(DISTINCT a.inc) AS inc,
           SUM(DISTINCT if(a.cls <> 0, 1, 0)) AS cls,
           SUM(DISTINCT if(a.ord <> 0, 1, 0)) AS ord,
           SUM(DISTINCT if(a.fam <> 0, 1, 0)) AS fam,
           SUM(DISTINCT if(a.subfam <> 0, 1, 0)) AS subfam,
           SUM(DISTINCT if(a.gen <> 0, 1, 0)) AS gen,
           SUM(DISTINCT if(a.spec <> 0, 1, 0)) AS spec,
           SUM(DISTINCT if(a.subspec <> 0, 1, 0)) AS subspec
FROM a
GROUP BY a.inc

但它不起作用，因为在第一种方法中不会区分所有大于 0 的重复值；在第二种情况下，它只给出 1 和 0 。
那么，有人可以帮我吗？先感谢您。狮子座

Answer 1

I know that count distinct doesn't count zero values.

我不知道你从哪里得到这个想法，但这是不正确的。也许您正在考虑 NULL 值？获得所需结果的一种方法是在非重复计数中将 0 视为 NULL。

尝试这样的事情(我也删除了 group by，这没有帮助):

SELECT COUNT(DISTINCT case when a.inc = 0 then null else a.inc end) AS inc,
       COUNT(DISTINCT case when a.cls = 0 then null else a.cls end) AS cls,
       COUNT(DISTINCT case when a.ord = 0 then null else a.ord end) AS ord,
       COUNT(DISTINCT case when a.fam = 0 then null else a.fam end) AS fam,
       COUNT(DISTINCT case when a.subfam = 0 then null else a.subfam end) AS subfam,
       COUNT(DISTINCT case when a.gen = 0 then null else a.gen end) AS gen,
       COUNT(DISTINCT case when a.spec = 0 then null else a.spec end) AS spec,
       COUNT(DISTINCT case when a.subspec = 0 then null else a.subspec end) AS subspec
    FROM a
    WHERE (a.inc = 11)