我在MySQL数据库中有一个名为sales
的表,如下所示:
company manufactured shipped
Mercedes Germany United States
Mercedes Germany Germany
Mercedes Germany United States
Toyota Japan Canada
Toyota Japan England
Audi Germany United States
Audi Germany France
Audi Germany Canada
Tesla United States Mexico
Tesla United States Canada
Tesla United States United States
这是一把小提琴:http://www.sqlfiddle.com/#!17/145ff/3
我想返回将ALL个产品发货到国际的公司列表(即,其中manufactured
列中的值不同于shipped
列中ALL records个特定company
的值).
使用上面的示例,所需的结果集将为:
company
Toyota
Audi
以下是我的(黑客)try :
WITH temp_table AS (
SELECT
s.company AS company
, SUM(CASE
WHEN s.manufactured != s.shipped THEN 1
ELSE 0
END
) AS count_international
, COUNT(s.company) AS total_within_company
FROM
sales s
GROUP BY
s.company
)
SELECT
company
FROM
temp_table
WHERE count_international = total_within_company
从本质上讲,我计算列不匹配的实例.然后,我判断这些不匹配实例的总和是否与给定组中的记录数量匹配.
这种方法是works,但它远不是一个优雅的解决方案!
有没有人能就实现该查询的一种更惯用的方式提供建议?
谢谢!