Запрос oracle ничего не выводит

Question

Запрос ничего не выводит

по сути он должен вывести

ID_Film   Tickets_Purchased 

3          8

5          8

4          3

исключая значение 14, потому что это максимальное

WITH SEC AS (SELECT S.ID_Film, COUNT(T.ID_Ticket) Tickets_Purchased
    FROM Ticket T INNER JOIN SCHEDULE S  
    ON T.ID_SCHEDULE = S.ID_SCHEDULE 
    GROUP BY S.ID_Film) 
SELECT S.ID_Film, SEC.Tickets_Purchased, S.Dates
    FROM SCHEDULE S INNER JOIN SEC
    ON SEC.ID_Film = S.ID_Film  
    GROUP BY S.ID_Film, S.Dates, SEC.Tickets_Purchased
    HAVING SEC.Tickets_Purchased <> MAX(Tickets_Purchased) 
    ORDER BY SEC.Tickets_Purchased DESC;

если поставить в строке

HAVING SEC.Tickets_Purchased <> MAX(Tickets_Purchased)

знак '=' то выводится

ID_Film   Tickets_Purchased 

1          14

2          14

3          8

5          8

4          3

Answer 1

Выражение:

GROUP BY [...], SEC.Tickets_Purchased
HAVING SEC.Tickets_Purchased <> MAX(Tickets_Purchased)

не имет никакого смысла, потому что в группе будет только одно значение этого столбца, которое всегда равно и MAX и MIN.

HAVING тут не подходит. На основании данных из вопроса, два наиболее простых варианта:

create table films as 
with q (ID_Film,  Tickets_Purchased) as (  
    select 1, 14 from dual union all
    select 2, 14 from dual union all
    select 3, 8  from dual union all
    select 5, 8  from dual union all
    select 4, 3  from dual) select * from q;

select ID_Film, Tickets_Purchased 
from films 
where Tickets_Purchased < (select MAX (Tickets_Purchased) from films) 
order by Tickets_Purchased desc;

select ID_Film, Tickets_Purchased from (
    select ID_Film, Tickets_Purchased, max (Tickets_Purchased) over () maxPurchased  
    from films)
where Tickets_Purchased < maxPurchased 
order by Tickets_Purchased desc;

Оба дадут желаемый результат:

   ID_FILM TICKETS_PURCHASED
---------- -----------------
         3                 8
         5                 8
         4                 3

БЛОГ НА HUSL

Запрос oracle ничего не выводит

Ответы (1 шт):