mySQL从一个查询中的多个表中获取信息 [英] mySQL get information from multiple tables in one query
问题描述
我对SQL非常陌生，在尝试了几次不同的google搜索并阅读一些SQL教程后，似乎无法获得所需的信息。
给定以下示例表：
表1（活动每当对任务进行更改时，每次可以多次更新）：
ID Who what When
001约翰创建于2008-10-01< br>
001比尔结帐2008-10-02< br>
001约翰已更新2008-10-03< br>
002比尔创建时间2008-10-04< br>
002 John于2008-10-05更新< br>
002比尔结帐2008-10-06< br>
表2（任务 - 这是主要任务跟踪表）：
ID创建状态
001 2008-10-01已关闭
002 2008-10-04关闭
表3（注释）：
ID当评论< br
001 2008-10-01我正在创建一个新任务
001 2008-10-02我已完成任务
001 2008-10 -03好工作
002 2008-10-04我要创建第二个任务
002 2008-10-05这个任务看起来太容易了
002 2008-10 -06我已经完成了这个简单的任务
什么SQL
结果会是这样：
谁的什么ID当评论
比尔更新002 2008-10-03好工作
这意味着Bill在关闭后更改了任务002，并添加了评论Nice Job。
任何帮助非常感谢。
提前感谢。
p> SELECT a1.Who，a1.What，a1.ID，c.When，c.Comment
从活动AS a1
JOIN活动AS a2 ON（a1.ID = a2.ID AND a1.When> a2.When）
JOIN注释AS c ON（a1.ID = c.ID AND a.When = c.When）;
WHERE a2.What ='Closed';
我想你需要一些方法来将注释中的一行与Activity中的正确行关联。现在如果两个人在同一天对一个给定的任务发表评论，你不知道他的评论是谁的。我建议您给Activity中的每一行一个唯一键，然后从注释表中引用它。
CREATE TABLE任务（
Task_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT，
创建日期NOT NULL，
状态VARCHAR（10）
）TYPE = InnoDB;
CREATE TABLE Activity（
Activity_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT，
Task_ID INT NOT NULL REFERENCES任务
Who VARCHAR（10）NOT NULL，
什么VARCHAR（10）NOT NULL，
当DATE NOT NULL
）TYPE = InnoDB;
CREATE TABLE注释（
Comment_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT，
Activity_ID INT NOT NULL REFERENCES活动，
Who VARCHAR（10）NOT NULL，
当DATE NOT NULL，
注释VARCHAR（100）NOT NULL
）TYPE = InnoDB;
然后，您可以进行关联，以便查询返回更准确的结果：
SELECT c.Who，a1.What，a1.Task_ID，c.When，c.Comment
FROM活动AS a1
JOIN活动AS a2 ON（a1.Task_ID = a2.Task_ID AND a1.When> a2.When）
JOIN注释AS c ON（a1.Activity_ID = c.Activity_ID）;
WHERE a2.What ='Closed';
请务必使用 TYPE = InnoDB MySQL因为默认存储引擎MyISAM不支持外键引用。
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.
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):
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):
ID Created Status
001 2008-10-01 Closed
002 2008-10-04 Closed
Table 3(Comments):
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?
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"
Any help would be much appreciated.
Thanks in advance.
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.
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从一个查询中的多个表中获取信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
