我有以下簡化查詢
SELECT
id
to_char(execution_date, 'YYYY-MM-DD') as execution_date
FROM schema.values
ORDER BY execution_date DESC, id DESC
execution_date 可以為空。
如果 execution_date 中沒有值，它將默認設定為 1970-01-01。我的問題是，下表的值將導致 1970-01-01 被視為最新日期的結果。
桌子：
| ID | 執行日期 |
|---|---|
| 1 | |
| 2 | 2020-01-01 |
| 3 | 2022-01-02 |
| 4 |
我期望的結果
| ID | 執行日期 |
|---|---|
| 3 | 2022-01-02 |
| 2 | 2020-01-01 |
| 4 | 1970-01-01 |
| 1 | 1970-01-01 |
我得到了什么
| ID | 執行日期 |
|---|---|
| 4 | 1970-01-01 |
| 1 | 1970-01-01 |
| 3 | 2022-01-02 |
| 2 | 2020-01-01 |
如何獲得正確的訂單，如果日期為空，是否可以輕松回傳空的 varchar？
uj5u.com熱心網友回復：
如果您的表有 NULL 值，而不是空值，您可以嘗試使用nulls last：
with t as (select 1 as id, NULL::date as dt
union select
2, '2020-01-01'::date
union select
3, '2020-01-02'::date
union select
4, NULL::date)
select *
from t
order by t.dt desc nulls last, id desc;
它也應該適用于空文本值：
with t as (select 1 as id, ''::text as dt
union select
2, '2020-01-01'::text
union select
3, '2020-01-02'::text
union select
4, NULL::text)
select *
from t
order by t.dt desc nulls last, id desc
如果您需要將 NULL 日期更改為 1970 年，請使用COALESCE()：
with t as (select 1 as id, NULL::date as dt
union select
2, '2020-01-01'::date
union select
3, '2020-01-02'::date
union select
4, NULL::date)
select coalesce(t.dt, '1970-01-01'::date) as dt
from t
order by t.dt desc nulls last, id desc
這是dbfiddle
uj5u.com熱心網友回復：
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=5d1fc31a3cf2d3121092f2446cce87e5
SELECT
id,
to_char(coalesce( execution_date, '1970-01-01'::date), 'YYYY-MM-DD') as execution_date
FROM values1
ORDER BY execution_date DESC, id DESC;
uj5u.com熱心網友回復：
我沒有只見樹木不見森林……
這是簡單的解決方案：
SELECT
id
CASE WHEN execution_date IS NULL THEN ''
ELSE to_char(execution_date, 'YYYY-MM-DD') END
AS execution_date
FROM schema.values
ORDER BY execution_date DESC, id DESC
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/475143.html
下一篇：R滾動添加或洗掉計數到串列