英文:
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
, includefm
(otherwise you'll get value which is right-padded with spaces, up to the max day name length) and add language identifierSQL> 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 itSQL> 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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论