SQL - количество раз, когда внешний ключ появляется в таблице

#sql #oracle #oracle11g

#sql #Oracle #oracle11g

Вопрос:

Информация о схеме: СХЕМА

речь идет о 3 таблицах: SIGHTING, SPOTTER И ORG

ПОИСК ссылается на SPOTTER через FK SpotterId.

Средство ОБНАРУЖЕНИЯ ссылается на ORG через FK OrgID.

Я хотел бы, чтобы запрос возвращал два столбца; один для списка ОРГАНИЗАЦИЙ.OrgName и еще один для соответствующего общего количества появлений Spotter_ID в сообщениях о НАБЛЮДЕНИЯХ для соответствующей ОРГАНИЗАЦИИ.OrgID.

То, что я сделал ниже, возвращает неправильные значения для каждой возвращаемой строки:

 SELECT ORG.ORG_NAME AS ORG_NAME,
       (SELECT COUNT(SIGHTINGS.SPOTTER_ID) 
          FROM SIGHTINGS
             , SPOTTERS 
         WHERE SIGHTINGS.SPOTTER_ID = SPOTTERS.SPOTTER_ID 
          AND SPOTTERS.ORG_ID=ORG.ORG_ID) AS ORG_COUNT 
  FROM ORG;

1. Пожалуйста, избегайте публикации изображений; структуру вашей таблицы можно было бы лучше объяснить, опубликовав инструкции DDL. Также, пожалуйста, опубликуйте некоторые примеры данных и желаемый результат

Ответ №1:

Кажется, что вам нужна только агрегация:

   SELECT COUNT(1), orgName
    FROM SIGHTING
         INNER JOIN SPOTTER USING (spotterId)
         INNER JOIN ORG USING (orgId)
GROUP BY orgName

1. Почему count(1) ? Count(*) было бы быстрее! (найдите «Окончательно убедительное доказательство», если эта ссылка приведет вас к началу страницы)

2. Это работает отлично. Это также намного чище, чем мой опубликованный ответ. Спасибо.

3. @Boneist — Возможно, я ошибаюсь, но разве оптимизатор автоматически — и сверхбыстро — не преобразует count(1) в count(*) , по крайней мере, в более новых версиях Oracle? Возможно, это было не так 15 лет назад, когда была написана эта статья. По-прежнему нет причин писать count(1) , но я слышал этот аргумент раньше, и я не нахожу его очень убедительным (возможно, больше).

4. @mathguy это был ироничный комментарий — вы смотрели на ссылку, которую я разместил? К сожалению, я случайно забыл добавить смайлик к своему предыдущему комментарию

5. @Boneist — как сказал Том: «ROTFL 🙂 спасибо (сначала я читал это серьезно …. ) «.. Я в хорошей компании 🙂

Ответ №2:

Это простая агрегация, но вам нужен только один JOIN :

 select o.orgname, count(*) as numSpotters
from org o join
     spotters s
     on o.orgId = s.orgId
group by o.orgname;

Примечание: Если вам нужны все организации, даже те, у которых нет корректировщиков, тогда используйте left join вместо join .

1. Требование состояло в том, чтобы вести подсчет по таблице НАБЛЮДЕНИЙ (то есть считать наблюдения наблюдателей, принадлежащих организации), а не считать самих наблюдателей. Вот почему необходимы два объединения.

2. @mathguy . . . На самом деле, вопрос неоднозначен: «другой для соответствующего общего количества Spotter_ID». Я мог бы считать это отличным количеством.

3. @user3603179 — пожалуйста, прочтите два комментария к ответу Гордона (прямо над этим) — вам нужно уточнить назначение. Гордон прав, то, как вы это сформулировали, можно интерпретировать в любом случае. Если средство обнаружения появляется четыре раза в таблице наблюдений, считается ли это как «один» или как «четыре» в вашем требовании?

4. @mathguy Извините за неясность, но из вашего примера, корректировщик, появляющийся 4 раза, добавит 4 к количеству.