查询在瑞士获取计数至少为1的名称

Question

如何解决这个问题?

在下面的查询中，我想在结果中包含所有在瑞士计数至少为1的名称。

我已经尝试创建一个临时表，我认为它的工作，但必须有一个最简单的方法来解决:

SELECT c.name, 
COUNT(CASE WHEN country = 'Germany' THEN 1 END) as 'Germany', 
COUNT(CASE WHEN country = 'London' THEN 1 END) as 'London',     
COUNT(CASE WHEN country = 'Switzerland' THEN 1 END) as 'Switzerland',   
COUNT(CASE WHEN country NOT IN('London', 'Ireland') THEN 1 END) as 'others' 
FROM a_products p 
JOIN a_customers c 
on id_customer = c.id 
Group By name

潜在的解决方案:

select *
into #Swiss
from a_products
where country = 'Switzerland' 
SELECT c.name, 
COUNT(CASE WHEN country = 'Germany' THEN 1 END) as 'Germany', 
COUNT(CASE WHEN country = 'London' THEN 1 END) as 'London',     
COUNT(CASE WHEN country = 'Switzerland' THEN 1 END) as 'Switzerland',   
COUNT(CASE WHEN country NOT IN('London', 'Ireland') THEN 1 END) as 'others' 
FROM a_products p 
JOIN a_customers c 
on p.id_customer = c.id 
JOIN #Swiss s
on p.id_customer = s.id_customer
where s.id_customer is not null
Group By name

Answer 1

count表示非空值，当country = '…' is always != from null在这种情况下，您可以尝试使用SUM并将检查值管理为1而不检查为0

SELECT c.name, 
SUM(CASE WHEN country = 'Germany' THEN 1 ELSE 0 END ) as Germany, 
SUM(CASE WHEN country = 'London' THEN 1 ELSE 0 END ) as London,     
SUM(CASE WHEN country = 'Switzerland' THEN 1 ELSE 0 END ) as Switzerland,   
SUM(CASE WHEN country NOT IN('London', 'Ireland') THEN 1 ELSE 0 END ) as others
FROM a_products p 
JOIN a_customers c 
on id_customer = c.id 
Group By name

对于筛选瑞士的值，可以使用

SELECT c.name, 
SUM(CASE WHEN country = 'Germany' THEN 1 ELSE 0 END ) as Germany, 
SUM(CASE WHEN country = 'London' THEN 1 ELSE 0 END ) as London,     
SUM(CASE WHEN country = 'Switzerland' THEN 1 ELSE 0 END ) as Switzerland,   
SUM(CASE WHEN country NOT IN('London', 'Ireland') THEN 1 ELSE 0 END ) as others
FROM a_products p 
JOIN a_customers c 
on id_customer = c.id 
Group By name
HAVING SUM(CASE WHEN country = 'Switzerland' THEN 1 ELSE 0 END ) >=1 

或

SELECT c.name,  
SUM(CASE WHEN country = 'Switzerland' THEN 1 ELSE 0 END ) as Switzerland 
FROM a_products p 
JOIN a_customers c 
on id_customer = c.id 
Group By name
HAVING SUM(CASE WHEN country = 'Switzerland' THEN 1 ELSE 0 END ) >=1