我需要創建一個請求，將日期進行比較。像這樣的請求：
SELECT f.Name,
TO_CHAR(s.Film_Start_Time, 'hh24:mi') 。
TO_CHAR(f.Duration, 'hh24:mi')。
f2.姓名。
TO_CHAR(s2.Film_Start_Time, 'hh24:mi')。
TO_CHAR(f2.Duration, 'hh24:mi')
FROM Schedule s
JOIN Films f ON s.Film_Name = f.Name
JOIN Schedule s2 ON s2.Film_Start_Time >/span> s.Film_Start_Time
and s2.Film_Start_Time < s.Film_Start_Time NUMTODSINTERVAL(f.Duration, 'MINUTE'/span>)
JOIN Films f2 ON s2.Film_Name = f2.Name
ORDER BY s.Film_Start_Time ASC;
但是當f.Duration的值超過90分鐘時，它被粉碎，錯誤為ORA-01481：無效的數字格式模型 開始時間資料
uj5u.com熱心網友回復：
你的duration列應該是一個NUMBER而不是DATE：
CREATE TABLE films (
name VARCHAR2(20 CHAR) NOT NULL,
duration NUMBER(4,0) NOT NULL,
category VARCHAR2(20 CHAR) NOT NULL。
company VARCHAR2(20 CHAR) 。
country VARCHAR2(20 CHAR) 。
發布年份 NUMBER
);
然后你可以使用：
SELECT f.Name,
TO_CHAR(s.Film_Start_Time, 'hh24:mi') AS f1_start_time,
TO_CHAR(s.film_start_time NUMTODSINTERVAL(f.Duration, 'MINUTE'/span>), 'hh24:mi')
AS f1_end_time。
f2.名稱。
TO_CHAR(s2.Film_Start_Time, 'hh24:mi') AS f2_start_time,
TO_CHAR(s2.Film_start_time NUMTODSINTERVAL(f2.Duration, 'MINUTE'/span>), 'hh24:mi')
AS f2_end_time
FROM Schedule s
JOIN films f ON s.Film_Name = f.Name
JOIN Schedule s2 ON s2.Film_Start_Time >/span> s.Film_Start_Time
and s2.Film_Start_Time < s.Film_Start_Time NUMTODSINTERVAL(f.Duration, 'MINUTE'/span>)
JOIN Films f2 ON s2.Film_Name = f2.Name
ORDER BY s.Film_Start_Time ASC;
db<>fiddle here
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/334010.html
標籤：