当前位置:操作系统 > Unix/Linux >>

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

[每日一题] 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
 
 
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,