sql 列出截至 2005 年 1 月 1 日的所有员工、他们的雇用日期以及每个人为公司工作的天数

Question

CREATE TABLE Employee (  
    Employee_ID     INT     NOT NULL,  
    First_Name      VARCHAR(64) NOT NULL,  
    Last_Name       VARCHAR(64) NOT NULL,  
    Dept_Code       VARCHAR(6)  NOT NULL,  
    Hire_Date       DATE        NULL,  
    Credit_Limit    NUMERIC(8,2)    NULL,  
    Phone_Ext       INT     NULL,  
    Manager_ID      INT     NULL);  

INSERT INTO Employee VALUES (201,'Susan','Brown','Exe','01-Jun-1998',30.00,3484,NULL);  
INSERT INTO Employee VALUES (202,'Jim','Kern','Sal','16-Aug-1999',25.00,8722,201);  
INSERT INTO Employee VALUES(203,'Martha','Woods','Shp','02-Feb-2004',25.00,7591,201);  
INSERT INTO Employee VALUES(204,'Ellen','Owens','Sal','01-Jul-2003',15.00,6830,202);  
INSERT INTO Employee VALUES(205,'Henry','Perkins','Sal','01-Mar-2000',25.00,5286,202);  
INSERT INTO Employee VALUES(206,'Carol','Rose','Act','','',NULL,NULL);  
INSERT INTO Employee VALUES(207,'Dan','Smith','Shp','01-Dec-2004',25.00,2259,203);  
INSERT INTO Employee VALUES(208,'Fred','Campbell','Shp','01-Apr-2003',30.00,1752,203);  
INSERT INTO Employee VALUES(209,'Paula','Jacobs','Mkt','17-Mar-1999',15.00,3357,201);  
INSERT INTO Employee VALUES(210,'Nancy','Hoffman','Sal','16-Feb-2004',25.00,2974,203);  

Answer 1

您可以使用两个日期之间的-运算符来获取以天为单位的差异，然后将其trunc为整数：

SELECT first_name,
       last_name,
       hire_date,
       TRUNC(hire_date - TO_DATE('01-01-2005', 'dd-mm-yyyy'))
FROM   Assignment1_Employee