按电话Mac地址唯一标识全部(未知、已知)、未知、已知客户的SQL查询

Question

表1(客户表)

Id、CustomerId、IsKnownCustomer、电话地址

1，NULL 0 00:9a:34:cf:a4

2,004024 1 00:6f:64:cf:a0:93

3,004024 1 00:6f:64:cf:a0:93

4,004003 1 2c:0e:3d:15:12:43

6，NULL 0 2c:0e:3d:15:125

7,004003 1 34:f3:9a:ee:53:a9

8,004023 1 38:0B:40:DC:BA:3A

9，NULL 0 4c:66:41:11:26:16

这是该表，所有客户、未知客户和已知客户都需要数据。

输出如下:根据语音地址唯一地查找记录。

计数客户，|客户类型

7全部

3个未知客户

4个已知客户

Answer 1

您可以使用联合和计数

 select count(*), 'all'
 from customer
 union 
 select count(*), 'unknown customer'
 from customer
 where isKnowCustomer = 0
 union 
 select count(*), 'known customer'
 from customer
 where isKnowCustomer = 1

Answer 2

我只想把这个放在一行：

select count(*) as all,
       sum(IsKnownCustomer) as known,
       sum(1 - IsKnownCustomer) as unknown
from t;

我不明白为什么你想要三行而不是三列。

如果这样做，您可以执行以下操作：

select (case when isKnownCustomer = 0 then 'Unknown'
             when isKnownCustomer = 1 then 'Known'
             else 'All'
        end) as which, count(*)
from t
group by grouping sets ( (IsKnownCustomer), () )

Answer 3

下面的SQL将会对你有所帮助。

SELECT  
    SUM(CASE WHEN Isknown = 1 THEN 1 ELSE 0 END) AS 'Known Customer',
    SUM(CASE WHEN Isknown = 0 THEN 1 ELSE 0 END) AS 'Unknown Customer',
    COUNT(*) AS 'All' 
FROM YourTable