SQL:如何从单个列中选择满足多个条件的单个ID(“行") [英] SQL: how to select a single id ("row") that meets multiple criteria from a single column

问题描述

我有一个非常狭窄的表:祖先user_id.

I have a very narrow table: user_id, ancestry.

user_id栏不言自明.

The user_id column is self explanatory.

祖先"列包含用户祖先所在的国家/地区.

The ancestry column contains the country from where the user's ancestors hail.

用户可以在表上有多行，因为用户可以有来自多个国家的祖先.

A user can have multiple rows on the table, as a user can have ancestors from multiple countries.

我的问题是:如何选择祖先来自多个指定国家的用户?

My question is this: how do I select users whose ancestors hail from multiple, specified countries?

例如，向我显示拥有英格兰，法国和德国祖先的所有用户，并为每个符合条件的用户返回1行.

For instance, show me all users who have ancestors from England, France and Germany, and return 1 row per user that met that criteria.

那是什么SQL?

 user_id     ancestry

---------   ----------

    1        England
    1        Ireland
    2        France
    3        Germany
    3        Poland
    4        England
    4        France
    4        Germany
    5        France
    5        Germany

对于上述数据，由于user_id 4具有来自英格兰，法国和德国的祖先，我希望结果为"4".

In the case of the data above, I would expect the result to be "4" as user_id 4 has ancestors from England, France and Germany.

谢谢.

P.S.需要说明的是:是的，user_id/祖先列构成唯一对，因此不会为给定用户重复国家/地区.

P.S. To clarify: Yes, the user_id / ancestry columns make a unique pair, so a country would not be repeated for a given user.

P.P.S.我正在寻找来自三个国家(英国，法国和德国)的用户(并且这些国家是任意的).

P.P.S. I am looking for users who hail from all 3 countries - England, France, AND Germany (and the countries are arbitrary).

P.P.P.S.我不是在寻找特定于RDBMS的答案.我希望总体上回答这个问题.

P.P.P.S. I am not looking for answers specific to a certain RDBMS. I'm looking to answer this problem "in general."

我很满意为每个查询重新生成where子句，只要可以通过编程方式生成where子句即可(例如，我可以构建一个函数来构建WHERE/FROM-WHERE子句).

I'm content w regenerating the where clause for each query provided generating the where clause can be done programmatically (e.g. that I can build a function to build the WHERE / FROM - WHERE clause).

推荐答案

尝试一下:

Select user_id
from yourtable
where ancestry in ('England', 'France', 'Germany')
group by user_id
having count(user_id) = 3

最后一行表示用户的血统具有所有3个国家/地区.

The last line means the user's ancestry has all 3 countries.

这篇关于SQL:如何从单个列中选择满足多个条件的单个ID(“行")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！