仅当具有最新值的列“ date”具有另一列非NULL时，才检索行 [英] Retrive row only if the column 'date' with the latest value have an another column not NULL

问题描述

假设我有这张桌子

id  | value | date       | result
----+-------+------------+--------
1   | 1     | 2017-01-01 | NULL 
2   | 1     | 2017-01-02 | NULL 
3   | 2     | 2017-01-03 | NULL 
4   | 1     | 2017-01-04 | NULL  
5   | 2     | 2017-01-05 | NULL 
6   | 3     | 2017-01-06 | NULL 
7   | 1     | 2017-01-07 | NULL 

我的目标是检索最后一个日期具有结果的每个值 等于NULL。

My goal is to retrieve every "value" for which the last "date" has a "result" equal to NULL.

例如，对于上一张表，我的结果应如下所示：

For instance, with the previous table, my result should look like this:

id  | value | date       | result
----+-------+------------+--------
5   | 2     | 2017-01-05 | NULL
6   | 3     | 2017-01-06 | NULL
7   | 1     | 2017-01-07 | NULL 

我使用的查询是：

SELECT DISTINCT ON (value) * 
FROM table 
WHERE result IS NULL 
ORDER BY value ASC, date DESC

但是当我得到此表时：

id  | value | date       | result
----+-------+------------+--------
1   | 1     | 2017-01-01 | NULL
2   | 1     | 2017-01-02 | NULL
3   | 2     | 2017-01-03 | NULL
4   | 1     | 2017-01-04 | NULL
5   | 2     | 2017-01-05 | something
6   | 3     | 2017-01-06 | NULL
7   | 1     | 2017-01-07 | NULL

我获得

id  | value | date       | result
----+-------+------------+--------
4   | 2     | 2017-01-04 | NULL
6   | 3     | 2017-01-06 | NULL
7   | 1     | 2017-01-07 | NULL

而不是

id  | value | date       | result
----+-------+------------+--------
6   | 3     | 2017-01-06 | NULL
7   | 1     | 2017-01-07 | NULL

我尝试使用group by / hading，但这比其他方式更麻烦。
这是不可能做的，还是我缺少真正简单的东西？

I tried to use group by / having, but it was more a disaster than else. Is this impossible to do or I'm missing something really simple?

推荐答案

您应该在查询：

select *
from (
    select distinct on (value) * 
    from my_table 
    order by value asc, date desc
    ) s
where result is null
order by id

 id | value |    date    | result 
----+-------+------------+--------
  6 |     3 | 2017-01-06 | 
  7 |     1 | 2017-01-07 | 
(2 rows)

这篇关于仅当具有最新值的列“ date”具有另一列非NULL时，才检索行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！