Нужно решение для вывода сложной матрицы запросов в mariadb

create table role (
  role varchar(20),
  id int
);

insert into role (role, id) values ('Friend', 1);
insert into role (role, id) values ('Son', 2);
insert into role (role, id) values ('Daughter', 3);
insert into role (role, id) values ('Father', 4);
insert into role (role, id) values ('Mother', 5);
insert into role (role, id) values ('Brother', 6);
insert into role (role, id) values ('Sister', 7);

create table person (
  persons varchar(20),
  personid int
);

insert into person (persons, personid) values ('James', 1);
insert into person (persons, personid) values ('Peter', 2);
insert into person (persons, personid) values ('Joseph', 3);
insert into person (persons, personid) values ('Jeni', 4);

create table role_person (
  roleid int,
  personid int
);

insert into role_person (roleid, personid) values (2, 1);
insert into role_person (roleid, personid) values (2, 2);
insert into role_person (roleid, personid) values (4, 2);
insert into role_person (roleid, personid) values (6, 2);
insert into role_person (roleid, personid) values (6, 2);
insert into role_person (roleid, personid) values (3, 4);
insert into role_person (roleid, personid) values (4, 3);

Я хочу, чтобы конечный результат был следующим
окончательным выходом

    люди друг сын дочь отец мать брат сестра
    Джеймс - Y - - - - -
    Питер - Y - Y - Y -
    Джозеф - - - Y - - -
    Jeni - - Y - - - -
    

Поскольку базовая база данных - это Мария БД, я не могу использовать функцию поворота. Обратите внимание, что люди могут увеличивать или уменьшать / также, роль может увеличиться завтра, как дед, жена и т. Д.

Не знаете, как решить эту проблему в хранимой процедуре или запросе.

Можем ли мы использовать XML DATA TYPE для обработки динамического запроса.

mysql,stored-procedures,mariadb,pivot-table,cross-join,

0

Ответов: 3


0

Как насчет:

select 
    p.persons,
    case when 1 in (select 1 from role_person rp where rp.personid = p.personid and rp.roleid = 1) then 'Y' else '-' end as friend,
    case when 1 in (select 1 from role_person rp where rp.personid = p.personid and rp.roleid = 2) then 'Y' else '-' end as son,
    case when 1 in (select 1 from role_person rp where rp.personid = p.personid and rp.roleid = 3) then 'Y' else '-' end as daughter,
    case when 1 in (select 1 from role_person rp where rp.personid = p.personid and rp.roleid = 4) then 'Y' else '-' end as father,
    case when 1 in (select 1 from role_person rp where rp.personid = p.personid and rp.roleid = 5) then 'Y' else '-' end as mother,
    case when 1 in (select 1 from role_person rp where rp.personid = p.personid and rp.roleid = 6) then 'Y' else '-' end as brother,
    case when 1 in (select 1 from role_person rp where rp.personid = p.personid and rp.roleid = 7) then 'Y' else '-' end as sister
  from person p;

Результат:

persons  friend  son  daughter  father  mother  brother  sister
James    -       Y    -         -       -       -        -
Peter    -       Y    -         Y       -       Y        -
Joseph   -       -    -         Y       -       -        -
Jeni     -       -    Y         -       -       -        -

0

Я думаю, что пока невозможно получить динамическое число столбцов в MariaDB.

Насколько я знаю, эта функциональность присутствует только в:

  • Oracle (как «модель»).
  • PostgreSQL (как «кросс-таблица»).
  • SQL Server (как «поворот»).

0

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

MySQL, хранимые-процедуры, MariaDB, поворотный стол, перекрестное соединение,