SQL: как выбрать один идентификатор ( "строка" ), который соответствует нескольким критериям из одного столбца

У меня очень узкая таблица: user_id, ancestry.

Столбец user_id является самоочевидным.

Столбец "Родословная" содержит страну, откуда приветствуют предки пользователей.

Пользователь может иметь несколько строк в таблице, так как пользователь может иметь предков из разных стран.

Мой вопрос заключается в следующем: как выбрать пользователей, чьи предки родом из нескольких указанных стран?

Например, покажите мне всех пользователей, у которых есть предки из Англии, Франции и Германии, и верните 1 строку для каждого пользователя, которая соответствовала этим критериям.

Что такое SQL?

 user_id     ancestry

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

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

В случае данных выше, я ожидаю, что результат будет "4", поскольку user_id 4 имеет предков из Англии, Франции и Германии.

Спасибо заранее.

P.S. Чтобы уточнить: Да, столбцы user_id/ancestry создают уникальную пару, поэтому страна не будет повторяться для данного пользователя.

P.P.S. Я ищу пользователей, которые родом из всех трех стран - Англии, Франции и Германии (и страны произвольны).

P.P.P.S. Я не ищу ответы, относящиеся к определенной СУБД. Я хочу ответить на эту проблему "в целом".

Я доволен регенерацией предложения where для каждого предоставленного запроса, генерируя предложение where, которое может выполняться программно (например, я могу построить функцию для построения предложения WHERE/FROM - WHERE).

Ответ 1

Попробуйте следующее:

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

Последняя строка означает, что у пользователя есть все 3 страны.

Ответ 2

SELECT DISTINCT (user_id) 
FROM [user]
WHERE user.user_id In (select user_id from user where ancestry = 'England') 
    And user.user_id In (select user_id from user where ancestry = 'France') 
    And user.user_id In (select user_id from user where ancestry = 'Germany');`

Ответ 3

Пользователи, имеющие одну из трех стран

SELECT DISTINCT user_id
FROM table
WHERE ancestry IN('England','France','Germany')

Пользователи, у которых есть все 3 страны

SELECT DISTINCT A.userID
FROM table A
   INNER JOIN table B on A.user_id = B.user_id
   INNER JOIN table C on A.user_id = C.user_id
WHERE A.ancestry = 'England'
   AND B.ancestry = 'Germany'
   AND C.ancestry = 'France'

Ответ 4

Первый способ: JOIN:

получить людей с несколькими странами:

SELECT u1.user_id 
FROM users u1
JOIN users u2
on u1.user_id  = u2.user_id 
AND u1.ancestry <> u2.ancestry

Получить людей из двух стран:

SELECT u1.user_id 
FROM users u1
JOIN users u2
on u1.user_id  = u2.user_id 
WHERE u1.ancestry = 'Germany'
AND u2.ancestry = 'France'

Для 3 стран... присоединяйся три раза. Чтобы получить результат один раз, различный.

Второй способ: GROUP BY

Это приведет к тому, что пользователи будут иметь 3 строки (имея... счет), а затем укажите, какие строки разрешены. Обратите внимание: если у вас нет UNIQUE KEY на (user_id, ancestry), пользователь с "id, england", который появляется 3 раза, также будет соответствовать... так что это зависит от структуры вашей таблицы и/или данных.

SELECT user_id 
FROM users u1
WHERE ancestry = 'Germany'
OR ancestry = 'France'
OR ancestry = 'England'
GROUP BY user_id
HAVING count(DISTINCT ancestry) = 3

Ответ 5

один из подходов, если вы хотите получить все user_id, которое удовлетворяет всем условиям:

SELECT DISTINCT user_id FROM table WHERE ancestry IN ('England', '...', '...') GROUP BY user_id HAVING count(*) = <number of conditions that has to be satisfied>

и т.д.. Если вам нужно взять все user_id, которые удовлетворяют хотя бы одному условию, то вы можете сделать

SELECT DISTINCT user_id from table where ancestry IN ('England', 'France', ... , '...')

Я не знаю, есть ли что-то похожее на IN, но которое объединяет условия с AND вместо OR

Ответ 6

грубая сила (и тестируется только в системе Oracle, но я думаю, что это довольно стандартный):

select distinct usr_id from users where user_id in (
    select user_id from (
      Select user_id, Count(User_Id) As Cc
      From users 
      GROUP BY user_id
    ) Where Cc =3
  )
  and ancestry in ('England', 'France', 'Germany')
;

edit: Мне нравится @HuckIt ответ еще лучше.

Ответ 7

Этот вопрос несколько лет, но я пришел через дубликат. Я хочу предложить более общее решение. Если вы знаете, что у вас всегда есть фиксированное число предков, вы можете использовать некоторые собственные объединения, как уже было предложено в ответах. Если вы хотите, чтобы общий подход продолжал чтение.

То, что вам нужно здесь, называется Quotient в реляционной алгебре. Котировщик - это более или менее обратное декартово произведение (или Cross Join в SQL).

Скажем, ваш предок set A is (я использую здесь табличную нотацию, я думаю, что это лучше для понимания)

ancestry
-----------
'England'
'France'
'Germany'

а ваш пользовательский набор U -

user_id
--------
   1
   2
   3

Декартово произведение C=AxU следующее:

user_id  |  ancestry
---------+-----------
   1     | 'England'
   1     | 'France'
   1     | 'Germany'
   2     | 'England'
   2     | 'France'
   2     | 'Germany'
   3     | 'England'
   3     | 'France'
   3     | 'Germany'

Если вы вычисляете заданное значение U=C/A, вы получаете

user_id
--------
   1
   2
   3

Если вы переделаете декартовую продукцию UXA, вы снова получите C. Но учтите, что для набора T, (T/A)xA не обязательно будет воспроизводить T. Например, если T -

user_id  |  ancestry
---------+-----------
   1     | 'England'
   1     | 'France'
   1     | 'Germany'
   2     | 'England'
   2     | 'France'

то (T/A) есть

user_id
--------
   1

(T/A)xA будет тогда

user_id  |  ancestry
---------+------------
   1     | 'England'
   1     | 'France'
   1     | 'Germany'

Обратите внимание, что записи для user_id=2 были устранены операциями Quotient и Cartesian Product.

Ваш вопрос: у какого user_id есть предки из всех стран вашего предка? Другими словами, вы хотите U=T/A, где T - ваш исходный набор (или ваша таблица).

Чтобы реализовать фактор в SQL, вам нужно выполнить 4 шага:

Создайте декартово произведение вашего набора предков и набор все user_ids.
Найти все записи в Cartesian Product, которые не имеют партнера в исходном наборе (Left Join)
Извлеките user_ids из набора результатов из 2)
Возвращает все user_ids из исходного набора, которые не входят в набор результатов из 3)

Так что сделайте это шаг за шагом. Я буду использовать синтаксис TSQL (сервер Microsoft SQL), но его легко адаптировать к другим СУБД. В качестве имени таблицы (user_id, ancestry) я выберите ancestor

CREATE TABLE ancestry_set (ancestry nvarchar(25))
INSERT INTO ancestry_set (ancestry) VALUES ('England')
INSERT INTO ancestry_set (ancestry) VALUES ('France')
INSERT INTO ancestry_set (ancestry) VALUES ('Germany')

CREATE TABLE ancestor ([user_id] int, ancestry nvarchar(25))
INSERT INTO ancestor ([user_id],ancestry) VALUES (1,'England')
INSERT INTO ancestor ([user_id],ancestry) VALUES(1,'Ireland')
INSERT INTO ancestor ([user_id],ancestry) VALUES(2,'France')
INSERT INTO ancestor ([user_id],ancestry) VALUES(3,'Germany')
INSERT INTO ancestor ([user_id],ancestry) VALUES(3,'Poland')
INSERT INTO ancestor ([user_id],ancestry) VALUES(4,'England')
INSERT INTO ancestor ([user_id],ancestry) VALUES(4,'France')
INSERT INTO ancestor ([user_id],ancestry) VALUES(4,'Germany')
INSERT INTO ancestor ([user_id],ancestry) VALUES(5,'France')
INSERT INTO ancestor ([user_id],ancestry) VALUES(5,'Germany')

1) Создайте декартово произведение вашего набора предков и набор всех user_ids.

SELECT a.[user_id],s.ancestry
FROM ancestor a, ancestry_set s
GROUP BY a.[user_id],s.ancestry

2) Найдите все записи в декартовом продукте, которые не имеют партнера в исходном наборе (Left Join) и

3) Извлеките user_ids из набора результатов из 2)

SELECT DISTINCT cp.[user_id]
FROM (SELECT a.[user_id],s.ancestry
      FROM ancestor a, ancestry_set s
      GROUP BY a.[user_id],s.ancestry) cp
   LEFT JOIN ancestor a ON cp.[user_id]=a.[user_id] AND cp.ancestry=a.ancestry
WHERE a.[user_id] is null

4) Верните все user_ids из исходного набора, которые не включены в набор результатов из 3)

SELECT DISTINCT [user_id]
FROM ancestor
WHERE [user_id] NOT IN (
   SELECT DISTINCT cp.[user_id]
   FROM (SELECT a.[user_id],s.ancestry
         FROM ancestor a, ancestry_set s
         GROUP BY a.[user_id],s.ancestry) cp
   LEFT JOIN ancestor a ON cp.[user_id]=a.[user_id] AND cp.ancestry=a.ancestry
   WHERE a.[user_id] is null
   )