我正在研究一些自聯接示例，并且在以下示例中繪制了空白。它是以下鏈接中的最后一個示例Self-Join Example
SELECT
e1.hire_date,
(e1.first_name || ' ' || e1.last_name) employee1,
(e2.first_name || ' ' || e2.last_name) employee2
FROM
employees e1
INNER JOIN employees e2 ON
e1.employee_id > e2.employee_id
AND e1.hire_date = e2.hire_date
ORDER BY
e1.hire_date DESC,
employee1,
employee2;
我不明白為什么我需要檢查第一個員工實體中的 Employee_id 是否大于第二個員工實體中的員工 ID。
如果有人可以為此提供一個很好的解釋，將不勝感激。
所需的創建/插入腳本。
CREATE TABLE employees
(
employee_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 108
PRIMARY KEY,
first_name VARCHAR( 255 ) NOT NULL,
last_name VARCHAR( 255 ) NOT NULL,
email VARCHAR( 255 ) NOT NULL,
phone VARCHAR( 50 ) NOT NULL ,
hire_date DATE NOT NULL ,
manager_id NUMBER( 12, 0 ) , -- fk
job_title VARCHAR( 255 ) NOT NULL,
CONSTRAINT fk_employees_manager
FOREIGN KEY( manager_id )
REFERENCES employees( employee_id )
ON DELETE CASCADE
);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (107,'Summer','Payne','[email protected]','515.123.8181',to_date('07-JUN-16','DD-MON-RR'),106,'Public Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (106,'Rose','Stephens','[email protected]','515.123.8080',to_date('07-JUN-16','DD-MON-RR'),2,'Accounting Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (101,'Annabelle','Dunn','[email protected]','515.123.4444',to_date('17-SEP-16','DD-MON-RR'),2,'Administration Assistant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (1,'Tommy','Bailey','[email protected]','515.123.4567',to_date('17-JUN-16','DD-MON-RR'),null,'President');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (3,'Blake','Cooper','[email protected]','515.123.4569',to_date('13-JAN-16','DD-MON-RR'),1,'Administration Vice President');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (2,'Jude','Rivera','[email protected]','515.123.4568',to_date('21-SEP-16','DD-MON-RR'),1,'Administration Vice President');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (11,'Tyler','Ramirez','[email protected]','515.124.4269',to_date('28-SEP-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (10,'Ryan','Gray','[email protected]','515.124.4169',to_date('16-AUG-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (14,'Elliot','Brooks','[email protected]','515.124.4567',to_date('07-DEC-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (12,'Elliott','James','[email protected]','515.124.4369',to_date('30-SEP-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (13,'Albert','Watson','[email protected]','515.124.4469',to_date('07-MAR-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (9,'Mohammad','Peterson','[email protected]','515.124.4569',to_date('17-AUG-16','DD-MON-RR'),2,'Finance Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (104,'Harper','Spencer','[email protected]','515.123.7777',to_date('07-JUN-16','DD-MON-RR'),2,'Human Resources Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (4,'Louie','Richardson','[email protected]','590.423.4567',to_date('03-JAN-16','DD-MON-RR'),3,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (5,'Nathan','Cox','[email protected]','590.423.4568',to_date('21-MAY-16','DD-MON-RR'),4,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (8,'Bobby','Torres','[email protected]','590.423.5567',to_date('07-FEB-16','DD-MON-RR'),4,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (7,'Charles','Ward','[email protected]','590.423.4560',to_date('05-FEB-16','DD-MON-RR'),4,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (6,'Gabriel','Howard','[email protected]','590.423.4569',to_date('25-JUN-16','DD-MON-RR'),4,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (102,'Emma','Perkins','[email protected]','515.123.5555',to_date('17-FEB-16','DD-MON-RR'),1,'Marketing Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (103,'Amelie','Hudson','[email protected]','603.123.6666',to_date('17-AUG-16','DD-MON-RR'),102,'Marketing Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (105,'Gracie','Gardner','[email protected]','515.123.8888',to_date('07-JUN-16','DD-MON-RR'),2,'Public Relations Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (17,'Frederick','Price','[email protected]','515.127.4563',to_date('24-DEC-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (16,'Alex','Sanders','[email protected]','515.127.4562',to_date('18-MAY-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (18,'Ollie','Bennett','[email protected]','515.127.4564',to_date('24-JUL-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (19,'Louis','Wood','[email protected]','515.127.4565',to_date('15-NOV-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (20,'Dexter','Barnes','[email protected]','515.127.4566',to_date('10-AUG-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (15,'Rory','Kelly','[email protected]','515.127.4561',to_date('07-DEC-16','DD-MON-RR'),1,'Purchasing Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (49,'Isabella','Cole','[email protected]','011.44.1344.619268',to_date('15-OCT-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (48,'Jessica','Woods','[email protected]','011.44.1344.429278',to_date('10-MAR-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (47,'Ella','Wallace','[email protected]','011.44.1344.467268',to_date('05-JAN-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (46,'Ava','Sullivan','[email protected]','011.44.1344.429268',to_date('01-OCT-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (50,'Mia','West','[email protected]','011.44.1344.429018',to_date('29-JAN-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (56,'Evie','Harrison','[email protected]','011.44.1344.486508',to_date('23-NOV-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (57,'Scarlett','Gibson','[email protected]','011.44.1345.429268',to_date('30-JAN-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (58,'Ruby','Mcdonald','[email protected]','011.44.1345.929268',to_date('04-MAR-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (59,'Chloe','Cruz','[email protected]','011.44.1345.829268',to_date('01-AUG-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (60,'Isabelle','Marshall','[email protected]','011.44.1345.729268',to_date('10-MAR-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (61,'Daisy','Ortiz','[email protected]','011.44.1345.629268',to_date('15-DEC-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (62,'Freya','Gomez','[email protected]','011.44.1345.529268',to_date('03-NOV-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (80,'Elizabeth','Dixon','[email protected]','011.44.1644.429262',to_date('04-JAN-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (64,'Florence','Freeman','[email protected]','011.44.1346.229268',to_date('19-MAR-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (65,'Alice','Wells','[email protected]','011.44.1346.329268',to_date('24-JAN-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (66,'Charlotte','Webb','[email protected]','011.44.1346.529268',to_date('23-FEB-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (67,'Sienna','Simpson','[email protected]','011.44.1346.629268',to_date('24-MAR-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (68,'Matilda','Stevens','[email protected]','011.44.1346.729268',to_date('21-APR-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (69,'Evelyn','Tucker','[email protected]','011.44.1343.929268',to_date('11-MAR-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (70,'Eva','Porter','[email protected]','011.44.1343.829268',to_date('23-MAR-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (71,'Millie','Hunter','[email protected]','011.44.1343.729268',to_date('24-JAN-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (72,'Sofia','Hicks','[email protected]','011.44.1343.629268',to_date('23-FEB-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (73,'Lucy','Crawford','[email protected]','011.44.1343.529268',to_date('24-MAR-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (74,'Elsie','Henry','[email protected]','011.44.1343.329268',to_date('21-APR-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (75,'Imogen','Boyd','[email protected]','011.44.1644.429267',to_date('11-MAY-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (76,'Layla','Mason','[email protected]','011.44.1644.429266',to_date('19-MAR-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (77,'Rosie','Morales','[email protected]','011.44.1644.429265',to_date('24-MAR-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (78,'Maya','Kennedy','[email protected]','011.44.1644.429264',to_date('23-APR-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (79,'Esme','Warren','[email protected]','011.44.1644.429263',to_date('24-MAY-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (55,'Grace','Ellis','[email protected]','011.44.1344.987668',to_date('09-DEC-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (54,'Lily','Fisher','[email protected]','011.44.1344.498718',to_date('30-MAR-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (53,'Sophia','Reynolds','[email protected]','011.44.1344.478968',to_date('20-AUG-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (52,'Sophie','Owens','[email protected]','011.44.1344.345268',to_date('24-MAR-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (51,'Poppy','Jordan','[email protected]','011.44.1344.129268',to_date('30-JAN-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (63,'Phoebe','Murray','[email protected]','011.44.1346.129268',to_date('11-NOV-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (85,'Holly','Shaw','[email protected]','650.509.1876',to_date('27-JAN-16','DD-MON-RR'),22,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (86,'Emilia','Holmes','[email protected]','650.509.2876',to_date('20-FEB-16','DD-MON-RR'),22,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (87,'Molly','Rice','[email protected]','650.509.3876',to_date('24-JUN-16','DD-MON-RR'),22,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (88,'Ellie','Robertson','[email protected]','650.509.4876',to_date('07-FEB-16','DD-MON-RR'),22,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (89,'Jasmine','Hunt','[email protected]','650.505.1876',to_date('14-JUN-16','DD-MON-RR'),23,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (90,'Eliza','Black','[email protected]','650.505.2876',to_date('13-AUG-16','DD-MON-RR'),23,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (91,'Lilly','Daniels','[email protected]','650.505.3876',to_date('11-JUL-16','DD-MON-RR'),23,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (92,'Abigail','Palmer','[email protected]','650.505.4876',to_date('19-DEC-16','DD-MON-RR'),23,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (93,'Georgia','Mills','[email protected]','650.501.1876',to_date('04-FEB-16','DD-MON-RR'),24,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (94,'Maisie','Nichols','[email protected]','650.501.2876',to_date('03-MAR-16','DD-MON-RR'),24,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (95,'Eleanor','Grant','[email protected]','650.501.3876',to_date('01-JUL-16','DD-MON-RR'),24,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (96,'Hannah','Knight','[email protected]','650.501.4876',to_date('17-MAR-16','DD-MON-RR'),24,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (97,'Harriet','Ferguson','[email protected]','650.507.9811',to_date('24-APR-16','DD-MON-RR'),25,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (98,'Amber','Rose','[email protected]','650.507.9822',to_date('23-MAY-16','DD-MON-RR'),25,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (99,'Bella','Stone','[email protected]','650.507.9833',to_date('21-JUN-16','DD-MON-RR'),25,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (100,'Thea','Hawkins','[email protected]','650.507.9844',to_date('13-JAN-16','DD-MON-RR'),25,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (81,'Lola','Ramos','[email protected]','650.507.9876',to_date('24-JAN-16','DD-MON-RR'),21,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (82,'Willow','Reyes','[email protected]','650.507.9877',to_date('23-FEB-16','DD-MON-RR'),21,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (83,'Ivy','Burns','[email protected]','650.507.9878',to_date('21-JUN-16','DD-MON-RR'),21,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (84,'Erin','Gordon','[email protected]','650.507.9879',to_date('03-FEB-16','DD-MON-RR'),21,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (33,'Reggie','Simmons','[email protected]','650.124.8234',to_date('10-APR-16','DD-MON-RR'),22,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (44,'Emily','Hamilton','[email protected]','650.121.2874',to_date('15-MAR-16','DD-MON-RR'),25,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (43,'Olivia','Ford','[email protected]','650.121.2994',to_date('29-JAN-16','DD-MON-RR'),25,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (42,'Amelia','Myers','[email protected]','650.121.8009',to_date('17-OCT-16','DD-MON-RR'),25,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (41,'Connor','Hayes','[email protected]','650.121.1834',to_date('06-APR-16','DD-MON-RR'),24,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (26,'Leon','Powell','[email protected]','650.124.1214',to_date('16-JUL-16','DD-MON-RR'),21,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (27,'Kai','Long','[email protected]','650.124.1224',to_date('28-SEP-16','DD-MON-RR'),21,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (28,'Aaron','Patterson','[email protected]','650.124.1334',to_date('14-JAN-16','DD-MON-RR'),21,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (29,'Roman','Hughes','[email protected]','650.124.1434',to_date('08-MAR-16','DD-MON-RR'),21,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (30,'Austin','Flores','[email protected]','650.124.5234',to_date('20-AUG-16','DD-MON-RR'),22,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (31,'Ellis','Washington','[email protected]','650.124.6234',to_date('30-OCT-16','DD-MON-RR'),22,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (32,'Jamie','Butler','[email protected]','650.124.7234',to_date('16-FEB-16','DD-MON-RR'),22,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (45,'Isla','Graham','[email protected]','650.121.2004',to_date('09-JUL-16','DD-MON-RR'),25,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (34,'Seth','Foster','[email protected]','650.127.1934',to_date('14-JUN-16','DD-MON-RR'),23,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (35,'Carter','Gonzales','[email protected]','650.127.1834',to_date('26-AUG-16','DD-MON-RR'),23,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (36,'Felix','Bryant','[email protected]','650.127.1734',to_date('12-DEC-16','DD-MON-RR'),23,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (37,'Ibrahim','Alexander','[email protected]','650.127.1634',to_date('06-FEB-16','DD-MON-RR'),23,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (38,'Sonny','Russell','[email protected]','650.121.1234',to_date('14-JUL-16','DD-MON-RR'),24,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (39,'Kian','Griffin','[email protected]','650.121.2034',to_date('26-OCT-16','DD-MON-RR'),24,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (40,'Caleb','Diaz','[email protected]','650.121.2019',to_date('12-FEB-16','DD-MON-RR'),24,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (25,'Ronnie','Perry','[email protected]','650.123.5234',to_date('16-NOV-16','DD-MON-RR'),1,'Stock Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (24,'Callum','Jenkins','[email protected]','650.123.4234',to_date('10-OCT-16','DD-MON-RR'),1,'Stock Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (23,'Jackson','Coleman','[email protected]','650.123.3234',to_date('01-MAY-16','DD-MON-RR'),1,'Stock Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (22,'Liam','Henderson','[email protected]','650.123.2234',to_date('10-APR-16','DD-MON-RR'),1,'Stock Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (21,'Jaxon','Ross','[email protected]','650.123.1234',to_date('18-JUL-16','DD-MON-RR'),1,'Stock Manager');
uj5u.com熱心網友回復：
如果您在員工 ID 上根本沒有任何條件，您最終會得到發生自我匹配的記錄，例如結果將顯示“Gracie Gardner 與 Gracie Gardner 在同一天被聘用”
然后我們可以輸入ON e1.employee_id <> e2.employee_id- 這會阻止 Gracie 與 Gracie 匹配，但是你會發現“Gracie Gardner 與 Summer Payne 是在同一天被聘用的”和“Summer Payne 和 Gracie Gardner 是在同一天被聘用的”——即你d 在“人與人配對”方面得到“重復記錄”，每個名字都被雙向提及
使用大于可以防止這種情況，并且實際上意味著任何給定的名稱對只出現一次。因為 Gracie 的 ID 比 Summer 的小，所以你會被Gracie in e1配對Summer in e2但你不會被Summer in e1配對Gracie in e2
另一種可視化它的方法是使用正方形/矩陣
no id id1<>id2 id1<id2
A B C A B C A B C
A * * * A * * A
B * * * B * * B *
C * * * C * * C * *
在第一個方格中，所有東西都與所有東西相匹配。在第二個中，您洗掉了 AA / BB / CC 匹配項，但仍然有 AB 和 BA、AC 和 CA 等。在第三個方格中，您只有唯一配對的字母（AB 但不是 BA，AC 但不是 CA，...）
uj5u.com熱心網友回復：
如果你比較!=（而不是>）：
SELECT e1.hire_date,
e1.first_name || ' ' || e1.last_name AS employee1,
e2.first_name || ' ' || e2.last_name AS employee2
FROM employees e1
INNER JOIN employees e2
ON e1.employee_id != e2.employee_id AND e1.hire_date = e2.hire_date
ORDER BY
e1.hire_date DESC,
employee1,
employee2;
然后你會發現你得到了重復的行，這些行的值交換了名稱：
雇用日期 員工1 員工2 16 年 12 月 7 日 艾略特布魯克斯 羅里·凱利 16 年 12 月 7 日 羅里·凱利 艾略特布魯克斯
如果你使用 just >then 你只會得到兩行之一，因為另一行被過濾掉了：
雇用日期 員工1 員工2 16 年 12 月 7 日 羅里·凱利 艾略特布魯克斯
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/367860.html