SQL-запрос — 2 запроса, включающих COUNT() и владельца определенной поездки

Мои базы данных выглядят так:

ПИК (ИМЯ, ПОДЪЕМ, РАЗНИЦА, КАРТА, РЕГИОН)
АЛЬПИНИСТ (ИМЯ, ПОЛ)
УЧАСТВОВАЛ (TRIP_ID, ИМЯ)
ПОДШЕЛ (TRIP_ID, PEAK, КОГДА)

  • PEAK предоставляет информацию о горных вершинах, которые интересуют пользователя. В таблице указано название каждой вершины, ее высота (в футах), уровень сложности (по шкале от 1 до 5), карта, на которой она расположена. и регион Сьерра-Невада, в котором он расположен.
  • АЛЬПИНИСТ перечисляет членов клуба, называет их имена и пол.
  • УЧАСТИЕ дает набор альпинистов, которые участвовали в каждом из различных восхождений. Количество участников в каждой поездке разное.
  • CLIMBED показывает, какие вершины были покорены в каждом восхождении, а также данные о том, что каждая вершина была покорена.

Мне нужна помощь с написанием SQL-запроса для этих двух примеров сценариев:

  • На какие вершины покорились Марк и Мэри?
  • В каких поездках общая высота, набранная всеми участниками, превышала 500 000 футов?

Это то, что у меня есть для первого запроса:

SELECT PEAK
FROM CLIMBED
WHERE TRIP_ID IN
    (SELECT TRIP_ID
     FROM PARTICIPATED
     WHERE NAME IN ('MARK','MARY')
     GROUP BY TRIP_ID
     HAVING COUNT(*) = 2
    );

Проблема с этим запросом в том, что он дает мне только все вершины, на которые Марк и Мэри поднялись во время одного и того же путешествия, которое они совершили вместе. Мне нужно как-то получить вершины, на которые они оба взобрались, но ради которых не были вместе.

Для второго запроса я понятия не имею, как получить COUNT() каждой вершины, на которую поднялись все участники во время определенного TRIP_ID.


sql count oracle-xe
person Scott    schedule 04.04.2016    source источник
comment
Для первого запроса я хотел быть конкретным, сказав, что мне нужно иметь возможность захватить все пики, на которые покорились Марк и Мэри, за исключением тех, на которые либо Марк поднялся, а Мэри нет, либо Мэри поднялась, а Марк нет. .   -  person Scott    schedule 05.04.2016

Ответы (2)


arrow_upward
1
arrow_downward

Что касается вашего первого вопроса, вы должны просто удалить предложение «Иметь», чтобы получить восхождения, в которых участвовали Марк или Мэри.

SELECT PEAK
FROM CLIMBED
WHERE TRIP_ID IN
    (SELECT TRIP_ID
     FROM PARTICIPATED
     WHERE NAME IN ('MARK','MARY')
     GROUP BY TRIP_ID
    );

Если вы оставите здесь предложение Have, это означает, что вам нужно, чтобы и Марк, и Мэри были в таблице Участие для конкретной поездки, чтобы trip_id дал вам 2 строки, указанные в предложении Наличие.

Чтобы получить вершины, на которые забрался один, но не другой, используйте исходный запрос, но измените условие наличия на 1:

SELECT PEAK
FROM CLIMBED
WHERE TRIP_ID IN
    (SELECT TRIP_ID
     FROM PARTICIPATED
     WHERE NAME IN ('MARK','MARY')
     GROUP BY TRIP_ID
     HAVING COUNT(*) = 1
    );

Это работает, потому что с учетом условия where Count(*) будет:

  • 0, если ни один из них не поднялся (гипотетически - условие where не позволит показать эту строку)
  • 1 если один из них залез
  • 2 если они оба поднялись

Наличие предложений ограничивает запросы на основе условий после группировки, поэтому обычно они основаны на агрегатах, таких как Count (*), которые дают вам количество записей, «содержащихся» в каждой группе.

Второй вопрос немного сложнее, но если я правильно его понимаю, вы сможете использовать что-то вроде этого:

SELECT climbed.trip_id, sum(peak.elev) 
FROM climbed LEFT JOIN participated ON climbed.trip_id = participated.trip_id
LEFT JOIN peak ON climbed.peak = peak.name
GROUP BY climbed.trip_id
HAVING sum(peak.elev) > 500000;

Это работает, потому что при использовании левого соединения высота каждого альпиниста дублируется; затем, когда вы суммируете для каждой поездки, она добавляет высоту для каждого альпиниста.

person Kateract    schedule 04.04.2016
comment
Первый запрос дает мне все пики, на которые поднялись Марк или Мэри. Но есть ли способ показать только пики, на которые покорились Марк и Мэри, включая похожие пики, на которые они поднялись во время разных поездок? - person Scott; 05.04.2016
comment
Однако второй запрос работает точно так, как ожидалось. Благодарю вас! - person Scott; 05.04.2016
comment
@Scott Дайте мне знать, если исправления, которые я сделал, сработали для вас. - person Kateract; 05.04.2016
comment
Да, это имеет смысл, я как бы искал что-то с немного другой настройкой, но вы лучше работаете для того, чего я пытаюсь достичь. - person Scott; 05.04.2016
comment
Вы пытаетесь получить исключающее или, у которого на самом деле нет ключевого слова в SQL. - person Kateract; 05.04.2016

arrow_upward
1
arrow_downward

На какие вершины покорились Марк и Мэри?

В разных поездках:

SELECT c.peak
FROM climbed c
JOIN participated p
   ON c.trip_id = p.trip_id
WHERE p.name IN('mark','mary')

В каких поездках общая высота, набранная всеми участниками, превышала 500 000 футов?

SELECT c.trip_id, SUM(pe.elev)
FROM climbed c
JOIN peak pe
   ON c.peak = pe.name
JOIN participated pa
   ON c.trip_id = pa.trip_id
GROUP BY c.trip_id
HAVING SUM(pe.elev) > 500000
person supergrady    schedule 04.04.2016