计数如果MySql具有Distinct

Question

请帮我查询，我有这个数据

customeid | param
1001      |  A
1001      |  B
1001      |  C
1002      |  B
1002      |  A
1003      |  A
1003      |  B
1004      |  A

我需要区分customerid，我使用了这个查询，但结果不正确

SELECT
COUNT(DISTINCT IF(param not in ('A'),1,0))
FROM
table

输出结果

count   
3 (from customeid 1001,1002,1003)

如何用if param不在A中来区分customerid，并且不能添加where查询

Answer 1

您使用countif和distinct来判断没有问题，但存在问题，如果满足条件，则需要返回customeid，否则需要返回null

您可以关注此查询。

SELECT
COUNT(DISTINCT IF(param not in ('A'),customeid,NULL))
FROM
table

sqlfiddle

注意

当该值为null时，COUNT不会累积

Answer 2

select customeid,count(customeid)
from table
where param <>'A'
group by customeid

Answer 3

使用case when和聚合函数group by

select customeid,count(*) as Count
from (    
select (case when param!='A' then customeid else end) as customeid    from your_table
) T group by customeid

Answer 4

SELECT DISTINCT COSTUMEID, COUNT(DISTINCT (PARAM))
FROM TABLE 
WHERE PARAM NOT IN (('A'),1,0) 
GROUP BY COSTUMEID