mysql - 在 MySQL 中查找 2 个日期之间的差异

Question

我有 2 个不同的日期列:detail_returndate 和detail_duedate。我试图找出两者之间的差异并将其返回为 days_overdue。

这是基本的 SELECT。我尝试过使用 diffday 并减去 2，但我运气不佳。

    select rent_num, vid_num, detail_returndate, detail_duedate
    from   detailrental

如果您需要该表的完整 SQL 代码，请告诉我，但我认为您不需要它

需要明确的是，我需要通过计算 detail_returndate 和 detail_duedate 之间的差异来找出逾期天数。这应该会产生逾期金额，以天为单位

/* Database Systems, 9th Ed., Coronel/MOrris/Rob */
/* Type of SQL : MySQL */

/*Create table price*/
CREATE TABLE price  
(price_code         INTEGER     PRIMARY KEY AUTO_INCREMENT,
 price_description  VARCHAR(20) NOT NULL,
 price_rentfee      DECIMAL(5,2),
 price_dailylatefee DECIMAL(5,2));


/*Insert data into price*/
INSERT INTO price VALUES(1,'Standard',2,1);
INSERT INTO price VALUES(2,'New Release',3.5,3);
INSERT INTO price VALUES(3,'Discount',1.5,1);
INSERT INTO price VALUES(4,'Weekly Special',1,.5);


/*Create table movie*/
CREATE TABLE movie
(movie_num    INTEGER     PRIMARY KEY AUTO_INCREMENT,
 movie_title  VARCHAR(75) NOT NULL,
 movie_year   INTEGER,
 movie_cost   DECIMAL(5,2),
 movie_genre  VARCHAR(50),
 price_code   INTEGER,
 FOREIGN KEY(price_code) REFERENCES price(price_code));

/*Insert data into movie*/
INSERT INTO movie VALUES(1234,'The Cesar Family Christmas',2007,39.95,'FAMILY',2);
INSERT INTO movie VALUES(1235,'Smokey Mountain Wildlife',2004,59.95,'ACTION',1);
INSERT INTO movie VALUES(1236,'Richard Goodhope',2008,59.95,'DRAMA',2);
INSERT INTO movie VALUES(1237,'Beatnik Fever',2007,29.95,'COMEDY',2);
INSERT INTO movie VALUES(1238,'Constant Companion',2008,89.95,'DRAMA',NULL);
INSERT INTO movie VALUES(1239,'Where Hope Dies',1998,25.49,'DRAMA',3);
INSERT INTO movie VALUES(1245,'Time to Burn',2005,45.49,'ACTION',1);
INSERT INTO movie VALUES(1246,'What He Doesn''t Know',2006,58.29,'COMEDY',1);


/*Create table video*/
CREATE TABLE video
(vid_num     INTEGER PRIMARY KEY AUTO_INCREMENT,
 vid_indate  DATE,
 movie_num   INTEGER,
 FOREIGN KEY(movie_num) REFERENCES movie(movie_num));

/*Insert data into video*/
INSERT INTO video VALUES(54321,'2008-06-18',1234);
INSERT INTO video VALUES(54324,'2008-06-18',1234);
INSERT INTO video VALUES(54325,'2008-06-18',1234);
INSERT INTO video VALUES(34341,'2007-01-22',1235);
INSERT INTO video VALUES(34342,'2007-01-22',1235);
INSERT INTO video VALUES(34366,'2009-03-02',1236);
INSERT INTO video VALUES(34367,'2009-03-02',1236);
INSERT INTO video VALUES(34368,'2009-03-02',1236);
INSERT INTO video VALUES(34369,'2009-03-02',1236);
INSERT INTO video VALUES(44392,'2008-10-21',1237);
INSERT INTO video VALUES(44397,'2008-10-21',1237);
INSERT INTO video VALUES(59237,'2009-02-14',1237);
INSERT INTO video VALUES(61388,'2007-01-25',1239);
INSERT INTO video VALUES(61353,'2006-01-28',1245);
INSERT INTO video VALUES(61354,'2006-01-28',1245);
INSERT INTO video VALUES(61367,'2008-07-30',1246);
INSERT INTO video VALUES(61369,'2008-07-30',1246);


/*Create table membership*/
CREATE TABLE membership
(mem_num     INTEGER     PRIMARY KEY AUTO_INCREMENT,
 mem_fname   VARCHAR(30) NOT NULL,
 mem_lname   VARCHAR(30) NOT NULL,
 mem_street  VARCHAR(120),
 mem_city    VARCHAR(50),
 mem_state   VARCHAR(2),
 mem_zip     VARCHAR(5),
 mem_balance DECIMAL(10,2));

/*Insert data into membership*/
INSERT INTO membership VALUES(102,'Tami','Dawson','2632 Takli Circle','Norene','TN','37136',11);
INSERT INTO membership VALUES(103,'Curt','Knight','4025 Cornell Court','Flatgap','KY','41219',6);
INSERT INTO membership VALUES(104,'Jamal','Melendez','788 East 145th Avenue','Quebeck','TN','38579',0);
INSERT INTO membership VALUES(105,'Iva','Mcclain','6045 Musket Ball Circle','Summit','KY','42783',15);
INSERT INTO membership VALUES(106,'Miranda','Parks','4469 Maxwell Place','Germantown','TN','38183',0);
INSERT INTO membership VALUES(107,'Rosario','Elliott','7578 Danner Avenue','Columbia','TN','38402',5);
INSERT INTO membership VALUES(108,'Mattie','Guy','4390 Evergreen Street','Lily','KY','40740',0);
INSERT INTO membership VALUES(109,'Clint','Ochoa','1711 Elm Street','Greenville','TN','37745',10);
INSERT INTO membership VALUES(110,'Lewis','Rosales','4524 Southwind Circle','Counce','TN','38326',0);
INSERT INTO membership VALUES(111,'Stacy','Mann','2789 East Cook Avenue','Murfreesboro','TN','37132',8);
INSERT INTO membership VALUES(112,'Luis','Trujillo','7267 Melvin Avenue','Heiskell','TN','37754',3);
INSERT INTO membership VALUES(113,'Minnie','Gonzales','6430 Vasili Drive','Williston','TN','38076',0);


/*Create table rental*/
CREATE TABLE rental
(rent_num  INTEGER PRIMARY KEY AUTO_INCREMENT,
 rent_date DATE,
 mem_num   INTEGER,
 FOREIGN KEY(mem_num) REFERENCES membership(mem_num));

/*Insert data into rental*/
INSERT INTO rental VALUES(1001,'2009-03-01',103);
INSERT INTO rental VALUES(1002,'2009-03-01',105);
INSERT INTO rental VALUES(1003,'2009-03-02',102);
INSERT INTO rental VALUES(1004,'2009-03-02',110);
INSERT INTO rental VALUES(1005,'2009-03-02',111);
INSERT INTO rental VALUES(1006,'2009-03-02',107);
INSERT INTO rental VALUES(1007,'2009-03-02',104);
INSERT INTO rental VALUES(1008,'2009-03-03',105);
INSERT INTO rental VALUES(1009,'2009-03-03',111);


/*Create table detailrental*/
CREATE TABLE detailrental
(rent_num            INTEGER,
 vid_num             INTEGER,
 detail_fee          DECIMAL(5,2),
 detail_duedate      DATE,
 detail_returndate   DATE,
 detail_dailylatefee DECIMAL(5,2),
 PRIMARY KEY(rent_num, vid_num),
 FOREIGN KEY(rent_num) REFERENCES rental(rent_num),
 FOREIGN KEY(vid_num)  REFERENCES video(vid_num));

/*Insert data into dailyrental*/
INSERT INTO detailrental VALUES(1001,34342,2,'2009-03-04','2009-03-02',1);
INSERT INTO detailrental VALUES(1001,61353,2,'2009-03-04','2009-03-03',1);
INSERT INTO detailrental VALUES(1002,59237,3.5,'2009-03-04','2009-03-04',3);
INSERT INTO detailrental VALUES(1003,54325,3.5,'2009-03-04','2009-03-09',3);
INSERT INTO detailrental VALUES(1003,61369,2,'2009-03-06','2009-03-09',1);
INSERT INTO detailrental VALUES(1003,61388,0,'2009-03-06','2009-03-09',1);
INSERT INTO detailrental VALUES(1004,44392,3.5,'2009-03-05','2009-03-07',3);
INSERT INTO detailrental VALUES(1004,34367,3.5,'2009-03-05','2009-03-07',3);
INSERT INTO detailrental VALUES(1004,34341,2,'2009-03-07','2009-03-07',1);
INSERT INTO detailrental VALUES(1005,34342,2,'2009-03-07','2009-03-05',1);
INSERT INTO detailrental VALUES(1005,44397,3.5,'2009-03-05','2009-03-05',3);
INSERT INTO detailrental VALUES(1006,34366,3.5,'2009-03-05','2009-03-04',3);
INSERT INTO detailrental VALUES(1006,61367,2,'2009-03-07',NULL,1);
INSERT INTO detailrental VALUES(1007,34368,3.5,'2009-03-05',NULL,3);
INSERT INTO detailrental VALUES(1008,34369,3.5,'2009-03-05','2009-03-05',3);
INSERT INTO detailrental VALUES(1009,54324,3.5,'2009-03-05',NULL,3);
INSERT INTO detailrental VALUES(1001,34366,3.5,'2009-03-04','2009-03-02',3);

Answer 1

SELECT  rent_num, 
        vid_num, 
        detail_returndate, 
        detail_duedate, 
        DATEDIFF( detail_duedate, detail_returndate) as days_overdue    
FROM detailrental