[每日一题] OCP1z0-047 :2013-07-21子查询――多字段的顺序

查和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