对全部(未知、已知)、未知、已知客户进行SQL查询

Question

Id, CustomerId, IsKnownCustomer,phonemacaddress

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

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

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

count Customer, customer type  
7                 all
3                 unknown customer
4                 known customer

Answer 1

你可以用这个。

SELECT COUNT(DISTINCT phonemacaddress) [count Customer] , 
    CASE 
        WHEN GROUPING(IsKnownCustomer) = 1 THEN 'all' 
        WHEN IsKnownCustomer = 1 THEN 'known customer' 
        WHEN IsKnownCustomer = 0 THEN 'unknown customer' END [customer type] 
FROM @CustomerTable
GROUP BY IsKnownCustomer WITH ROLLUP
ORDER BY GROUPING(IsKnownCustomer) DESC, IsKnownCustomer 

结果：

count Customer customer type
-------------- ----------------
7              all
3              unknown customer
4              known customer

Answer 2

尝试此查询

select count(Id), 'All' as customer_type  from customers
union
select count(Id), 'Unknown' as customer_type  from customers
WHERE isKnownCustomer =0
union
select count(Id), 'known' as customer_type  from customers
WHERE isKnownCustomer =1;

http://sqlfiddle.com/#!9/3e7616/15