如何将行转换为列？

Question

如何将行转换为列？

4

我有一个数据库，其中存储了按项目分组的关键字及其相关数据，然后我为每个项目显示一个数据网格，每个关键字有一行，所有列均从同一个表“data”中检索。我有4个表：keywords，projects，group_keywords和data。“keywords”仅存储关键字，“projects”存储项目名称，“group_keywords”存储分配给该项目的关键字ID，而“data”则是每个关键字的所有数据所在的地方，并通过外键关联到keywords.id以及通过name列标识数据名称。

现在，为了检索一个项目中的关键字及其所有数据，我使用以下查询：

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;

- jarkam

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Stefan Mai · Accepted Answer

这个操作通常被称为“旋转”，一些关系型数据库管理系统支持它，但是MySQL似乎不支持。你有两个选择，用SQL或PHP来完成。在MySQL中，使用自连接可以实现此操作（我不知道哪个字段适合作为ID字段，所以请原谅我创建自己的示例）。从性能角度考虑，确保对ID和列名进行索引，否则这些连接将变得很慢。

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 版本，您不一定需要加载数组，可以流式传输。按 PK 排序并向下遍历，设置属性。伪代码如下：

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

希望这可以帮助您！