047:QUESTION 228

Question:

View the Exhibit and examine the description of the EMPLOYEES table. You executed the 
following SQL statement: 
SELECT first_name, department_id, salary 
FROM employees ORDER BY department_id, first_name, salary desc; 

Which two statements are true regarding the output of the above query? (Choose two.)  

A. The values in all the columns would be sorted in the descending order. 
B. The values in the SALARY column would be sorted in descending order for all the employees 
having the same value in the DEPARTMENT_ID column. 
C. The values in the FIRST_NAME column would be sorted in ascending order for all the 
employees having the same value in the DEPARTMENT_ID column. 
D. The values in the FIRST_NAME column would be sorted in the descending order for all the 
employees having the same value in the DEPARTMENT_ID column. 
E. The values in the SALARY column would be sorted in descending order for all the employees 
having the same value in the DEPARTMENT_ID and FIRST_NAME column. 

Answer:C,E

Translate：

SELECT first_name, department_id, salary 
FROM employees ORDER BY department_id, first_name, salary desc; 

观察EMPLOYEES表，关于上述语句的输出以下哪两项是正确的？

A.所有字段的值都会降序排列。

B.SALARY字段会为所有拥有相同DEPARTMENT_ID值的雇员降序排列。

C.FIRST_NAME会为所有拥有相同DEPARTMENT_ID值的雇员升序排列。

D.FIRST_NAME会为所有拥有相同DEPARTMENT_ID值的雇员降序排列。

E.SALARY会为所有拥有相同DEPARTMENT_ID值和FIRST_NAME值的雇员降序排列。

Notes：

Order by  有优先级的,排前面的优先级最高,  第一列排序完成后再按第二列指定顺序排序,  而且默认是升序.但是试验结果表明对于 union 这种形式组织起来的数据不是完全是这样的,明确指出 order by  的那列优先级是最高的,而不是排前面的列优先级最高. 

SQL> create table employees(first_name varchar(3),department_id varchar(3),salary varchar(3)); 
SQL>insert into employees values('1','1','1'); 
SQL> insert into employees values('1','2','3'); 
SQL> insert into employees values('1','3','2'); 
SQL> SELECT first_name, department_id, salary 
2   FROM employees ORDER BY department_id, first_name, salary desc; 
FIR DEP SAL 
--- --- --- 
1    1    1 
1    2    3 
1    3    2

说明排前面的优先级高,这里没有顾及到第 3 列要求降序排列

SQL> update employees set department_id='1'; 
SQL> SELECT first_name, department_id, salary 
2   FROM employees ORDER BY department_id, first_name, salary desc; 
FIR DEP SAL 
--- --- --- 
1    1    3   
1    1    2 
1    1    1 
SQL> update employees set first_name=salary; 
SQL> SELECT first_name, department_id, salary 
2   FROM employees ORDER BY department_id , first_name desc, salary; 
FIR DEP SAL 
--- --- --- 
3    1    3 
2    1    2 
1    1    1 

下面试验 union 聚集起来的数据 order by 情况

select 1,1,1 
from dual 
union 
select 1,2,3 
from dual 
union 
select 1,3,2 
from dual 
order by 3 desc; 
1           2           3 
1           3           2 
1           1           1 

(说明第 3 列要求降序排列优先级最高,而不是按排前面的列优先高来排序的，注意跟上面比较)

select 2,1,1 
from dual 
union 
select 3,2,3 
from dual 
union 
select 1,3,2 
from dual 
order by 2 desc,3 asc;  
1           3           2 
3           2           3 
2           1           1 

(说明第 2 列明确要求降序排列的优先级最高,而不是默认的第 1 列升序的优先级高) 

select 2,1,1 
from dual 
union 
select 3,2,3 
from dual 
union 
select 1,3,2   
from dual; 
1           3           2 
2           1           1 
3           2           3 

(在没有指明 order by 的前提下,这个时候按默认第一列升序排列,如果都相同，则按第 2 列升序