mySQL在一个查询中从多个表中获取信息

I'm terribly new to SQL, and cannot seem to get the desired information out, after trying a few different google searches, and reading through some SQL tutorials.

我对SQL非常陌生，在尝试了几个不同的谷歌搜索并阅读了一些SQL教程后，似乎无法获得所需的信息。

I think it involves some sort of joins, but cannot get them straight.

我认为它涉及某种联接，但不能直截了当。

Given the following sample tables:

给出以下示例表：

Table 1(Activity This is updated every time a change is made to a task, could be manytimes per day):

表1（活动每次对任务进行更改时都会更新，每天可以多次）：

ID  Who     What        When
001 John    Created 2008-10-01<br>
001 Bill    Closed  2008-10-02<br>
001 John    Updated 2008-10-03<br>
002 Bill    Created 2008-10-04<br>
002 John    Updated 2008-10-05<br>
002 Bill    Closed  2008-10-06<br>

Table 2(Tasks - This is the main task tracking table):

表2（任务 - 这是主要任务跟踪表）：

ID  Created Status
001 2008-10-01  Closed
002 2008-10-04  Closed

Table 3(Comments):

表3（评论）：

ID  When    Comment<br
001 2008-10-01  "I'm creating a new task"
001 2008-10-02  "I have completed the task"
001 2008-10-03  "Nice job"
002 2008-10-04  "I'm creating a second task"
002 2008-10-05  "This task looks too easy"
002 2008-10-06  "I have completed this easy task"

What SQL (mySQL if it makes any difference) query would I use to find out who had done something on a task that had been closed?

什么SQL（mySQL，如果它有什么不同）查询我会用来找出谁已经关闭的任务做了什么？

The results would be something like:

结果将是这样的：

Who What    ID  When    Comment
Bill Updated 002 2008-10-03 "Nice job"

Meaning that Bill changed task 002 after it was closed, and added the comment "Nice Job"

这意味着Bill在关闭后更改了任务002，并添加了评论“Nice Job”

Any help would be much appreciated.

任何帮助将非常感激。

Thanks in advance.

提前致谢。

3 个解决方案

#1

SELECT a1.Who, a1.What, a1.ID, c.When, c.Comment
FROM Activity AS a1
  JOIN Activity AS a2 ON (a1.ID = a2.ID AND a1.When > a2.When)
  JOIN Comments AS c ON (a1.ID = c.ID AND a.When = c.When);
WHERE a2.What = 'Closed';

I think you need some way to associate a row in Comments to the correct row in Activity. Right now if two people comment on a given Task on the same day, you don't know whose comment is whose. I'd recommend that you give each row in Activity a unique key, and then reference that from the Comments table.

我认为您需要一些方法将Comments中的行与Activity中的正确行相关联。现在，如果两个人在同一天评论给定的任务，你不知道谁的评论是谁。我建议您为Activity中的每一行指定一个唯一键，然后从Comments表中引用它。

CREATE TABLE Tasks (
  Task_ID      INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Created      DATE NOT NULL,
  Status       VARCHAR(10)
) TYPE=InnoDB;

CREATE TABLE Activity (
  Activity_ID  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Task_ID      INT NOT NULL REFERENCES Tasks,
  Who          VARCHAR(10) NOT NULL,
  What         VARCHAR(10) NOT NULL,
  When         DATE NOT NULL
) TYPE=InnoDB;

CREATE TABLE Comments (
  Comment_ID   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Activity_ID  INT NOT NULL REFERENCES Activity,
  Who          VARCHAR(10) NOT NULL,
  When         DATE NOT NULL,
  Comment      VARCHAR(100) NOT NULL
) TYPE=InnoDB;

Then you can make associations so the query returns more accurate results:

然后，您可以建立关联，以便查询返回更准确的结果：

SELECT c.Who, a1.What, a1.Task_ID, c.When, c.Comment
FROM Activity AS a1
  JOIN Activity AS a2 ON (a1.Task_ID = a2.Task_ID AND a1.When > a2.When)
  JOIN Comments AS c ON (a1.Activity_ID = c.Activity_ID);
WHERE a2.What = 'Closed';

Make sure to use TYPE=InnoDB in MySQL because the default storage engine MyISAM doesn't support foreign key references.

确保在MySQL中使用TYPE = InnoDB，因为默认存储引擎MyISAM不支持外键引用。

#2

You will need to use a JOIN statement to retrieve fields from multiple tables. http://www.w3schools.com/Sql/sql_join.asp

您将需要使用JOIN语句从多个表中检索字段。 http://www.w3schools.com/Sql/sql_join.asp

SELECT Activity.Who, Activity.What, Comments.When, Comments.Comment FROM Activity JOIN Comments ON Activity.ID = Comments.ID JOIN Tasks ON Comments.ID = Tasks.ID WHERE Tasks.Status = 'Closed'

Also, you're table structure looks to have a little redundancy.

此外，您的表结构看起来有一点冗余。

#3

Are you planning on joining the tables based on the date? So you will only have 1 change per day?

您是否计划根据日期加入表格？那么你每天只会换1次？