sql - PostgreSQL 选择主记录并显示特定记录的详细信息列

Question

首先，我必须为糟糕的主题文字道歉，但我没有更好的主意。也许正因为如此，我在网上搜索时没有找到解决方案。

我有 2 个表:master 和 details 谁当然有外键要掌握。我想从 master 中获取所有行和所有字段，并从 master 中每一行的特定记录的详细信息(比如某​​些列的顺序)中获取所有字段。

我试过这样的:

SELECT master.id, master.title, temp2.master_id, temp2.datetime, temp2.title_details
FROM master
LEFT JOIN (SELECT master_id, datetime, title AS title_details FROM details ORDER BY datetime DESC) temp2 ON temp2.master_id=master.id

//and this:
SELECT master.id, master.title, 
(SELECT master_id, datetime, title AS title_details FROM details WHERE master.id=details.master_id ORDER BY datetime DESC) 
FROM master
//but of course: subquery must return only one column

但这行不通。

例如我想做什么:

Master:
id  title
1   test
2   blab
3   something

Details:
id  master_id   datetime    title
1   1           2004-...    t: 1.1
2   1           2005-...    t: 2.1
3   1           2006-...    t: 3.1
4   2           2004-...    t: 4.2
5   2           2005-...    t: 5.2
6   3           2006-...    t: 6.3  

Expected output:
id  title       datetime    title_details
1   test        2006-...        t: 3.1
2   blab        2005-...        t: 5.2
3   something   2006-...        t: 6.3

因为我很难解释我需要什么，下面是我不想做的 PHP 代码(来自 head):

$q = Database::$DB->prepare("SELECT * FROM master");
$q2 = Database::$DB->prepare("SELECT * FROM details WHERE master_id=? ORDER BY datetime DESC LIMIT 1");
$rows = $q->execute();
foreach ($rows as $row)
{
    $q2->execute($row->id);
    $row->AdditionalFields = $q2->fetch();
} 

换句话说，我不想遍历所有主行并详细选择特定一条记录的数据(最后 - 按日期时间排序)。

我尝试了所有不同的 UNION、JOINS 和 SUBQUERIES，但没有成功。

已编辑(对不同答案发表评论):

实际查询是:

SELECT DISTINCT ON (todo_topics.id) todo_topics.id, todo_topics.user_id, users.username AS author, todo_topics.title, todo_topics.datetime_created, todo_topics.version, todo_topics.todo_status_id, todo_statuses.icon_image, 
    todo_topics.version_status_changed, todo_posts.text, u.username AS last_poster, todo_posts.user_id as last_poster_id
FROM todo_topics
LEFT JOIN todo_statuses ON todo_statuses.id = todo_topics.todo_status_id
LEFT JOIN users ON users.id = todo_topics.user_id
LEFT JOIN todo_posts ON todo_topics.id=todo_posts.todo_topic_id
LEFT JOIN users u ON u.id = todo_posts.user_id
ORDER BY todo_topics.id, todo_posts.datetime_created  DESC

“总运行时间:0.863 毫秒”

    SELECT
      todo_topics.id, todo_topics.user_id, users.username AS author, todo_topics.title, todo_topics.datetime_created, todo_topics.version, todo_topics.todo_status_id, todo_statuses.icon_image, 
        todo_topics.version_status_changed, todo_posts.text, u.username AS last_poster, todo_posts.user_id as last_poster_id
    FROM
      todo_topics
      LEFT JOIN todo_statuses ON todo_statuses.id = todo_topics.todo_status_id
    LEFT JOIN users ON users.id = todo_topics.user_id

    INNER JOIN
    (
      SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY todo_topic_id ORDER BY datetime_created DESC) AS ordinal
      FROM
        todo_posts
    )
      AS todo_posts
        ON todo_posts.todo_topic_id = todo_topics.id
    LEFT JOIN users u ON u.id = todo_posts.user_id
    WHERE
      todo_posts.ordinal = 1

“总运行时间:1.281 毫秒”

 SELECT
  todo_topics.id, todo_topics.user_id, users.username AS author, todo_topics.title, todo_topics.datetime_created, todo_topics.version, todo_topics.todo_status_id, todo_statuses.icon_image, 
    todo_topics.version_status_changed, todo_posts.text, u.username AS last_poster, todo_posts.user_id as last_poster_id
FROM
  todo_topics
LEFT JOIN todo_statuses ON todo_statuses.id = todo_topics.todo_status_id
LEFT JOIN users ON users.id = todo_topics.user_id
INNER JOIN
(
  SELECT
    todo_topic_id,
    MAX(datetime_created)  AS max_datetime
  FROM
    todo_posts
  GROUP BY
    todo_topic_id
)
  AS details_lookup
    ON  details_lookup.todo_topic_id = todo_topics.id
INNER JOIN
  todo_posts
    ON  todo_posts.todo_topic_id = details_lookup.todo_topic_id
    AND todo_posts.datetime_created  = details_lookup.max_datetime
LEFT JOIN users u ON u.id = todo_posts.user_id

“总运行时间:1.143 毫秒”

如果有人想知道这个时间对特定硬件意味着什么:

数据库是实验性的(每个表中有几条记录 - < 100)在 Windows 7 本地主机、Intel I7 3,4GHz、16GB ram、PostgreSQL 9.3.4(默认安装)上运行

Answer 1

使用 DISTINCT ON 更简单:

SELECT DISTINCT ON (m.id)
       m.*, d.datetime, d.title AS title_details
FROM   master m
LEFT   JOIN details d ON d.master_id = m.id
ORDER  BY m.id, d.datetime DESC;

假设master.id为主键，details.datetime为NOT NULL。
详细解释:
Select first row in each GROUP BY group?

注意 datetime 是否可以为 NULL。你可能想要 NULLS LAST在这种情况下。