根据布尔列升序或降序 [英] ascending or descending order according to boolean column

问题描述

这是PostgreSQL 10中的表结构.

This is the table structure in PostgreSQL 10.

即使date"是一个 int，它也代表一个 yyyy mm dd 日期.我正在尝试编写一个按日期排序的 SELECT，当它是 BC=true 时它是 desc，所以日期将按正确的顺序 -500 01 02 然后 -500 01 03 (yyyy mm dd)
当是 BC=false 它是 asc，所以日期将再次按正确顺序 1500 01 02，然后 150 01 03 (yyyy mm dd)

Even though "date" is an int, it represents a yyyy mm dd date. I am trying to write a SELECT that orders by date and when it is BC=true it is desc, so dates will be in the right order -500 01 02 then -500 01 03 (yyyy mm dd)
and when is is BC=false it is asc, so dates will be in the right order again 1500 01 02, then 150 01 03 (yyyy mm dd)

我想出了这个 SELECT * FROM test ORDER BY bc=true desc, bc=false asc; 在 BC 日期上效果很好，但它翻转了 AD 日期(15000103 然后 15000102，即是错的).

I came up with this SELECT * FROM test ORDER BY bc=true desc, bc=false asc; that does great on BC dates, but it flips the AD dates (15000103 then 15000102, that is wrong).

我知道有 date 类型可用，但我希望它可以作为精确 BC 日期的 hack.

I know there is the date type available, but I want this to work as a hack for exact BC dates.

如何更改我的 SELECT 以根据 BC 布尔列正确排序日期?

How can I change my SELECT to properly order the dates according to the BC boolean column?

谢谢

推荐答案

我认为 bc=true desc, bc=false asc 效果不佳.等我有了电脑，我会再检查一下，稍后更新我的答案.

I don't think bc=true desc, bc=false asc is working good. I'll check again when I have my computer and update my answer later.

也许我的解决方案只是一个伎俩或作弊.这不是合法的方式.你可以试试这个.

Maybe my solutions is just a trick or a cheat. This is not a legal way. You can try this.

SELECT * FROM test 
ORDER BY bc DESC, CASE WHEN bc THEN date*(-1) ELSE date END ASC;

或者

SELECT * FROM test 
ORDER BY bc DESC, CASE WHEN bc THEN abs(date) ELSE date END ASC;

希望我的回答能让你满意.

Hopefully my answer will satisfy you.

这篇关于根据布尔列升序或降序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！