仅当具有最新值的列“ date”具有另一列非NULL时,才检索行 [英] Retrive row only if the column 'date' with the latest value have an another column not NULL
本文介绍了仅当具有最新值的列“ date”具有另一列非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屋!
查看全文