Oracle-运行时和显示基于插入年份的数据(不重复) [英] Oracle - runtime and show data based on inserted year ( not duplicate )

问题描述

设施表

FACILITYNO    FACILITYNAME
'FACNO01'        Toilet
'FACNO02'       Staff Office
'FACNO03'     Principle Office
'FACNO04'       Science Lab
'FACNO05'       Math Lab
'FACNO06'       Computer Lab
'FACNO07'        Biology Lab
'FACNO08'       Chemical Lab

BookingTable

BookingTable

FACILITYNO  bookingID       userID     Timebooked  
'FACNO01'       1             0003    10-May-2016       
'FACNO04'       2             0001    10-May-2018    
'FACNO05'       3             0001    10-Apr-2017
'FACNO01'       4             0001    10-Apr-2017
'FACNO02'       5             0003    10-Jan-2011
'FACNO04'       6             0006    10-Apr-2018
'FACNO06'       7             0003    10-Apr-2016
'FACNO07'       8             0006    10-Apr-2015
'FACNO08'       9             0001    10-Apr-2017

预期结果(在插入& n-> 2016年之后)

Expected Result (after insert &n -> Year 2016)

FACILITYNAME  count_times  MONTH
Toilet          1            5
Computer Lab    1            4

---

在此代码I上，当使用运行时(& n)并输入2016'时，它将显示我在上面提供的预期表.

---

On this code I , when runtime is used (&n) and I entered 2016' it will show the expected table that I have provided above.

下面是我的代码(但最终出错/重复，甚至出现的数据都不是我插入的年份(2016年))

Below is my code ( but ended up error/duplicate , appearing the data even is not the year I have inserted (2016) )

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE TO_CHAR(b.Timebooked , 'YYYY') = &n
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ))

推荐答案

正确的格式(在需要时使用quotes和to_date)似乎没有问题:

No problem seems with proper formatting (using quotes & to_date whenever needed) :

create table FacilityTable (FACILITYNO varchar2(30), FACILITYNAME varchar2(30));
insert into FacilityTable values('FACNO01','Toilet');
insert into FacilityTable values('FACNO02','Staff Office');
insert into FacilityTable values('FACNO03','Principle Office');
insert into FacilityTable values('FACNO04', 'Science Lab');
insert into FacilityTable values('FACNO05','Math Lab');
insert into FacilityTable values('FACNO06','Computer Lab');
insert into FacilityTable values('FACNO07','Biology Lab');
insert into FacilityTable values('FACNO08','Chemical Lab');


create table BookingTable (FACILITYNO varchar2(30),bookingID int, userID varchar2(30), Timebooked date);
insert into BookingTable values('FACNO01',1,'0003',to_date('10-May-2016','dd-Mon-yyyy'));       
insert into BookingTable values('FACNO04',2,'0001',to_date('10-May-2018','dd-Mon-yyyy'));    
insert into BookingTable values('FACNO05',3,'0001',to_date('10-Apr-2017','dd-Mon-yyyy'));
insert into BookingTable values('FACNO01',4,'0001',to_date('10-Apr-2017','dd-Mon-yyyy'));
insert into BookingTable values('FACNO02',5,'0003',to_date('10-Jan-2011','dd-Mon-yyyy'));
insert into BookingTable values('FACNO04',6,'0006',to_date('10-Apr-2018','dd-Mon-yyyy'));
insert into BookingTable values('FACNO06',7,'0003',to_date('10-Apr-2016','dd-Mon-yyyy'));
insert into BookingTable values('FACNO07',8,'0006',to_date('10-Apr-2015','dd-Mon-yyyy'));
insert into BookingTable values('FACNO08',9,'0001',to_date('10-Apr-2017','dd-Mon-yyyy'));

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE TO_CHAR(b.Timebooked , 'YYYY') = &n
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ));

FACILITYNAME    COUNT_TIMES MONTHS
Computer Lab           1    4
Toilet                 1    5

演示(2016年直接替换为& n)

这篇关于Oracle-运行时和显示基于插入年份的数据(不重复)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！