子查询――多字段的顺序问题

查和104号员工同一个部门和同一个经理的所有员工的员工编号、姓名和部门。

hr@OCM> SELECTemployee_id,first_name,department_id

  2  FROMemployees

  3 WHERE (manager_id,department_id) = (SELECT department_id,manager_id FROM

  4 employees

  5 WHERE employee_id=104)

  6  ANDemployee_id<.104;

 

no rows selected

用上面的语句，没有查出记录来。原来是什么呢？主要是where条件这里的子查询的操作符或字段的顺序写错了！我们来分析答案：

 

答案A是把这语句改成：exists的语法不是这样写的，明显不对！！！

 

hr@OCM> SELECTemployee_id,first_name,department_id

  2  FROMemployees

  3 WHERE (manager_id,department_id) exists (SELECT department_id,manager_idFROM

  4 employees

  5 WHERE employee_id=104)

  6  ANDemployee_id<>104;

WHERE(manager_id,department_id) exists (SELECT department_id,manager_id FROM

                                 *

ERROR at line 3:

ORA-00920: invalidrelational operator

 

答案B把这语句改成：结果显示无记录，其实这语法是没问题的，只是where条件主查询字段顺序不匹配，所以答案B不正确

hr@OCM> SELECTemployee_id,first_name,department_id

  2  FROMemployees

  3 WHERE (manager_id,department_id) in (SELECT department_id,manager_idFROM

  4  employees

  5 WHERE employee_id=104)

  6  ANDemployee_id<>104;

 

no rows selected

 

答案C把这语句改成：结果显示无记录，其实这语法是没问题的，只是where条件主查询字段顺序不匹配，，所以答案C不正确

 

hr@OCM> SELECTemployee_id,first_name,department_id

  2  FROMemployees

  3 WHERE (manager_id,department_id) = any (SELECT department_id,manager_idFROM

  4 employees

  5 WHERE employee_id=104)

  6  ANDemployee_id<>104;

 

no rows selected

 

 

答案D把这语句改成：显示三条记录，OK，那就是答案D，WHERE后面字段的顺序严格要和子查询里的字段顺序要一致。

 

hr@OCM> SELECTemployee_id,first_name,department_id

  2  FROMemployees

  3 WHERE (manager_id,department_id) = (SELECT manager_id,department_id FROM

  4 employees

  5 WHERE employee_id=104)

  6  ANDemployee_id<>104;

 

EMPLOYEE_IDFIRST_NAME           DEPARTMENT_ID

------------------------------- -------------

       105 David                          60

        106 Valli                           60

        107 Diana                           60

 

 

最后正确答案是选：D