Как превратить строки в столбцы?
У меня есть база данных, в которой я храню ключевые слова, сгруппированные в проекты, и данные, относящиеся к каждому ключевому слову, затем я отображаю сетки данных для каждого проекта с одной строкой на ключевое слово и несколькими столбцами, полученными из одной и той же таблицы "данные". У меня есть 4 таблицы, ключевые слова, проекты, групповые слова и данные. в "ключевых словах" хранится только ключевое слово, "проекты" - название проекта, "group_keywords" - идентификаторы ключевых слов для ключевых слов, назначенных этому проекту, а "данные" - это то, куда идут все данные для каждого ключевого слова, идентифицируется внешним ключом для keywords.id, и столбец имени для идентификации имени данных.
Теперь, чтобы получить ключевые слова + все данные для проекта, я использую этот запрос:
SELECT * FROM `group_keywords`
INNER JOIN keywords on keywords.id = keyword_id
INNER JOIN data ON data.id = keywords.id
WHERE `group_id` = (SELECT `id` FROM `projects` WHERE `name` = 'ProjectName'
Это дает мне что-то вроде
id group_id keyword_id id keyword id name value
12 5 52 52 absorption food 52 data_name_x1 6
12 5 52 52 absorption food 52 data_name_x2 8
12 5 52 52 absorption food 52 data_name_x3 26
12 5 52 52 absorption food 52 data_name_x4 2
...
Но то, что я хочу, это получить:
id group_id keyword_id id keyword id data_name_x1 data_name_x2 data_name_x3 data_name_x4
12 5 52 52 absorption food 52 6 8 26 2
...
Таким образом, я могу легко сортировать и использовать разбивку на страницы для сеток данных, иначе я понятия не имею, как это сделать, потому что при использовании больших наборов данных я не могу просто сбросить все в массив, слишком много данных.
Это схема:
-- --------------------------------------------------------
-- Table structure for table `keywords`
CREATE TABLE IF NOT EXISTS `keywords` (
`id` int(10) unsigned NOT NULL auto_increment,
`keyword` varchar(255) NOT NULL,
UNIQUE KEY `id` (`id`),
UNIQUE KEY `keyword` (`keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=115386 ;
-- --------------------------------------------------------
-- Table structure for table `data`
CREATE TABLE IF NOT EXISTS `data` (
`id` int(10) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`value` varchar(15) NOT NULL,
UNIQUE KEY `id` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
-- Table structure for table `projects`
--
CREATE TABLE IF NOT EXISTS `projects` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`parent` varchar(100) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
-- --------------------------------------------------------
-- Table structure for table `group_keywords`
CREATE TABLE IF NOT EXISTS `group_keywords` (
`id` int(10) NOT NULL auto_increment,
`group_id` int(10) NOT NULL,
`keyword_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `group_id` (`group_id`,`keyword_id`),
KEY `keyword_id` (`keyword_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=119503 ;
-- --------------------------------------------------------
-- Constraints for table `data`
--
ALTER TABLE `data`
ADD CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- --------------------------------------------------------
-- Constraints for table `group_keywords`
--
ALTER TABLE `group_keywords`
ADD CONSTRAINT `group_keywords_ibfk_1` FOREIGN KEY (`keyword_id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
1 answers
Эта операция традиционно называется "unpivot", и ее поддерживают несколько СУБД, но MySQL, похоже, не входит в их число. У вас есть два варианта: сделать это в SQL или сделать это в PHP. В MySQL это выглядит примерно так с самосоединениями (я не знаю, какое поле квалифицируется как поле идентификатора для вас, поэтому простите, что я создаю свой собственный пример). С точки зрения производительности убедитесь, что вы индексируете как идентификатор, так и имя столбца, иначе эти соединения будут сканироваться.
shapes
ID Name Value
1 Color Red
1 Shape Circle
... for more "columns"
2 Color Green
2 Shape Square
... for more "columns"
SELECT
A.ID,
B.Value as Color,
C.Value as Shape
... for more "columns"
FROM shapes A
LEFT JOIN shapes B ON B.ID = A.ID AND B.Name = 'Color'
LEFT JOIN shapes C ON C.ID = A.ID AND C.Name = 'Shape'
... for more "columns"
Что должно нас зацепить (если только мой главный SQL-анализатор не ошибается сегодня вечером):
ID Color Shape
1 Red Circle
2 Green Square
Для версии PHP вам не обязательно загружать массив, вы можете передавать его в потоковом режиме. Сортируйте по ПК и пройдите по нему, задавая свойства. В псевдокоде:
Set X to undefined
Get a Record
Check the ID property, if it's different than X, create a new object, set X to the new ID, and yield the previous object
Set the property of the object based on the "Name" column of our result
Надеюсь, это поможет!