组合两个MySQL表，将一个数据用于组合表中的标题

I want to combine two MySQL tables, but use some data in one of them as new headers in the combined one. I have two tables, like this:

我想组合两个MySQL表,但在其中一个表中使用一些数据作为组合的新表头。我有两个表,像这样:

A log of fruits we've eaten each day.

我们每天吃的水果记录。

id date       fruit
5  2011-11-27 banana
4  2011-11-26 apple
3  2011-11-25 orange
2  2011-11-24 banana
1  2011-11-23 pear

A table with what we think of the fruits we've eaten.

我们想到我们吃过的水果的表格。

id user   fruit  rating
7  andrew banana yum
6  andrew apple  eww
5  lisa   apple  yum
4  andrew orange yum
3  lisa   orange yum
2  andrew pear   eww
1  lisa   pear   eww

What I'm looking for is a SQL query with a result something like this:

我正在寻找的是一个SQL查询,其结果如下:

date       fruit  andrew lisa
2011-11-27 banana yum    NULL
2011-11-26 apple  eww    yum
2011-11-25 orange yum    yum
2011-11-24 banana yum    NULL
2011-11-23 pear   eww    eww

I suppose I'm not the first to want something like this; but it is not very easy to look for the answer when you have no idea to describe it except by showing the tables and the desired result.

我想我不是第一个想要这样的人;但是,除非通过显示表格和所需结果,否则在您不知情的情况下查找答案并不容易。

For my own experimenting, I have now actually created the example tables I use above. (What I really need it for is conceptually similar, but I don't actually know anyone named andrew and I don't log fruits we eat. Anyhow, the real tables are somewhat larger and more complicated - these will do for describing the concept.

对于我自己的实验,我现在实际上创建了我上面使用的示例表。 (我真正需要它在概念上是相似的,但我实际上并不认识任何名为andrew的人,我也没有记录我们吃的水果。无论如何,真正的表格更大更复杂 - 这些将用于描述概念。

--
-- Table structure `fruit_log`
--

CREATE TABLE IF NOT EXISTS `fruit_log` (
  `id` int(11) NOT NULL auto_increment,
  `date` date NOT NULL,
  `fruit` varchar(6) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping of data in table `fruit_log`
--

INSERT INTO `fruit_log` (`id`, `date`, `fruit`) VALUES
(1, '2011-11-23', 'pear'),
(2, '2011-11-24', 'banana'),
(3, '2011-11-25', 'orange'),
(4, '2011-11-26', 'apple'),
(5, '2011-11-27', 'banana');

--
-- Table structure `fruit_log`
--

CREATE TABLE IF NOT EXISTS `fruit_ratings` (
  `id` int(11) NOT NULL auto_increment,
  `user` varchar(6) NOT NULL,
  `fruit` varchar(6) NOT NULL,
  `rating` enum('yum','eww') default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping of data in table `fruit_ratings`
--

INSERT INTO `fruit_ratings` (`id`, `user`, `fruit`, `rating`) VALUES
(1, 'lisa', 'pear', 'eww'),
(2, 'andrew', 'pear', 'eww'),
(3, 'lisa', 'orange', 'yum'),
(4, 'andrew', 'orange', 'yum'),
(5, 'lisa', 'apple', 'yum'),
(6, 'andrew', 'apple', 'eww'),
(7, 'andrew', 'banana', 'yum');

I've learnt a lot trying to find an answer to this question. To begin with, "pivot tables" as a concept, introduced to me by @w0051977 . w0051977's link is however specific to Microsoft SQL Server - I run MySQL. I was introduced to the concept, anyway, and looked further. I learnt some things from Wikipedia and a lot from this chronicle: http://dev.mysql.com/tech-resources/articles/wizard/print_version.html

我学到了很多东西,试图找到这个问题的答案。首先,@ pivot table“作为一个概念,由@ w0051977介绍给我。然而,w0051977的链接特定于Microsoft SQL Server - 我运行MySQL。无论如何,我被介绍了这个概念,并进一步展望。我从*学到了一些东西,还有很多来自这篇编年史的东西:http://dev.mysql.com/tech-resources/articles/wizard/print_version.html

However; I have been unable to find anything that even remotely resembles my goal. The hard part seems to be using a dynamic number of cell data (data from the "user" column: "andrew" and "lisa" in my example, but could also include "bob" and perhaps even "alice", etc) and "flipping" them over to instead act as column headers.

然而;我一直无法找到任何与我的目标非常相似的东西。困难的部分似乎是使用动态数量的单元格数据(来自“用户”列的数据:“andrew”和“lisa”在我的例子中,但也可能包括“bob”,甚至可能包括“alice”等)和“翻转”它们而不是作为列标题。

The Wizard does this in the chronicle linked above, but not in a single query: he uses a clever SQL trick to generate part of another SQL query that can be pasted into the query that does what he wanted to do to begin with. Of course you don't have to actually copy and paste it - the Wizard automates it with Perl, and I'd do it with PHP (this is part of a PHP-based project). But if I have to use PHP to generate a query anyway, I might as well do it a simpler way.

向导在上面链接的编年史中执行此操作,但不是在单个查询中:他使用一个聪明的SQL技巧来生成另一个SQL查询的一部分,该查询可以粘贴到执行他想要开始执行的操作的查询中。当然,您不必实际复制和粘贴它 - 向导使用Perl自动执行它,我会使用PHP(这是基于PHP的项目的一部分)。但是,如果我必须使用PHP来生成查询,我不妨以更简单的方式做到这一点。

@adam-wenger suggested that I use LEFT JOIN, but noted that it would only use for a limited number of users. This is his query, adapted to the table names I introduced above in an edit after his answer was posted.

@ adam-wenger建议我使用LEFT JOIN,但指出它只能用于有限数量的用户。这是他的查询,在他的回答发布后,根据我在编辑中介绍的表名进行了调整。

SELECT fruit_log.date, fruit_log.fruit, a.rating AS 'andrew', l.rating AS 'lisa'
FROM fruit_log
LEFT JOIN fruit_ratings AS a
    ON fruit_log.fruit = a.fruit
    AND a.user = 'andrew'
LEFT JOIN fruit_ratings AS l
    ON fruit_log.fruit = l.fruit
    AND l.user = 'lisa'
ORDER BY date DESC

With PHP, I can generate as many LEFT JOINs as I need, thus solving my problem. I can also use sub-queries, as thus:

使用PHP,我可以根据需要生成尽可能多的LEFT JOIN,从而解决了我的问题。我也可以使用子查询,因为:

SELECT fruit_log.date, fruit_log.fruit, (
    SELECT fruit_ratings.rating
    FROM fruit_ratings
    WHERE fruit_ratings.user = 'andrew'
    AND fruit_ratings.fruit = fruit_log.fruit
) AS 'andrew', (
    SELECT fruit_ratings.rating
    FROM fruit_ratings
    WHERE fruit_ratings.user = 'lisa'
    AND fruit_ratings.fruit = fruit_log.fruit
) AS 'lisa'
FROM fruit_log
ORDER BY date DESC

I suppose one of them is more efficient than the other, but I don't know which one (if I have the time and the interest I'll do a benchmark some time, unless someone provides insight before that happens).

我想其中一个比另一个更有效,但我不知道哪一个(如果我有时间和兴趣,我会做一段时间的基准测试,除非有人在此之前提供见解)。

If someone has a one query solution to my problem, it would be very appreciated. Until then; I'll have to stick to PHP ducttape (and this question will remain unanswered).

如果有人对我的问题有一个查询解决方案,那将非常感激。直到那时;我将不得不坚持使用PHP ducttape(这个问题仍然没有答案)。

3 个解决方案

#1

If you only have two users, andrew and lisa, you can approach the problem with LEFT JOIN: (you'll have to update table names, as they were not in the question, I guessed)

如果你只有两个用户,andrew和lisa,你可以用LEFT JOIN解决问题:(你必须更新表名,因为它们不在问题中,我猜)

SELECT fl.Date, fl.fruit, a.rating AS 'Andrew', l.rating AS 'Lisa'
FROM fruitLog AS fl
LEFT JOIN thoughts AS a ON fl.fruit = a.fruit
   AND a.user = 'andrew'
LEFT JOIN thoughts AS l ON fl.fruit = l.fruit
   AND l.user = 'lisa'
ORDER BY fl.Date DESC

If you had additional users, more could be added with Additional LEFT JOINS

如果您有其他用户,可以使用Additional LEFT JOINS添加更多用户

#2

I think you will have to use an SQL Pivot. Have a look at a tutorial: http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx.

我认为你将不得不使用SQL Pivot。看一下教程:http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx。

#3

A pivot table is probably what you're looking for, as suggested by w0051977 and Adam. Here's some info on creating pivot queries in MySQL: http://www.artfulsoftware.com/infotree/queries.php#78. You'd probably be interested in the second topic "automate pivot table queries". Not an easy query to get running though...

根据w0051977和Adam的建议,数据透视表可能正是您所寻找的。以下是有关在MySQL中创建数据透视查询的一些信息:http://www.artfulsoftware.com/infotree/queries.php#78。您可能对第二个主题“自动化数据透视表查询”感兴趣。不是一个简单的查询来运行...

#1