Помощь в программировании SQL с использованием count и max ..etc

Как и в случае любого сложного SQL-запроса, секрет заключается в том, чтобы разбить его на части, желательно независимо тестируемые части.

Первая проблема состоит в том, чтобы установить подсчет количества билетов, выданных каждой машине за рассматриваемый период. Какой здесь критерий? Вероятно, если у машины возникла проблема, которая началась в ноябре 2011 г. и распространилась на декабрь 2011 г., ее следует учитывать; аналогично, если проблема была начата в январе 2012 г., но завершена в феврале 2012 г., это должно быть засчитано. Итак, нам нужно:

SELECT machine_name, COUNT(*) AS num_tickets
  FROM tickets
 WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
 GROUP BY machine_name;

Если вы решите, что учитываются только даты подачи заявок, измените критерий, чтобы дважды ссылаться на date_submitted; аналогичным образом, если учитываются только даты, когда билеты были заполнены, дважды укажите date_completed. Обратите внимание, что если у машины есть тикет, который был запущен в ноябре и не разрешен до февраля, приведенный выше запрос засчитает его; если вы используете любую из альтернатив, у этой машины не было проблем в течение рассматриваемого периода.

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

SELECT MAX(num_tickets) AS max_tickets
  FROM (SELECT machine_name, COUNT(*) AS num_tickets
          FROM tickets
         WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
         GROUP BY machine_name
       );

Теперь нам нужно выбрать имя(а) машины, на которой было такое количество билетов:

SELECT machine_name
  FROM (SELECT MAX(num_tickets) AS max_tickets
          FROM (SELECT machine_name, COUNT(*) AS num_tickets
                  FROM tickets
                 WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
                 GROUP BY machine_name
               )
        ) AS n
  JOIN (SELECT machine_name, COUNT(*) AS num_tickets
          FROM tickets
         WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
         GROUP BY machine_name
       ) AS m
    ON n.max_tickets = m.num_tickets;

Предполагая, что Oracle поддерживает предложение WITH, это можно упростить (значительно):

WITH t AS
    (SELECT machine_name, COUNT(*) AS num_tickets
       FROM tickets
      WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
      GROUP BY machine_name
    )
SELECT t.machine_name
  FROM t
  JOIN (SELECT MAX(num_tickets) AS max_tickets FROM t) AS m
    ON t.num_tickets = m.max_tickets;

Предупреждение: я использовал "псевдоним AS" в подзапросах, как это поддерживается стандартом SQL. Я считаю, что Oracle не разрешает «псевдоним AS» и требует только «псевдоним» после имен таблиц; Я не уверен, относится ли это также к именам для подзапросов. Если нотации «AS m» и «AS n» вызывают проблемы, попробуйте удалить AS. Вы можете найти аналогичную проблему с переименованием столбцов «AS num_tickets» и т. д., но я считаю, что Oracle разрешает AS в этом контексте.

Предположительно, это всего лишь один из серии вопросов, поскольку для ответа, похоже, не требуется ни одна из таблиц, кроме таблицы «Билеты». Предположительно, другие вопросы требуют использования других таблиц.

Вам нужно использовать group by.

select machine_name, count(*) as numMachines
from tickets
where date_submitted >= '01-DEC-2011' and 'date_submitted <= '31-JAN-2012'
group by machine_name
order by numMachines desc

Ваш запрос вернет вам одну строку для каждой проблемы. Ваш первый шаг — сгруппировать результат по машинам, чтобы получить по одной строке для каждой машины. Затем вы можете добавить столбец подсчета, который показывает, сколько проблем было для этой машины.

Чтобы найти максимальное количество проблем, вам нужно поместить свой запрос в подзапрос, чтобы вы могли извлечь максимум. Затем вы можете использовать это в качестве подвыборки в предложении have, чтобы вернуть машины с этим максимальным количеством.

SELECT machine_name, COUNT(machine_name) AS ticket_count
  FROM tickets
  WHERE date_submitted >= '01-DEC-2012' AND date_submitted <= '31-JAN-2012'
  GROUP BY machine_name
  HAVING ticket_count = (
    SELECT MAX(ticket_count) FROM (
      SELECT COUNT(machine_name) AS ticket_count
        FROM tickets
        WHERE date_submitted >= '01-DEC-2012' AND date_submitted <= '31-JAN-2012'
        GROUP BY machine_name
    )
  )