sql通过名为的列循环查找最大日期

tzdcorbm 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(264)

我正在寻找sql循环通过20列的日期，以找到最大值。
我的代码现在有点糟糕：

,case 
    when ANNEX_20_DATE is not null and ANNEX_20_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_20_DATE
    when ANNEX_19_DATE is not null and ANNEX_19_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_19_DATE
    when ANNEX_18_DATE is not null and ANNEX_18_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_18_DATE
    when ANNEX_17_DATE is not null and ANNEX_17_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_17_DATE
    when ANNEX_16_DATE is not null and ANNEX_16_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_16_DATE
    when ANNEX_15_DATE is not null and ANNEX_15_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_15_DATE
    when ANNEX_14_DATE is not null and ANNEX_14_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_14_DATE
    when ANNEX_13_DATE is not null and ANNEX_13_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_13_DATE
    when ANNEX_12_DATE is not null and ANNEX_12_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_12_DATE
    when ANNEX_11_DATE is not null and ANNEX_11_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_11_DATE
    when ANNEX_10_DATE is not null and ANNEX_10_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_10_DATE
    when ANNEX_9_DATE is not null and ANNEX_9_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_9_DATE
    when ANNEX_8_DATE is not null and ANNEX_8_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_8_DATE
    when ANNEX_7_DATE is not null and ANNEX_7_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_7_DATE
    when ANNEX_6_DATE is not null and ANNEX_6_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_6_DATE
    when ANNEX_5_DATE is not null and ANNEX_5_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_5_DATE
    when ANNEX_4_DATE is not null and ANNEX_4_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_4_DATE
    when ANNEX_3_DATE is not null and ANNEX_3_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_3_DATE
    when ANNEX_2_DATE is not null and ANNEX_2_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_2_DATE
    when ANNEX_1_DATE is not null and ANNEX_1_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_1_DATE

    ELSE 'N/A'

是甲骨文数据库，我知道最大日期在附件20里

sql oracle

来源：https://stackoverflow.com/questions/62930080/sql-loop-through-columns-with-name-to-find-max-date

1条答案

按热度按时间

mepcadol1#

首先，你不需要 ANNEX_XX_DATE is not null 自 ANNEX_XX_DATE<>... 如果附件\u xx \u date为空，则不能为真，因此您可以在没有附件\u xx \u date的情况下简化：

,case 
    when ANNEX_20_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_20_DATE
    when ANNEX_19_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_19_DATE
    when ANNEX_18_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_18_DATE
    when ANNEX_17_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_17_DATE
    when ANNEX_16_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_16_DATE
    when ANNEX_15_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_15_DATE
    when ANNEX_14_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_14_DATE
    when ANNEX_13_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_13_DATE
    when ANNEX_12_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_12_DATE
    when ANNEX_11_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_11_DATE
    when ANNEX_10_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_10_DATE
    when ANNEX_9_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_9_DATE
    when ANNEX_8_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_8_DATE
    when ANNEX_7_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_7_DATE
    when ANNEX_6_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_6_DATE
    when ANNEX_5_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_5_DATE
    when ANNEX_4_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_4_DATE
    when ANNEX_3_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_3_DATE
    when ANNEX_2_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_2_DATE
    when ANNEX_1_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_1_DATE

    ELSE 'N/A'

另外，我可能会用coalesce（nullif（…）…）替换它，以使其更简单、更简短：

coalesce(
   nullif(ANNEX_20_DATE, date'1900-01-01'),
   nullif(ANNEX_19_DATE, date'1900-01-01'),
   nullif(ANNEX_18_DATE, date'1900-01-01'),
   nullif(ANNEX_17_DATE, date'1900-01-01'),
   nullif(ANNEX_16_DATE, date'1900-01-01'),
   nullif(ANNEX_15_DATE, date'1900-01-01'),
   nullif(ANNEX_14_DATE, date'1900-01-01'),
   nullif(ANNEX_13_DATE, date'1900-01-01'),
   nullif(ANNEX_12_DATE, date'1900-01-01'),
   nullif(ANNEX_11_DATE, date'1900-01-01'),
   nullif(ANNEX_10_DATE, date'1900-01-01'),
   nullif(ANNEX_9_DATE , date'1900-01-01'),
   nullif(ANNEX_8_DATE , date'1900-01-01'),
   nullif(ANNEX_7_DATE , date'1900-01-01'),
   nullif(ANNEX_6_DATE , date'1900-01-01'),
   nullif(ANNEX_5_DATE , date'1900-01-01'),
   nullif(ANNEX_4_DATE , date'1900-01-01'),
   nullif(ANNEX_3_DATE , date'1900-01-01'),
   nullif(ANNEX_2_DATE , date'1900-01-01'),
   nullif(ANNEX_1_DATE , date'1900-01-01')
)

赞(0）回复(0）举报 2021-07-26

我来回答

sql通过名为的列循环查找最大日期

1条答案

相关问题

热门标签

最新问答