mySQL 在一个查询中从多个表中获取信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/231126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mySQL get information from multiple tables in one query
提问by CalvinTreg
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.
提前致谢。
回答by Bill Karwin
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 中的正确行相关联。现在，如果两个人在同一天对给定的 Task 发表评论，你不知道谁的评论是谁的。我建议您为 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=InnoDBin MySQL because the default storage engine MyISAM doesn't support foreign key references.
确保TYPE=InnoDB在 MySQL 中使用，因为默认存储引擎 MyISAM 不支持外键引用。
回答by Totty
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.
此外，您的表结构看起来有点冗余。
回答by Darryl Hein
Are you planning on joining the tables based on the date? So you will only have 1 change per day?
您是否打算根据日期加入表格？所以你每天只能换 1 次？
