有没有一种方法可以限制左外连接？

Question

我有一张日志表，格式如下（包含样本数据）：

  date     |  time
2010/01/02 | 10:00
2010/01/02 | 13:00
2010/01/04 | 02:34

我想要进行一次SQL查询，以便检索出如下格式的结果：

date1      | time1 | date2      | time2
2010/01/02 | 10:00 | 2010/01/02 | 13:00
2010/01/02 | 13:00 | 2010/01/04 | 02:34
2010/01/04 | 02:34 | <null>     | <null>

所以我想用左外连接来解决这个问题：

SELECT
  i.date as date1,
  i.time as time1,
  j.date as date2,
  j.time as time2
FROM
  log_table i
  LEFT OUTER JOIN
    log_table j
  ON
    i.id = j.id
    AND (j.date > i.date
    OR (j.date == i.date AND j.time > i.time))

然而，这将导致以下结果：

date1      | time1 | date2      | time2
2010/01/02 | 10:00 | 2010/01/02 | 13:00
2010/01/02 | 10:00 | 2010/01/04 | 02:34
2010/01/02 | 13:00 | 2010/01/04 | 02:34
2010/01/04 | 02:34 | <null>     | <null>

顺便说一下，所使用的数据库是PostgreSql。

谢谢。

- absolute0

---

我发现在ON子句之后缺少了WHERE。 - Pentium10

我猜你想要将“i”中的一行与其后面的“j”中的一行配对？从你的描述中并不完全清楚。 - ijw

Answer 1

在 PostgreSQL 8.4 中：

SELECT  date AS date1, time AS time1,
        LEAD(date) OVER (ORDER BY date, time, id) AS date2,
        LEAD(time) OVER (ORDER BY date, time, id) AS time2
FROM    log_table
ORDER BY
        date, time, id

或者只是这个：

SELECT  date1, time1, (lnext).*
FROM    (
        SELECT  date AS date1, time AS time1,
                LEAD(lt) OVER (ORDER BY date, time, id) AS lnext
        FROM    log_table lt
        ) q
ORDER BY
        date, time, id

在PostgreSQL 8.3及以下版本中：

SELECT  date AS date1, time AS time1,
        (li).date AS date2, (li).time AS time2
FROM    (
        SELECT  lo.*,
                (
                SELECT  li
                FROM    log_table li
                WHERE   (li.date, li.time, li.id) > (lo.date, lo.time, lo.id)
                ORDER BY
                        date, time, id
                LIMIT 1
                ) AS li
        FROM    log_table lo
        ) q

- Quassnoi

---

@ijw 是的，“i”和“j”基本上是相同的表格。如果行按照日期和时间排序，我希望一行与其后面的行配对。 - absolute0

@Quassnoi 这是我第一次听说lead over，如果在“i”和“j”中除了日期和时间之外还有其他列，它也能正常工作吗？ - absolute0

@absolute0：是的，可以。在PostgreSQL中，你可以在一个字段中选择整个记录，并在上层查询或客户端上展开它。 - Quassnoi

不幸的是，我们正在使用PostgreSQL 8.1，我会尝试您的其他建议。 - absolute0

PostgreSQL 8.3及以下版本的建议没有起作用，但凭直觉我改变了原始SQL的这一部分：“AND (j.date>i.date或(j.date == i.date AND j.time > i.time))”，使用了你所使用的语法：“AND (j.date，j.time)>(i.date，i.time)”，出乎意料地它按照我所期望的工作了...这是我第一次遇到这种语法。谢谢！ - absolute0

Answer 2

SELECT 
  i.date as date1, 
  i.time as time1, 
  min(j.date) as date2, 
  min(j.time) as time2 
FROM 
  log_table i 
  LEFT OUTER JOIN 
    log_table j 
  ON 
    i.id = j.id 
    AND (j.date > i.date 
    OR (j.date == i.date AND j.time > i.time)) 
GROUP BY i.date, i.time

- HLGEM