再次使用mysql "count distinct“

Question

我对mysql计数有一个奇怪的问题。当我执行的时候

   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)

我得到了：

​

​

这没问题，因为我有两个记录。

当我执行的时候

 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，并且在计数distinct之后，我想要得到

`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

但它不起作用，因为在第一种方法中，没有使distinct和sum所有重复的值大于0；而在第二种情况下，它只给出了1和0。

那么，有人能帮我吗？提前谢谢你。狮子座

Answer 1

我知道count distinct不会计算零值。，

。

我不知道你是从哪里得到这个想法的，但它是不正确的。也许你在考虑null值？获得您想要的结果的一种方法是在您的distinct计数中将0视为空。

尝试这样做(我也删除了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)

Answer 2

SELECT COUNT(DISTINCT a.inc) AS inc,
       COUNT(DISTINCT NULLIF(a.cls, 0)) AS cls,
       COUNT(DISTINCT NULLIF(a.ord, 0)) AS ord,
       COUNT(DISTINCT NULLIF(a.fam, 0)) AS fam,
       COUNT(DISTINCT NULLIF(a.subfam, 0)) AS subfam,
       COUNT(DISTINCT NULLIF(a.gen, 0)) AS gen,
       COUNT(DISTINCT NULLIF(a.spec, 0)) AS spec,
       COUNT(DISTINCT NULLIF(a.subspec, 0)) AS subspec
FROM a