SQL:输出属于其中所有国家/地区都相同的类别的所有名称 [英] SQL: Output all names which belong to a category where all the countries within it are the same

问题描述

我有下表称为商店:

Name          |   Country   |   Category 
Pharmacy          Japan         Health
Green Vine        Italy         Dining
Red Palace        Morocco       Dining 
La Pizza          Italy         Dining 
Nature Shop       Japan         Health
Medical 100       Japan         Health

我想编写一个查询，该查询输出所有国家/地区都属于同一类别(例如，健康"类别中的所有商店都在日本)的所有名称.例如.输出为:

I want to write a query that outputs all the Names that belong to a category where all the countries are the same (e.g. all the stores in the Health category are in Japan). E.g. the output would be:

Name       |  Country  |  Category
Pharmacy      Japan       Health
Nature Shop   Japan       Health
Medical 100   Japan       Health

我知道如何计算每个类别的条目数，但是如何判断一个类别中的所有国家/地区是否相同?任何见解都会受到赞赏.

I know how to count the number of entries per category, but how can I tell whether all the countries within a category are the same? Any insights are appreciated.

推荐答案

您可以将聚合与HAVING子句一起使用，该子句可检查类别的最小国家/地区与最大国家/地区相同，即所有国家/地区都是相同(如果不能存在NULL，似乎是这种情况).然后内部加入这些类别.

You can use aggregation with a HAVING clause, that checks that the minimum country is the same as the maximum country for a category, i.e. all countries are the same (if there cannot be NULLs, which seems to be the case). Then inner join these categories.

SELECT *
       FROM elbat t1
            INNER JOIN (SELECT t2.category
                               FROM elbat t2
                               GROUP BY t2.category
                               HAVING max(t2.country) = min(t2.country)) x
                       ON x.category = t1.category;

这篇关于SQL:输出属于其中所有国家/地区都相同的类别的所有名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！