sql - SQL 循环遍历具有名称的列以查找最大日期
问题描述
我正在寻找 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'
它是 oracle 数据库,我知道最大日期在附件 20 中
解决方案
首先,你不需要,ANNEX_XX_DATE is not null
因为ANNEX_XX_DATE<>...
如果 ANNEX_XX_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')
)
推荐阅读
- c# - Active Directory 中 UserPrincipal 对象的 GUID 属性是否唯一且不可欺骗?
- sql - Rails - 我如何查看我的 SQLite 表?
- java - 给玩家体验
- pandas - statsmodels 混合模型需要很长时间
- rx-java2 - 带有 RXJava2 的 Android 房间;发射器的 onNext() 未正确触发
- android-studio - 即使在安装 haxm 后 AVD 也无法正常工作
- node.js - 将 Firebase 添加到电子项目的问题,gRPC 依赖问题
- c# - 无法分配给“arg”,因为它是“foreach 迭代变量”
- vue.js - VUE,在v-for循环中,如何在样式中绑定循环的各个url
- c# - 如何在 C# 中的 TextBox 中显示 ComboBox 选择?