使用一次查询从多张表中获取mySQL的信息

Question

使用一次查询从多张表中获取mySQL的信息

mysqldatabase

4

我对SQL非常陌生，尝试了几次谷歌搜索并阅读了一些SQL教程，但似乎无法获取所需的信息。

我认为这涉及某种连接，但我无法理解它们。

以下是示例表：

表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>

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

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

表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"

如果我想查找关闭的任务中谁做了什么事情，我应该使用什么SQL查询语句（如果是mySQL会有什么不同）？

查询结果可能像这样：

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

意思是Bill在任务002关闭后更改了它，并添加了评论“干得好”。非常感谢您的帮助。提前致谢。

- CalvinTreg

也许您可以发布表的定义，包括外键和主键。例如，表1的主键是什么？表2和表1如何相关联？通过Id吗？ - Vincent Ramdhanie

所有的表格都是通过ID相关联的。我认为我需要确定的第一件事情是任务关闭后是否添加了活动，然后找出是谁执行了该活动，他们执行了什么活动，何时执行的以及他们的评论是什么。 - CalvinTreg

3个回答

2

你需要使用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'

此外，你的表结构似乎存在一些冗余。

- Totty

Totty的解决方案可以获取当前已关闭的任务中的所有评论，包括在任务关闭之前发表的评论。 - Bill Karwin

0

你打算根据日期加入表格吗？这样每天只会有一次更改？

- Darryl Hein

在我看来，他似乎正在使用Task.ID作为主键，并且其他表没有主键，只是将它们的ID字段作为Task.ID的外键。 - Totty

我计划查明开发人员在任务关闭后是否采取了行动，因此我假设日期是关键因素？是指关闭日期之后的活动日期吗？ - CalvinTreg

它可能会在一天内更改多次。 - CalvinTreg

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

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';

我认为您需要一种方法将评论中的行与活动中的正确行关联起来。目前，如果两个人在同一天对给定任务进行评论，您不知道哪个评论属于谁。我建议您为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;

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

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';

在使用MySQL时，请务必使用TYPE=InnoDB，因为默认的存储引擎MyISAM不支持外键约束。