Запрос Oracle для фильтрации и сортировки таблицы на основе значений в дочерней таблице
Я использую Oracle 11g, и у меня есть таблицы с данными и структурой следующим образом:
TABLE1_PARENT:
-------------------
PID | Name | Age |
-------------------
1 | Mark | 35 |
2 | Jane | 40 |
3 | Agatha | 45 |
-------------------
TABLE2_CHILD
==============================================
CID | Name | Age | Class | House | PID |
----------------------------------------------
1 | John | 7 | 3 | Red | 1 |
2 | Marie | 5 | 1 | Yellow| 2 |
3 | Petra | 6 | 2 | Green | 3 |
4 | Taylor | 8 | 4 | Blue | 2 |
5 | Lean | 9 | 5 | Red | 2 |
6 | Justin | 7 | 3 | Yellow| 3 |
7 | Arianna | 5 | 1 | Blue | 3 |
8 | Brendon | 6 | 2 | Green | 3 |
9 | Shawn | 7 | 3 | Red | 1 |
----------------------------------------------
Для одного условия запрос прост:
SELECT * FROM TABLE1_PARENT WHERE PID IN (SELECT PID FROM TABLE2_CHILD WHERE AGE=7);
что приведет к следующему результату:
TABLE1_PARENT:
-------------------
PID | Name | Age |
-------------------
1 | Mark | 35 |
3 | Agatha | 45 |
-------------------
Однако, если я хочу получить список родителей, чьи дети имеют возраст = 7 и принадлежат дому = «ЗЕЛЕНЫЙ», если я напишу запрос, как показано ниже:
SELECT * FROM TABLE1_PARENT WHERE PID IN (SELECT PID FROM TABLE2_CHILD WHERE AGE=7 AND house='GREEN');
Я бы не получил никаких результатов.
Я ожидаю, что результат будет:
TABLE1_PARENT:
-------------------
PID | Name | Age |
-------------------
3 | Agatha | 45 |
-------------------
так как у Агаты есть ребенок, принадлежащий возрасту = 7, и ребенок, принадлежащий дому = «ЗЕЛЕНЫЙ».
Мне удалось найти решение для аналогичной структуры данных с использованием потоков Java. Я пытаюсь сделать то же самое, используя Oracle SQL.
List<Parent> filteredParents = parents.stream()
.filter(parent -> parent.getChildren().stream()
.anyMatch(child -> child.getAge().equals("7")) && parent.getChildren().stream()
.anyMatch(child -> child.getHouse().equalsIgnoreCase("Green")))
.collect(Collectors.toList());
Я ожидаю, что запрос даст мне результат, в котором условия могут соответствовать любому из дочерних элементов. Потому что фильтрация происходит на родительском уровне.
Любая помощь будет здорово. Спасибо!
@Level_Up Спасибо, что указали на это. Я исправил это.
Ответы 3
Вы можете сделать это с помощью EXISTS:
SELECT t.*
FROM TABLE1_PARENT t
WHERE t.PID IN (
SELECT PID FROM TABLE2_CHILD WHERE AGE=7
) AND EXISTS (
SELECT 1 FROM TABLE2_CHILD WHERE PID = t.PID AND House = 'Green'
)
Проблема с вашим запросом в том, что он пытается применить оба условия в одной и той же строке внутри TABLE2_CHILD. Но это не то, что вы хотите.
Вам нужны родительские идентификаторы, для которых дочерние строки имеют AGE = 7, а внутри есть строка TABLE2_CHILD с House = 'Green'.
Вы можете использовать EXISTS для обоих условий, что может быть более эффективным:
SELECT t.*
FROM TABLE1_PARENT t
WHERE EXISTS (
SELECT 1 FROM TABLE2_CHILD WHERE PID = t.PID AND AGE=7
) AND EXISTS (
SELECT 1 FROM TABLE2_CHILD WHERE PID = t.PID AND House = 'Green'
)
или с условной агрегацией путем группировки дочерней таблицы по PID:
SELECT * FROM TABLE1_PARENT
WHERE PID IN (
SELECT PID
FROM TABLE2_CHILD
GROUP BY PID
HAVING
SUM(CASE WHEN Age = 7 THEN 1 ELSE 0 END) > 0
AND
SUM(CASE WHEN House = 'Green' THEN 1 ELSE 0 END) > 0
)
Если я применю эту логику к большому набору данных, скажем, к таблицам с миллионами записей. Не вызовет ли это проблемы с производительностью?
Если вы хотите получить родителя, у которого есть ребенок с возрастом = 7, и тот же ребенок принадлежит дом = ЗЕЛЕНЫЙ, используйте уже написанный вами запрос:
SELECT * FROM TABLE1_PARENT WHERE PID IN (SELECT PID FROM TABLE2_CHILD IF AGE=7 AND house='GREEN');
Если вы хотите получить родителя, чьи дети имеют возраст = 7 или дом = ЗЕЛЕНЫЙ или оба, используйте
SELECT * FROM TABLE1_PARENT WHERE PID IN (SELECT PID FROM TABLE2_CHILD IF AGE=7 OR house='GREEN');
Этот запрос вернет и Марка, и Агату. Я пытаюсь фильтровать на родительском уровне. Таким образом, у родителей должен быть ребенок, возраст которого = 7, и ребенок, чей дом = «Зеленый». Это не обязательно должен быть один и тот же ребенок. Но у родителя должен быть ребенок, отвечающий обоим критериям.
Вы не получаете вывод, потому что в вашей TABLE2_CHILD нет ребенка в возрасте 7 лет в теплице. Для ожидаемого результата запрос должен быть:
SELECT * FROM TABLE1_PARENT WHERE PID IN (SELECT PID FROM TABLE2_CHILD WHERE AGE=7) AND PID IN (SELECT PID FROM TABLE2_CHILD WHERE house='GREEN');
Это сработает. Но я пытаюсь использовать это на очень большом наборе данных. Не повлияет ли это на производительность?
На производительность это не повлияет, так как это самый простой способ получить ожидаемый результат.
SELECT * FROM TABLE1_PARENT WHERE PID IN (SELECT PID FROM TABLE2_CHILD IF AGE=7 AND house='GREEN');внутри подзапроса, который вы используетеIF, и я думаю, что вы должны использоватьWHERE