如何将行转成列？ [英] How to turn rows into columns?
问题描述
现在检索关键字+项目的所有数据，我使用这个查询：
SELECT * FROM`group_keywords`
INNER JOIN关键字keywords.id = keyword_id
INNER JOIN data ON data.id = keyword.id
WHERE`group_id` =（SELECT`id` FROM`projects` WHERE`name` ='ProjectName'
这给我一些像
id group_id keyword_id id关键字id名称值
12 5 52 52吸收食物52 data_name_x1 6
12 5 52 52吸收食物52 data_name_x2 8
12 5 52 52吸收食物52 data_name_x3 26
12 5 52 52吸收食物52 data_name_x4 2
...
但是我想要的是获取：
id group_id keyword_id id关键字id data_name_x1 data_name_x2 data_name_x3 data_name_x4
12 5 52 52吸收食物52 6 8 26 2
...
所以我可以轻松地对datagrids进行排序和使用分页，否则我不知道该怎么做，因为当使用大数据集时，我不能将所有数据转储成数组，数据太多。
这是模式：
- ------------------------ --------------------------------
- 表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`````````````````` EFAULT CHARSET = latin1 AUTO_INCREMENT = 115386;
- ------------------------------------ --------------------
- 表'data'的表结构
CREATE TABLE如果不存在`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;
- ------------------------------------ --------------------
- 表`projects`的表结构
-
CREATE TABLE IF NOT EXISTS`project`（
`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;
- ------------------------------------ --------------------
- 表'group_keywords`的表结构
CREATE TABLE如果不存在`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;
- ------------------------------------ --------------------
- 表'data'的限制
-
ALTER TABLE`data`
ADD CONSTRAINT`data_ibfk_1` FOREIGN KEY（`id`）REFERENCES`keywords`（`id`）ON DELETE CASCADE ON UPDATE CASCADE;
- ---------------------------------------- ----------------
- 表`group_keywords`的限制
-
ALTER TABLE`group_keywords`
添加约束` group_keywords_ibfk_1` FOREIGN KEY（`keyword_id`）REFERENCES`keywords`（`id`）ON DELETE CASCADE ON UPDATE CASCADE;
这个操作传统上被称为unpivot RDBMS支持它，但MySQL似乎不是其中之一。您有两个选项，在SQL中执行或在PHP中执行。在MySQL中，它看起来像这样一个自我连接（我不知道哪个字段符合你的ID字段，所以请原谅我创建我自己的例子）。从性能角度来看，请确保您同时对ID和列名称进行索引，否则这些连接将会爬网。
shape
ID名称值
1颜色红色
1形状圆形
...更多列
2颜色绿色
2形状Square
.. 。更多列
SELECT
A.ID，
B.Value as Color，
C.Value as Shape
... for更多列
FROM形状A
LEFT JOIN形状B ON B.ID = A.ID AND B.Name ='颜色'
LEFT JOIN形状C ON C.ID = A. ID AND C.Name ='Shape'
...更多列
哪个应该净我们（除非我的头 - SQL解析器今天晚上错误）：
ID颜色形状
1红色圈
2绿色广场
对于PHP版本，您不一定需要加载一个数组，你可以流式传输。按照PK排序，然后沿着它设置属性。在伪代码中：
将X设置为undefined
获取记录
检查ID属性（如果不同）比X创建一个新对象，将X设置为新的ID，并生成上一个对象
根据结果的Name列设置对象的属性
希望这有帮助！
I have a database where I store keywords grouped into projects and data related to each keyword, then I display datagrids foreach project with one row per keyword and several columns all retrieved from the same table "data". I have 4 tables, keywords, projects, group_keywords and data. "keywords" only stores the keyword, "projects" the project name, "group_keywords" the keywords ids for the keywords assigned to that project, and "data" is where all the data foreach keyword goes, identified by a foreign key for the keywords.id, and a name column to identify the data name.
Now to retrieve the keywords + all the data for a project I use this query:
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'
This gives me something like
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
...
But what I want is to get:
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
...
So I can sort and use pagination for the datagrids easly, otherwise I have no idea how to do it, because when using big data sets I can't just dump everything into an array, too much data.
This is the schema:
-- --------------------------------------------------------
-- 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;
This operation is traditionally called "unpivot" and a few RDBMSs support it, but MySQL doesn't appear to be one of them. You have two options, do it in SQL or do it in PHP. In MySQL it looks something like this with self-joins (I don't know which field qualifies as an ID field for you, so forgive me creating my own example). From a performance perspective, make sure you index both the ID and Column Name, otherwise these joins will crawl.
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"
Which should net us (unless my head-SQL-parser is misrunning tonight):
ID Color Shape
1 Red Circle
2 Green Square
For the PHP version, you don't necessarily have to load up an array, you can stream it. Sort by the PK and walk down it setting the properties. In pseudocode:
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
Hope this helps!
这篇关于如何将行转成列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
