Как преобразовать отношение "многие ко многим" из таблицы с двумя столбцами?

Предположим, что у меня есть эта таблица базы данных (некоторый пример кода ниже), в которой сохраняется взаимосвязь между двумя списками (требования и тестовые окна в моем случае), и я хочу создать таблицу с строками, показывающими теги и столбцы, содержащие требования с индикатором, показывающим, что существует связь,

Несколько ограничений

  • У меня нет роскоши менять структуру db, так как это относится к системе управления тестовыми случаями с открытым исходным кодом (TestLink).
  • Для этого можно написать код, но я надеюсь, что это можно сделать в MySQL.
  • Ах, и да, он использует MySQL, поэтому это должно было работать в этой среде.
  • Эта функциональность существовала, но была выведена из-за того, что, как правило, этот тип работы приносит db на колени, когда есть десятки тысяч тестов и требований.

    create table pivot (req_id int (11), testcase_id int (11));

    /* Данные для таблицы pivot */

    вставить в pivot (req_id, testcase_id) значения (1,1);

    вставить в pivot (req_id, testcase_id) значения (2,2);

    вставить в pivot (req_id, testcase_id) значения (3,3);

    вставить в pivot (req_id, testcase_id) значения (4,1);

    вставить в pivot (req_id, testcase_id) значения (5,2);

    вставить в pivot (req_id, testcase_id) значения (6,3);

    вставить в pivot (req_id, testcase_id) значения (2,1);

    вставить в pivot (req_id, testcase_id) значения (3,2);

Я хочу выйти из запроса - это таблица, которая выглядит примерно так:

1 2 3 4 5 6
1 x x x
2 x x x
3 x x

Примечание. Строка - это теги testcase_ids, а столбцы - это 'req_ids'

У кого-нибудь есть подсказка о том, как получить это с помощью только SQL?

3 ответа

Теперь у меня есть имя для того, что я пытаюсь выполнить. Это "динамическая кросс-таблица". Вот как я добрался до решения. Спасибо за http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html за четкие инструкции для получения здесь.

Строки 1-20. Настройте таблицу для тестирования.

Строки 22-29 - "статический" кросс-столбец, предполагая, что я знаю, сколько требований у меня есть. Спасибо D Mac за решение, которое вы дали :)

Строки 30-44 - запрос, который динамически генерирует статический запрос выше.

Строки 45-72. Здесь у меня проблема. Цель состоит в создании хранимой процедуры, которая возвращает результат динамического запроса. MySQL говорит, что есть проблема синтаксиса, но я не вижу, как это исправить. Есть предположения?

drop table if exists pivot;

create table 'pivot' ( 
'req_id' int(11), 
'testcase_id' int(11) 
); 

/*Data for the table 'pivot' */ 

insert into 'pivot'('req_id','testcase_id') values (1,4); 
insert into 'pivot'('req_id','testcase_id') values (2,4); 
insert into 'pivot'('req_id','testcase_id') values (3,4); 
insert into 'pivot'('req_id','testcase_id') values (4,7); 
insert into 'pivot'('req_id','testcase_id') values (1,7); 
insert into 'pivot'('req_id','testcase_id') values (2,12); 
insert into 'pivot'('req_id','testcase_id') values (3,12); 
insert into 'pivot'('req_id','testcase_id') values (4,4); 

select * from pivot;

select testcase_id
, if(sum(req_id = 1), 1, 0)
, if(sum(req_id = 2), 1, 0)
, if(sum(req_id = 3), 1, 0)
, if(sum(req_id = 4), 1, 0)
from pivot
group by testcase_id;

select concat(
 'select testcase_id','\n'
 , group_concat(
 concat(
 ', if(sum(req_id = ',p2.req_id,'), 1, 0)','\n'
 )
 order by p2.req_id
 separator ''
 )
 , 'from pivot','\n'
 , 'group by testcase_id;','\n'
 ) statement
from pivot p2
order by p2.req_id;

CREATE PROCEDURE p_coverage()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
begin
 select concat(
 'select testcase_id','\n'
 , group_concat(
 concat(
 ', if(sum(req_id = ',p2.req_id,'), 1, 0)','\n'
 )
 order by p2.req_id
 separator ''
 )
 , 'from pivot','\n'
 , 'group by testcase_id;','\n'
 ) statement
 into @coverage_query
 from pivot p2
 order by p2.req_id;

 prepare coverage from @coverage_query;

 execute coverage;

 deallocate prepare coverage;
end;

select * from pivot;


select testcase_id, 
 if(sum(req_id = 1), 'X', '') as '1', 
 if(sum(req_id = 2), 'X', '') as '2', 
 if(sum(req_id = 3), 'X', '') as '3', 
 if(sum(req_id = 4), 'X', '') as '4', 
 if(sum(req_id = 5), 'X', '') as '5', 
 if(sum(req_id = 6), 'X', '') as '6'
from pivot
group by testcase_id;

Это уродливо, но оно работает:

+-------------+---+---+---+---+---+---+
| testcase_id | 1 | 2 | 3 | 4 | 5 | 6 |
+-------------+---+---+---+---+---+---+
| 1 | X | X | | X | | | 
| 2 | | X | X | | X | | 
| 3 | | | X | | | X | 
+-------------+---+---+---+---+---+---+
3 rows in set (0.00 sec)


ниже намного эффективнее:

создать одну таблицу для test_cases, например

create table testCases(
id int(11) auto_increment,
testcase varchar(200),
primary key(id))

одна таблица для требований

requirements(
id int(11) auto_increment,
requirements varchar(200),
primary key(id))

то в третьей карте таблицы соотношение

create table matchRequirementsToTests(
 requirements varchar(200),
 testcase varchar(200),
primary key(requirements, testcase),
foreign key (requirements) references Requirements(id),
foreign key(test case) references Test_cases(id))

licensed under cc by-sa 3.0 with attribution.