如何在Oracle SQL中使用CASE语句检查特定日期。

huangapple go评论56阅读模式
英文:

How to check for a specific date with a CASE statement in SQL in Oracle SQL

问题

我正在尝试编写一个OracleSQL语句,根据特定字段(Column3)中的日期是否为周一,来选择昨天或过去3天的结果。

SELECT Column1, Column2, Column3 FROM Table
WHERE CASE
       WHEN To_Char(Column2, 'Day') = 'Monday'
          THEN Column3 >= (SYSDATE - Interval '3' Day)
       ELSE Column3 >= (SYSDATE - Interval '1' Day)
    END
Order by Column3 DESC;

或者可以使用以下代码:

SELECT Column1, Column2, Column3 FROM Table
WHERE Column3 >= 
    (CASE
       WHEN To_Char(Current_Date, 'D') = 1
          THEN To_Char(Current_Date - 3)
       ELSE To_Char(Current_Date - 1)
    END)
Order by Column3 DESC;

这些代码是为了从包含成千上万条记录的表中仅显示昨天或周末的最新结果而设计的。代码会读取Column3中的输入日期,然后根据条件显示最近的3天或昨天的结果。

已解决:最终的可行代码如下:

SELECT Column1, Column2, Column3 FROM The_Table
WHERE Column3 >= 
    CASE
        WHEN to_char(Current_Date, 'fmDay', 'nls_date_language = English') = 'Monday'
            THEN TRUNC(SYSDATE) - INTERVAL '3' DAY
        ELSE TRUNC(SYSDATE) - INTERVAL '1' DAY
    END
ORDER BY Column3 DESC;
英文:

I am trying to write a statement in OracleSQL which will select the results from either yesterday or the last 3 days according to whether the date in a specific field (column3) is monday.

SELECT Column1, Column2, Column3 FROM Table
WHERE CASE
       WHEN To_Char(Column2, 'Day') = 'Monday'
          THEN Column3 >= (SYSDATE - Interval "3" Day)
       ELSE Column3 >= (SYSDATE - Interval "1" Day)
    END
Order by Column3 DESC; 

alternatively

SELECT Column1, Column2, Column3 FROM Table
WHERE Column3 >= 
    (CASE
       WHEN To_Char(Current_Date 'D') = 1
          THEN To_Char(Current_Date - 3)
       ELSE To_Char(Current_Date - 1)
    END
Order by Column3 DESC; 

For clarity, I have a table with many thousands of entries and I need to display only recent results from yesterday or from the weekend. The snippet is meant to read the date of entry from column3 and then either show the last 3 days or yesterday accordingly.

SOLVED: The ultimate code which worked--

SELECT Column1, Column2, Column3 FROM The_Table
WHERE Column3 >= 
    CASE
        WHEN to_char(Current_Date, 'fmDay', 'nls_date_language = English') = 'Monday'
            THEN TRUNC(SYSDATE) - INTERVAL '3' DAY
        ELSE TRUNC(SYSDATE) - INTERVAL '1' DAY
    END
ORDER BY Column3 DESC;

答案1

得分: 1

Here is the translated content:

像这样吗?

SELECT column1, column2, column3
FROM your_table
WHERE column3 >=
   CASE
      WHEN TO_CHAR(column2, 'fmDay', 'nls_date_language = english') = 'Monday'
      THEN
         TRUNC(SYSDATE) - INTERVAL '3' DAY
      ELSE
         TRUNC(SYSDATE) - INTERVAL '1' DAY
   END
ORDER BY column3 DESC;

-  `TO_CHAR` 中,包括 `fm`(否则您将获得右侧填充了空格的值,最多为最大日期名称长度),并添加语言标识符

SQL> select 'x' || to_char(sysdate + 1, 'Day') || 'x' result from dual;

RESULT
-------------
xUtorak     x

SQL> select 'x' || to_char(sysdate + 1, 'fmDay', 'nls_date_language = english') || 'x' result from dual;

RESULT
-----------
xTuesdayx

- 截断 `sysdate` 以将其设置为*午夜*;否则,您将获得时间组件。我猜您不需要它

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate - interval '3' day result1,
  2   trunc(sysdate)- interval '3' day result2
  3  from dual;

RESULT1             RESULT2
------------------- -------------------
12.05.2023 09:45:46 12.05.2023 00:00:00

(Note: I've provided the translated SQL code as requested, and I've omitted the non-translation parts.)

英文:

Something like this?

  SELECT column1, column2, column3
    FROM your_table
   WHERE column3 >=
         CASE
            WHEN TO_CHAR (column2, 'fmDay', 'nls_date_language = english') = 'Monday'
            THEN
               TRUNC(SYSDATE) - INTERVAL '3' DAY
            ELSE
               TRUNC(SYSDATE) - INTERVAL '1' DAY
         END
ORDER BY column3 DESC;
  • in TO_CHAR, include fm (otherwise you'll get value which is right-padded with spaces, up to the max day name length) and add language identifier

    SQL> select 'x' || to_char(sysdate + 1, 'Day') || 'x' result from dual;
    
    RESULT
    -------------
    xUtorak     x
    
    SQL> select 'x' || to_char(sysdate + 1, 'fmDay', 'nls_date_language = english') || 'x' result from dual;
    
    RESULT
    -----------
    xTuesdayx
    
  • truncate sysdate to set it to midnight; otherwise, you'll get time component as well. I guess you don't need it

    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select sysdate - interval '3' day result1,
      2   trunc(sysdate)- interval '3' day result2
      3  from dual;
    
    RESULT1             RESULT2
    ------------------- -------------------
    12.05.2023 09:45:46 12.05.2023 00:00:00
    

答案2

得分: 0

根据此答案,检查星期几的一种与区域设置无关的方式是使用 ISO 周:

SELECT
   Column1,
   Column2,
   Column3
FROM Table
WHERE
   CASE
      WHEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') = 0
      THEN Column3 >= (SYSDATE - Interval "3" Day)
      ELSE Column3 >= (SYSDATE - Interval "1" Day)
   END
ORDER BY Column3 DESC; 
英文:

As per this answer, a locale independent way of checking for the day of week, is using the ISO week:

SELECT
   Column1,
   Column2,
   Column3
FROM Table
WHERE
   CASE
      WHEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') = 0
      THEN Column3 >= (SYSDATE - Interval "3" Day)
      ELSE Column3 >= (SYSDATE - Interval "1" Day)
   END
ORDER BY Column3 DESC; 

huangapple
  • 本文由 发表于 2023年5月15日 15:20:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/76251712.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定