Написание оператора SQL, который ссылается на вложенный VARRAY

Question

Я пытаюсь написать оператор SQL, который запрашивает столбец actors (определяется как VARRAY(5)) в таблице с именем movie без использования PL/SQL, чтобы ответить на вопрос:

Список всех актеров, которые появляются в нескольких фильмах, с указанием названия фильма и актера

select a.column_value, count(*), m.title
from movie m, table (m.actors) a
where count(*) > 2,
group by a.column_value;

Answer 1

Вы должны использовать HAVING пункт для проверки условия вместо пункта где и дополнительно LISTAGG может использоваться для отображения названий фильмов.

CREATE OR REPLACE TYPE actortype IS VARRAY(5) OF VARCHAR2(20);
/

create table movie ( id integer , title VARCHAR2(100), actors actortype );

INSERT INTO movie (
     id,
     title,
     actors
) VALUES (
     1,
     'The Shawshank Redemption',
     actortype('Morgan Freeman','Tim Robbins','Bob Gunton')
);
INSERT INTO movie (
     id,
     title,
     actors
) VALUES (
     1,
     'The Dark Knight',
     actortype('Morgan Freeman','Christian Bale','Heath Ledger')
);

запрос

SELECT a.column_value as Actor,
       COUNT(*) as num_of_movies,
       LISTAGG( m.title, ',') WITHIN GROUP ( ORDER BY id ) as movies
FROM movie m,
     TABLE ( m.actors ) a
GROUP BY a.column_value
HAVING COUNT(*) > 1;

ACTOR          NUM_OF_MOVIES MOVIES
------------  -------------  -------
Morgan Freeman  2            The Dark Knight,The Shawshank Redemption