PostgreSQL的：查询从同桌

Question

我有一个表：

id1 | date  | idreff | 
A11 | 2015-01-01| A11 
B12 | 2016-02-03| A11 
C53 | 2016-05-26| A11 

ID1有一个IDreff。我想从日期提取两种年龄： 1.交易本身的年龄 2.基于IDreff的第一笔交易的年龄。

所以，我想这样的结果：

id1 | date  | idreff | Age of Transaction | Age IDreff 

A11 | 2015-01-01| A11  
B12 | 2016-02-03| A11  
C53 | 2016-05-26| A11 

我如何得到的结果两种查询（交易ID1和IDreff岁）在相同的查询？

我试着用这样的：

SELECT *, age(t1.date) AS age1, age(t2.date) FROM table t1, table t2 WHERE t1.idreff=t2.id1 

但是因为给我翻倍每一行的结果是不正确的。

Answer 1

您可以修改这样的SQL：

SELECT 
    t1.*, age(t1.date) AS age1, age(t1.date, t2.date) 
FROM 
    table t1, table t2 
WHERE 
    t1.idreff=t2.id1 

Answer 2

你应该写更精确的加入：

SELECT 
    *, 
    age(t1.date) AS age1, 
    age(COALESCE(t2.date,t1.date)) as base_age 
FROM 
    table t1 
LEFT JOIN table t2 ON (t1.idreff=t2.id1 AND t1.id1<>t2.id1) 
WHERE 1 = 1