Oracle SQL Listagg удаляет дубликаты с условиями оператора case

Я пытаюсь показать повторяющиеся значения столбцов со списком, разделенным запятыми, используя listagg, но получаю сообщение об ошибке «Не одна группа по функции». Надеюсь, мне помогут.

Ниже приведен сценарий DDL с инструкциями вставки и данными:

DROP TABLE dept CASCADE CONSTRAINTS;

DROP TABLE myrole CASCADE CONSTRAINTS;

DROP TABLE person CASCADE CONSTRAINTS;

DROP TABLE person_role CASCADE CONSTRAINTS;
   
CREATE TABLE dept (
    id    INTEGER NOT NULL,
    dept  VARCHAR2(50 CHAR)
);

INSERT INTO dept (
    id,
    dept
) VALUES (
    1,
    'Operations'
);

INSERT INTO dept (
    id,
    dept
) VALUES (
    2,
    'Research'
);

INSERT INTO dept (
    id,
    dept
) VALUES (
    3,
    'Accounts'
);

INSERT INTO dept (
    id,
    dept
) VALUES (
    4,
    'Sales'
);

ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY ( id );

CREATE TABLE myrole (
    id    INTEGER NOT NULL,
    role  VARCHAR2(50 CHAR)
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    1,
    'JJJ'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    2,
    'Auth'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    3,
    'AAA'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    4,
    'MMM'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    5,
    'KKK'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    6,
    'BBB'
);

ALTER TABLE myrole ADD CONSTRAINT myrole_pk PRIMARY KEY ( id );

CREATE TABLE person (
    id      INTEGER NOT NULL,
    person  VARCHAR2(50 CHAR)
);

INSERT INTO person (
    id,
    person
) VALUES (
    1,
    'John'
);

INSERT INTO person (
    id,
    person
) VALUES (
    2,
    'Scott'
);

INSERT INTO person (
    id,
    person
) VALUES (
    3,
    'Ruth'
);

INSERT INTO person (
    id,
    person
) VALUES (
    4,
    'Smith'
);

INSERT INTO person (
    id,
    person
) VALUES (
    5,
    'Frank'
);

INSERT INTO person (
    id,
    person
) VALUES (
    6,
    'Martin'
);

INSERT INTO person (
    id,
    person
) VALUES (
    7,
    'Blake'
);

ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY ( id );

CREATE TABLE person_role (
    id         INTEGER NOT NULL,
    person_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    dept_id    INTEGER
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    1,
    1,
    1,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    2,
    2,
    2,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    3,
    2,
    4,
    1
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    4,
    2,
    4,
    2
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    5,
    3,
    1,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    6,
    3,
    5,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    7,
    4,
    3,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    8,
    5,
    6,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    9,
    6,
    6,
    3
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    10,
    6,
    6,
    2
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    11,
    6,
    2,
    NULL
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    12,
    7,
    6,
    4
);

INSERT INTO person_role (
    id,
    person_id,
    role_id,
    dept_id
) VALUES (
    13,
    7,
    6,
    4
);

ALTER TABLE person_role ADD CONSTRAINT person_role_pk PRIMARY KEY ( id );

ALTER TABLE person_role
    ADD CONSTRAINT person_role_myrole_fk FOREIGN KEY ( myrole_id )
        REFERENCES myrole ( id );

ALTER TABLE person_role
    ADD CONSTRAINT person_role_person_fk FOREIGN KEY ( person_id )
        REFERENCES person ( id );

CREATE SEQUENCE dept_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER dept_tr BEFORE
    INSERT ON dept
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := dept_seq.nextval;
END;
/

CREATE SEQUENCE myrole_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER myrole_tr BEFORE
    INSERT ON myrole
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := myrole_seq.nextval;
END;
/

CREATE SEQUENCE person_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER person_tr BEFORE
    INSERT ON person
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := person_seq.nextval;
END;
/

CREATE SEQUENCE person_role_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER person_role_tr BEFORE
    INSERT ON person_role
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := person_role_seq.nextval;
END;
/

Используя приведенный ниже запрос, предоставленный @Koen Lostrie, и добавив нужные мне столбцы, я получаю результат, как показано на рисунке:

SELECT p.person, r.role as myrole, d.dept,
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as myaccess
  FROM person_role pr 
  JOIN person p ON p.id = pr.person_id 
  JOIN myrole r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_role pr 
    JOIN person p ON p.id = pr.person_id 
    JOIN myrole r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id
    left join dept d on d.id = pr.dept_id

Вывод из запроса:

+--------+--------+------------+----------+
| PERSON | MYROLE |    DEPT    | MYACCESS |
+--------+--------+------------+----------+
| John   | JJJ    |            |          |
| Scott  | Auth   |            | Remove   |
| Scott  | MMM    | Operations |          |
| Scott  | MMM    | Research   |          |
| Ruth   | JJJ    |            |          |
| Ruth   | KKK    |            |          |
| Smith  | AAA    |            | Add      |
| Frank  | BBB    |            | Add      |
| Martin | AAA    | Accounts   |          |
| Martin | AAA    | Research   |          |
| Martin | Auth   |            | Remove   |
| Blake  | BBB    | Sales      |          |
| Blake  | BBB    | Sales      | Add      |
+--------+--------+------------+----------+

Теперь я хочу показать значения столбцов DEPT, разделенные запятыми, на основе столбцов PERSON и MYROLE, и ожидаемый результат показан ниже:

+--------+--------+---------------------+----------+
| PERSON | MYROLE |        DEPT         | MYACCESS |
+--------+--------+---------------------+----------+
| John   | JJJ    |                     |          |
| Scott  | Auth   |                     | Remove   |
| Scott  | MMM    | Operations,Research |          |
| Ruth   | JJJ    |                     |          |
| Ruth   | KKK    |                     |          |
| Smith  | AAA    |                     | Add      |
| Frank  | BBB    |                     | Add      |
| Martin | AAA    | Accounts,Research   |          |
| Martin | Auth   |                     | Remove   |
| Blake  | BBB    | Sales               | Add      |
+--------+--------+---------------------+----------+

Я добавил список в существующий запрос, но получаю ошибку

SELECT p.person, r.role as myrole,  
listagg(d.dept, ', ') within group (order by d.dept) as dept,
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as myaccess
  FROM person_role pr 
  JOIN person p ON p.id = pr.person_id 
  JOIN myrole r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_role pr 
    JOIN person p ON p.id = pr.person_id 
    JOIN myrole r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id
    left join dept d on d.id = pr.dept_id

не получив ни одной группы по ошибке. Не знаю, как исправить. Цените любую помощь.

Спасибо,
Риша

group-by listagg case-statement

Richa 14.05.2021 источник

comment

В сценариях ddl/dml нет данных для таблицы dept и данных для таблицы person_roles. Эта структура данных выглядит немного странно... person_roles - это таблица пересечений. Он имеет запись для каждой комбинации пользователя/роли. Обычно у человека будет отдел, а не человек/роль. - Koen Lostrie 14.05.2021

comment

@KoenLostrie мой плохой. Я был так утомлен этим запросом listagg, что забыл предоставить операторы вставки и данные. Прости за это. Я обновил сценарий DDL. - Richa 14.05.2021

comment

Таблица dept, похоже, имеет значения, необходимые в person_role. Отделов нет... - Koen Lostrie 14.05.2021

comment

@KoenLostrie Я вижу, что в моих сценариях ddl была опечатка, и я обновил ее в этом же посте. Прости за это - Richa 15.05.2021

Ответы (1)

arrow_upward
1
arrow_downward

LISTAGG является агрегатной функцией. Если вы применяете его к столбцу, то вам нужно указать в запросе, по каким столбцам вы группируете. Обычно это все столбцы, у которых нет агрегатной функции. Я не проверял, так как нет образцов данных для таблицы dept или таблицы person_roles, но, вероятно, проблема в этом.

SELECT p.person, r.role as myrole, listagg(d.dept, ', ') within group (order by d.dept) as dept_list,
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as myaccess
  FROM person_role pr 
  JOIN person p ON p.id = pr.person_id 
  JOIN myrole r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_role pr 
    JOIN person p ON p.id = pr.person_id 
    JOIN myrole r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id 
left join dept d on d.id = pr.dept_id
GROUP BY
  p.person, 
  r.role, 
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END
ORDER BY p.person

Koen Lostrie 14.05.2021

comment

Сейчас я включил образцы данных в скрипт DDL. Надеюсь, вы можете проверить это сейчас. Еще раз спасибо за вашу помощь. я глубоко признателен - Richa; 14.05.2021

comment

Мой ответ сработал? Если нет, вы получили ошибку? - Koen Lostrie; 14.05.2021

comment

Коэн Лостри, ты гений. Ваш запрос сработал. Единственная проблема заключается в том, что для записей с одинаковым именем отдела отображаются повторяющиеся значения. Для BLAKE это показывает Продажи, Продажи. - Richa; 15.05.2021

comment

Я поместил этот отчет на apex.oracle.com, подробности ниже: Внешний интерфейс apex.oracle.com/pls/apex/richaferna/r/person-role1/ имя пользователя: demo пароль: demo1234 Backend (то же имя пользователя и пароль) apex.oracle .com/pls/apex/, - Richa; 15.05.2021

comment

Рабочее пространство: рихаферна - Richa; 15.05.2021

comment

Отличная идея ! Обновил мой ответ. - Koen Lostrie; 15.05.2021

comment

Коэн Лостри, ты лучший. Спасибо за помощь и время - Richa; 15.05.2021

Oracle SQL Listagg удаляет дубликаты с условиями оператора case

Ответы (1)

Похожие вопросы