Помощ за 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 alias“ на подзаявките, както се поддържа от стандарта SQL. Вярвам, че Oracle не позволява „AS alias“ и изисква само „alias“ след имената на таблиците; Не съм сигурен дали това се отнася и за имената за подзаявки. Ако нотациите '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

Вашата заявка ще ви върне един ред за всеки проблем. Първата ви стъпка е да групирате резултата по машина, така че да получите един ред за всяка машина. След това можете да добавите колона за преброяване, която ви показва колко проблеми е имало за тази машина.

За да намерите максималния брой проблеми, трябва да поставите заявката си в подизбор, така че да можете да извлечете максимума. След това можете да използвате това като подизбор в клауза за връщане на машините, които имат този максимален брой.

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
    )
  )