oracle SQL Developer如何筛选日期

5kgi1eie 于 2022-12-11 发布在 Oracle

关注(0)|答案(2)|浏览(148)

和平我有一个表充满了值，我需要过滤每个月的所有日期10这是删除每个人谁是10-01-2022到10-31-2022之间
不是我说的具体日期

create table content(
    cname varchar2(1),
    type varchar2(15),
    episodes number,
    country varchar2(15),
    language varchar2(15),
    releasedate date,
     primary key(cname, type));
    
    insert into content values('A','film', 1, 'China', 'Mandarin', '03/10/2022');
    insert into content values('B','film', 1, 'Taiwan', 'Cantonese', '30/10/2022');
    insert into content values('C','film', 1, 'Singapore', 'Malay', '15/09/2022');
    insert into content values('A','series', 8, 'Korea', 'Korean', '28/09/2022');
    insert into content values('B','series', 10, 'China', 'Mandarin', '03/10/2022');
    insert into content values('C','series', 18, 'Korea', 'Korean', '01/11/2022');
    insert into content values('D','series', 8, 'Korea', 'Korean', '16/09/2022');
    insert into content values('D','documentary', 3, 'China', 'Mandarin', '18/10/2022');
    insert into content values('E','documentary', 6, 'Taiwan', 'Mandarin', '17/10/2022');
    
    
SELECT cname,type,country,episodes,language,releasedate
FROM content
minus
SELECT cname,type,country,episodes,language,releasedate
FROM content
WHERE  content.country ='China'
AND
content.TYPE ='documentary'
AND
content.MONTH(releasedate) =10;

oracle

来源：https://stackoverflow.com/questions/74691027/sql-developer-how-to-filter-date

2条答案

按热度按时间

eivgtgni1#

If is intended to fetch all data of October without checking the year, we can use EXTRACT , see here the documentation

SELECT cname, type, country, episodes, language, releasedate
FROM content
WHERE EXTRACT(MONTH FROM releasedate) = 10;

Otherwise, if only the October 2022 should be considered, we will prefer to use BETWEEN rather than two times EXTRACT for both year and month.

SELECT cname, type, country, episodes, language, releasedate
FROM content
WHERE releasedate BETWEEN 
TO_DATE('2022-10-01','YYYY-MM-DD') AND 
TO_DATE('2022-10-31','YYYY-MM-DD');

If your dates can contain a time, we should add a TRUNC to avoid a date like '10/31/2022 12:22:22' will not be found:

SELECT cname, type, country, episodes, language, releasedate
FROM content
WHERE TRUNC(releasedate) BETWEEN 
TO_DATE('2022-10-01','YYYY-MM-DD') AND 
TO_DATE('2022-10-31','YYYY-MM-DD');

Here we can verify this is working correctly: db<>fiddle

赞(0）回复(0）举报 2022-12-11

ruarlubt2#

Change from:

SELECT ...
MINUS
SELECT ... WHERE X AND Y AND Z

to a single query:

SELECT ...
WHERE NOT X OR NOT Y OR NOT Z

Which for your query would be:

SELECT *
FROM   content
WHERE  country != 'China'
OR     type    != 'documentary'
OR     releasedate <  DATE '2022-10-01'
OR     releasedate >= DATE '2022-11-01';

or, if you can have NULL values:

SELECT *
FROM   content
WHERE  country != 'China'
OR     country IS NULL
OR     type    != 'documentary'
OR     type    IS NULL
OR     releasedate <  DATE '2022-10-01'
OR     releasedate >= DATE '2022-11-01'
OR     releasedate IS NULL;

If you want to always exclude that month then use a mix of AND and OR :

SELECT *
FROM   content
WHERE  (  country != 'China'
       OR type    != 'documentary' )
AND    (  releasedate <  DATE '2022-10-01'
       OR releasedate >= DATE '2022-11-01' );

fiddle

赞(0）回复(0）举报 2022-12-11

我来回答

oracle SQL Developer如何筛选日期

2条答案

相关问题

热门标签

最新问答