sql - 查询在瑞士获得计数至少为 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

标签： sqlsql-server-2008

Answer 1

count 用于非空值，当 country = '...' 总是 != 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