查找日期之间的周数Oracle SQL Developer

Question

如果我有两个表：

成员：

CREATE TABLE members
(member_id number,
lastname varchar2(10),
firstname varchar2(10),
license# number(9),
ST char(2),
credit_card number(12));
INSERT INTO members VALUES(10, 'Tangier', 'Tim', 111111111, 'VA', 123456789111);
INSERT INTO members VALUES(12, 'Maulder', 'Fox', 333333333, 'FL', 333333333333);
INSERT INTO members VALUES(13, 'Wild', 'Coyote', 444444444, 'VA', 444444444444);
INSERT INTO members VALUES(14, 'Joan', 'Casteel', 555555555, 'FL', 555555555555);

和租金：

CREATE TABLE rental
(rental_id number(2),
rent_date date,
return_date date,
pay_method varchar2(10),
movie_id number,
member_id number);
INSERT INTO rental VALUES(1, '18-SEP-09', '1-OCT-09', 'Credit', 11, 10);
INSERT INTO rental VALUES(2, '18-SEP-09', '3-OCT-09', 'Credit', 8, 10);
INSERT INTO rental VALUES(3, '11-OCT-09', '19-OCT-09', 'Check', 6, 12);
INSERT INTO rental VALUES(4, '28-OCT-09', '31-OCT-09', 'Debit', 3, 13);
INSERT INTO rental VALUES(5, '28-OCT-09', '5-NOV-09', 'Debit', 5, 13);
INSERT INTO rental VALUES(6, '29-OCT-09', '11-NOV-09', 'Debit', 11, 13);
INSERT INTO rental VALUES(7, '1-NOV-09', '11-NOV-09', 'Credit', 10, 14);
INSERT INTO rental VALUES(8, '2-NOV-09', '10-NOV-09', 'Credit', 12, 14);
INSERT INTO rental VALUES(9, '5-NOV-09', NULL, 'Cash', 4, 12);
INSERT INTO rental VALUES(10, '8-NOV-09', '18-NOV-09', 'Cash', 12, 12);
INSERT INTO rental VALUES(11, '14-NOV-09', NULL, 'Cash', 3, 12);
INSERT INTO rental VALUES(12, '18-NOV-09', NULL, 'Debit', 11, 13);

如何显示租赁日期和归还日期之间的周数？如果为NULL，则在名为"delay_weeks"的新列中将返回日期设置为"2009年12月1日"，该列应四舍五入为整数。然后还有一个名为"Status"的新列，显示每个事务是否已返回。

这是一道家庭作业题，但我似乎做不对。我们应该只使用一个SQL语句。

到目前为止，我已经尝试了以下内容，我认为这几乎是正确的，但我得到了错误："；MEM"RENTAL_ID"：无效标识符。

SELECT mem.member_id AS member_id, 
ren.rental_id AS rental_transaction_id,
case
when ren.return_date IS NULL
THEN ROUND(TO_DATE('01-dec-2009','dd-mm-yyyy')-TO_DATE(ren.rent_date, 'dd-mm-yyyy')) 
ELSE ROUND(TO_DATE(ren.return_date, 'dd-mm-yyyy')-TO_DATE(ren.rent_date, 'dd-mm-yyyy'))
END AS delay_weeks, 
CASE
WHEN ren.return_date IS NULL
THEN 'not return'
ELSE 'return'
END AS status
FROM   members mem
JOIN rental ren ON mem.member_id = ren.member_id
order BY member_id;

如有任何指导，我们将不胜感激。如果需要澄清，请告诉我。TIA。

Answer 1

您可以减去日期，用'Dec 1, 2009'日期代替NULLreturn_dates，然后除以7：

select m.member_id, r.rental_id, floor(((case when r.return_date is null then to_date('01-DEC-09') else r.return_date end) - r.rent_date)/7) weeks
from rental r join members m on r.member_id = m.member_id;

Answer 2

不要对已经是日期的值调用TO_DATE；如果你修复了它，那么你就会得到代码：

case
when ren.return_date IS NULL
THEN ROUND(TO_DATE('01-dec-09','dd-mm-yyyy')-ren.rent_date) 
ELSE ROUND(ren.return_date-ren.rent_date)
END AS delay_weeks, 

如果运行该程序，则会突出显示下一个问题，即TO_DATE('01-dec-09','dd-mm-yyyy')预计年份为4位数，但您只提供了2位数，因此您将获得公元9年，而不是2009年(以及大约-730461天的差异(。最好的解决方案是使用日期文字(而不是尝试从字符串转换(：

case
when ren.return_date IS NULL
THEN ROUND(DATE '2009-12-01'-ren.rent_date) 
ELSE ROUND(ren.return_date-ren.rent_date)
END AS delay_weeks,

然后可以使用COALESCE函数而不是CASE表达式来简化它：

ROUND(COALESCE(ren.return_date, DATE '2009-12-01') - ren.rent_date)
AS delay_weeks,

然后，当你减去两个日期时，你会得到以天(而不是周(为单位的差异，所以你需要除以7:

SELECT mem.member_id AS member_id, 
ren.rental_id AS rental_transaction_id,
ROUND((COALESCE(ren.return_date, DATE '2009-12-01') - ren.rent_date)/7)
AS delay_weeks, 
CASE
WHEN ren.return_date IS NULL
THEN 'not return'
ELSE 'return'
END AS status
FROM   members mem
JOIN rental ren ON mem.member_id = ren.member_id
order BY member_id;

如果您只想计算完整的周数，那么您需要使用FLOOR。如果要计算零件周数，请使用CEIL；如果要计算到最近的周数，则使用ROUND。我假设你想要FLOOR或CEIL而不是ROUND，但这个要求的问题还不清楚。

db<gt；小提琴这里