今天笔试,遇到的一题:
一个 workerSalary 的表,有 salary 属性,现在要求 取出最高工资的三位所有属性,由低到高排列,然后剩下的
按原顺序排列
当时 不知道咋做,草草了事
回来用oracle 做测试,还是写不出来,所以求教大虾~测试的表为 class 属性有
classroom classname starttime endtime
101 a 2011-5-5 2011-8-8
102 b 2011-1-5 2011-7-8
103 c 2011-2-5 2011-10-8
... .. .... ......这句话可以取出最高几位并由低到高排列
select * from
(select * from class order by classroom desc)A
where rownum between 1 and 3 order by A.classroom asc
可是 后面加了
这句话 union select * from class 就报 无效sql 语句。
一个 workerSalary 的表,有 salary 属性,现在要求 取出最高工资的三位所有属性,由低到高排列,然后剩下的
按原顺序排列
当时 不知道咋做,草草了事
回来用oracle 做测试,还是写不出来,所以求教大虾~测试的表为 class 属性有
classroom classname starttime endtime
101 a 2011-5-5 2011-8-8
102 b 2011-1-5 2011-7-8
103 c 2011-2-5 2011-10-8
... .. .... ......这句话可以取出最高几位并由低到高排列
select * from
(select * from class order by classroom desc)A
where rownum between 1 and 3 order by A.classroom asc
可是 后面加了
这句话 union select * from class 就报 无效sql 语句。
解决方案 »
- Io异常:TheNetworkAdaptercouldnotestablishtheconnection
- rman 备份疑惑
- 如何制作数据库的软件升级包,急啊!在线等。
- 存储过程删数据
- 今天终于学会了配置Oracle for SQL Sever的Transparent Gateway了,真高兴!!散分!!
- 关于Oracle中old和new两个临时表
- 请问如何将SQL Server数据库转成Oracle数据库??
- 安装时的错误(急!急!急!)
- 请教大家一个直接插入日期型数据的一个问题,可能很多人也一样迷惑这块!
- 请高人:每月月底执行一次该如何写?DBMS_JOB.SUBMIT(:jobno, 'NEw(''00'',1,to_char(sysdate,''yyyymm''));', SYSDATE, 'add_mo
- 求高手帮写一个SQL语句,在线等..........
- 一个Oracle查询问题(大家给看下)
FROM (SELECT * FROM CLASS ORDER BY CLASSROOM DESC) A
WHERE A.ROWNUM < 4
UNION ALL
SELECT b.*
FROM CLASS b
WHERE b.ROWID NOT IN
(SELECT ROWID
FROM (SELECT ROWID FROM CLASS ORDER BY CLASSROOM DESC) c
WHERE c.ROWNUM < 4)
SELECT t.*
FROM employees t
ORDER BY
CASE WHEN row_number() over(ORDER BY t.salary DESC) > 3
THEN t.employee_id
ELSE row_number() over(ORDER BY t.salary DESC)
END
SELECT A.*
FROM (SELECT * FROM CLASS ORDER BY CLASSROOM DESC) A
WHERE ROWNUM < 4
UNION all
SELECT b.*
FROM CLASS b
WHERE b.ROWID NOT IN
(SELECT ROWID
FROM (SELECT ROWID FROM CLASS ORDER BY CLASSROOM DESC) c
WHERE ROWNUM < 4)
的确可以查出来,但是最高三位 是降序的,我在 union all 前 加个 order by classroom asc ,报无效sql 语句
from (select classroom,
classname,
starttime,
endtime,
row_number() over(order by classroom desc) as cn
from table1 t)
where cn < 4
union
select classroom, classname, starttime, endtime from class
不过好像 hudingchen 兄误解了,
在表结构为:
EMP_ID SALARY
1 156
2 415
3 545
4 546
5 445
6 844
7 485
时,执行
SELECT t.*
FROM workersalary t
ORDER BY
case when row_number() over(ORDER BY t.salary DESC) <= 3
then row_number() over(ORDER BY t.salary DESC)
else t.emp_id
end
(和您的意思差不多,我参考的http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html)
结果为
EMP_ID SALARY
6 844
1 156
4 546
2 415
3 545
5 445
7 485是我表述不好,要求是最高三位放在开始以升序(按salary)排列,剩下的数据跟在后面按原来顺序(如同这表按emp_id)排列
(select * from
(select * from class order by classroom desc)A
where rownum between 1 and 3 order by A.classroom asc)
union select * from class
我试过,报 缺少右括号,并且显示错位开始为 order by A.classroom 的order 上
select EMP_ID, SALARY
from (select EMP_ID, SALARY, row_number() over(order by SALARY desc) as cn
from workersalary)
where cn < 4
union
select EMP_ID, SALARY from workersalary;
应该可以了,可是
查询结果和预期不一样,不知道为什么
结构为 我只取了classroom
select classroom from (select classroom,
classname,
starttime,
endtime,
row_number() over(order by classroom desc) as cn
from class t) where cn <= 3
union select classroom from class
CLASSROOM
101
102
103
104
105
106union 前面的是 106 105 104 这个还得改下,我再试试~
SQL> WITH workersalary AS (
2 SELECT 1 emp_id,156 salary FROM DUAL UNION ALL
3 SELECT 2 emp_id,415 salary FROM DUAL UNION ALL
4 SELECT 3 emp_id,545 salary FROM DUAL UNION ALL
5 SELECT 4 emp_id,546 salary FROM DUAL UNION ALL
6 SELECT 5 emp_id,445 salary FROM DUAL UNION ALL
7 SELECT 6 emp_id,844 salary FROM DUAL UNION ALL
8 SELECT 7 emp_id,485 salary FROM DUAL
9 )
10 SELECT t.*
11 FROM workersalary t
12 ORDER BY CASE
13 WHEN ROW_NUMBER() OVER(ORDER BY t.salary DESC) > 3 THEN
14 NULL
15 ELSE
16 ROW_NUMBER() OVER(ORDER BY t.salary DESC)
17 END,
18 emp_id
19 ; EMP_ID SALARY
---------- ----------
6 844
4 546
3 545
1 156
2 415
5 445
7 4857 rows selected
SQL> WITH workersalary AS (
2 SELECT 1 emp_id,156 salary FROM DUAL UNION ALL
3 SELECT 2 emp_id,415 salary FROM DUAL UNION ALL
4 SELECT 3 emp_id,545 salary FROM DUAL UNION ALL
5 SELECT 4 emp_id,546 salary FROM DUAL UNION ALL
6 SELECT 5 emp_id,445 salary FROM DUAL UNION ALL
7 SELECT 6 emp_id,844 salary FROM DUAL UNION ALL
8 SELECT 7 emp_id,485 salary FROM DUAL
9 )
10 SELECT t.*
11 FROM workersalary t
12 ORDER BY CASE
13 WHEN ROW_NUMBER() OVER(ORDER BY t.salary DESC) > 3 THEN
14 NULL
15 ELSE
16 ROW_NUMBER() OVER(ORDER BY t.salary ASC)
17 END ,
18 emp_id
19 ; EMP_ID SALARY
---------- ----------
3 545
4 546
6 844
1 156
2 415
5 445
7 4857 rows selected