需要一个在非空值之后返回空值的 postgresql 查询

发布于 2024-11-08 18:28:57 字数 455 浏览 0 评论 0原文

需要一个 postgresql 查询，该查询在非空值末尾返回空值，无论升序/降序如何。

例如：- 我有一列 buy_price 的值为 1, 5, 3, 0, null, null

然后对于 ORDER BY buy_price ASC 它应该返回

0
1
3
5
null
null

，对于 ORDER BY buy_price DESC code> 它应该返回

5
3
1
0
null
null

我需要概括解决方案，我可以将其应用于 'boolean'、'float'、'string'、 'integer', 'date' 数据类型

原文

Need a postgresql query which return null value at the end of the non-null value irrespective of the Ascending/Descending Order.

For ex:- I have a column say purchase_price having values 1, 5, 3, 0, null, null

Then for ORDER BY purchase_price ASC It should return

0
1
3
5
null
null

and for ORDER BY purchase_price DESC It should return

5
3
1
0
null
null

I need generalise solution which i can able to apply for the 'boolean', 'float', 'string', 'integer', 'date' Datatype

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

阳光下慵懒的猫 2024-11-15 18:28:57

order by 支持nulls first/last。默认情况下，asc 获取 nulls last，desc 获取 nulls first。您可以为每一列覆盖它们：

order by col1 desc nulls last, col2 desc nulls first, etc

对此的额外说明：根本原因是它们被放置在 btree 索引的末尾。在 PostgreSQL 9.0 中，对 order by col desc nulls last 进行了优化，将索引扫描一分为二（反向扫描非空行，然后扫描空行）。在添加它的原始版本中并非如此（如果没记错的话，8.4）；后者对整个表进行 seq 扫描，然后进行快速排序）。而且旧版本根本不支持该功能。因此，除了最新版本的 PostgreSQL 之外，请谨慎使用它。

order by supports nulls first/last. By default asc gets nulls last and desc gets nulls first. You can override them for each column:

order by col1 desc nulls last, col2 desc nulls first, etc

An extra note on this: the underlying reason is that nulls they get placed at the end of the btree index. In PostgreSQL 9.0, order by col desc nulls last is optimized to split the index scan in two (reverse scan on not null rows, followed by null rows). This was not the case in the original version where it was added (8.4 if memory serves); the latter proceeds to do a seq scan of the whole table followed by a quicksort). And older versions do not support the feature at all. So be wary of using it in anything but recent versions of PostgreSQL.

回复收藏 0 原文

滿滿的愛 2024-11-15 18:28:57

select * from (select * from foo where x is not null order by x desc) a union all select  * from foo where x is null;

这是一个廉价的技巧，但您可以按照您想要的任何顺序选择非空案例，然后对空案例执行 UNION ALL（这里所有内容都很重要），这将严格遵循之后。

select * from (select * from foo where x is not null order by x desc) a union all select  * from foo where x is null;

A cheap hack, but you can select the non-null cases with whatever ordering you want, and then do a UNION ALL (all is all important here) with the null cases, which will strictly follow afterwards.

回复收藏 0 原文

七度光 2024-11-15 18:28:57

我总是用一个偷偷摸摸的联合来完成此操作，遵循以下几行：

select *, '1' as orderCol
from table
where nullableColumn is not null
union
select *, '2' as orderCol
from table
where nullableColumn is null
order by orderCol, nullableColumn

您显然必须更改它以进行排序 desc - 例如，通过以相反的方式设置值......

相当一般，但不是很优雅。

I've always done this with a sneaky union, along the following lines:

select *, '1' as orderCol
from table
where nullableColumn is not null
union
select *, '2' as orderCol
from table
where nullableColumn is null
order by orderCol, nullableColumn

You clearly have to change it for ordering desc - by setting the values the other way round, for instance...

Fairly general, but not very elegant.

回复收藏 0 原文

~没有更多了~