代码之家 › 专栏 › 技术社区 › Arya

按其他表中存在的行排序

postgresql sql

Arya · 技术社区 · 7 年前

我有两个没有外键连接的表。

一个叫 message_log 还有一个叫 assigned_conversation .

指定会话 还包含 match_id 柱

我下面的查询没有问题。

SELECT m.* 
FROM   message_log m 
WHERE  m.from_id <> 'MYID' 
       AND m.to_id = 'MYID' 
       AND m.unix_timestamp = (SELECT Max(unix_timestamp) 
                               FROM   message_log 
                               WHERE  match_id = m.match_id 
                               GROUP  BY match_id)

对于上面的查询,我只想更改结果的排序方式。

有没有可能将结果按 匹配id 存在于将表格放在结果顶部,用于 匹配id 这是世界上不存在的 指定会话 表中确实存在的表之后要排序的表桌子

3 回复 | 直到 7 年前

Tim Biegeleisen 7 年前

一种选择是使用 CASE 表达式,用于检查给定的 match_id 在外部查询中,可以在 assigned_conversation

SELECT m.* 
FROM   message_log m
WHERE  m.from_id <> 'MYID' 
   AND m.to_id = 'MYID' 
   AND m.unix_timestamp = (SELECT Max(unix_timestamp) 
                           FROM   message_log 
                           WHERE  match_id = m.match_id 
                           GROUP  BY match_id)
ORDER BY
    CASE WHEN EXISTS (SELECT 1 FROM assigned_conversation a
                      WHERE a.match_id = m.match_id)
         THEN 0 ELSE 1 END;

请注意,我们本可以尝试左键连接到 指定会话 ,但这可能会在结果集中引入重复项,假设 匹配id 在外部查询中,可能会在中出现多次 指定会话 . 使用 EXISTS 绕过这个问题。

Björn Nilsson 7 年前

您可以使用distinct来确保只获取最新的日志条目,而不必执行子查询,然后通过左键加入assigned_对话并按第一个匹配排序_id为null,然后使用m.unix_timestamp desc,您应该以干净高效的方式获得预期的结果

SELECT DISTINCT m.* 
FROM   message_log m 
left join assigned_conversation ac on ac.match_id = m.match_id
WHERE  m.from_id <> 'MYID' 
       AND m.to_id = 'MYID' 
ORDER BY ac.match_id is null, m.unix_timestamp desc

Gordon Linoff 7 年前

我会将查询简化为:

SELECT DISTINCT ON (ml.match_id) ml.* 
FROM message_log ml 
WHERE ml.from_id <> 'MYID' AND
      ml.to_id = 'MYID' 
ORDER BY ml.match_id, ml.unix_timestamp DESC;

SELECT ml.*
FROM (SELECT DISTINCT ON (ml.match_id) ml.*
      FROM message_log ml 
      WHERE ml.from_id <> 'MYID' AND
            ml.to_id = 'MYID' 
      ORDER BY ml.match_id, ml.unix_timestamp DESC
     ) ml
ORDER BY (EXISTS (SELECT 1 FROM assigned_conversation ac WHERE ac.match_id = ml.match_id))::int DESC,
         match_id;

但是,我倾向于将标志添加到结果集中的数据中:

SELECT ml.*
FROM (SELECT DISTINCT ON (ml.match_id) ml.*,
             (EXISTS (SELECT 1 FROM assigned_conversation ac WHERE ac.match_id = ml.match_id))::int as ac_flag
      FROM message_log ml 
      WHERE ml.from_id <> 'MYID' AND
            ml.to_id = 'MYID' 
      ORDER BY ml.match_id, ml.unix_timestamp DESC
     ) ml
ORDER BY ac_flag desc, match_id;