和平我有一个表充满了值,我需要过滤每个月的所有日期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;
2条答案
按热度按时间eivgtgni1#
If is intended to fetch all data of October without checking the year, we can use
EXTRACT
, see here the documentationOtherwise, if only the October 2022 should be considered, we will prefer to use
BETWEEN
rather than two timesEXTRACT
for both year and month.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:Here we can verify this is working correctly: db<>fiddle
ruarlubt2#
Change from:
to a single query:
Which for your query would be:
or, if you can have
NULL
values:If you want to always exclude that month then use a mix of
AND
andOR
:fiddle