有员工表 Employee(name, age, dept, salary) 请写出sql语句:
1 查询各部门工资总和
2 查询1982年上半年出生的人
3 按工资排序后取出第11至20条记录
4 将本表数据进行备份最后一个要用create table empl()?
1 查询各部门工资总和
2 查询1982年上半年出生的人
3 按工资排序后取出第11至20条记录
4 将本表数据进行备份最后一个要用create table empl()?
解决方案 »
- dataguard broker 求救
- 复杂的update sql应该怎样写
- ORA-01427: 单行子查询返回多于一个行
- win7安装oracle10g完成后登录时输入密码和用户名后界面就关闭
- oracle数据库中,如何查询指定时间段内的记录
- job不能正常执行??
- 请问在SQL PLUS中执行一个存储过程,其中有问题,请问用什么命令现实是多少行出错,或者错误信息?
- 请问在ORACLE中有没有像SQLSERVER中那样的种子,来实现自增类型,或方法
- ORACLE 字段类型问题? 100分
- Oracle10+:关于定时给表做分析的设计(实际应用)【对原先的代码进行进一步修改】
- 关于oracle数据库的问题
- 今天去面试,几道不太会的8道题,各位帮看看,多谢
1
select dept,sum(salary) salary
from
Employee
select *
from(
select *,rownum rn
from Employee
order by salary desc)
where rn>=11 and rn<=20
from Employee
group by dept
3. select * from (select employee.*, rownum no from rownum<=20)
where no>=11
where no>=11
where no>=11 你这个有问题的,要salary是PK才能这么写的,如果不是PK
是先取rownum<=20再排序,这样会会预想的效果两样的
保险的方法是用我写的语句,肯定不会错
那个age字段记得原体上写的是birth_date,不好意思,原帖没法编辑了。
最后一题呢?
2
select *
from Employee
where to_char(birth_date,'yyyymm')>='198201'
and to_char(birth_date,'yyyymm')<='198206'
这么写行吗???
我菜鸟。
create table backup
要把表的字段写出来的
总而言之你要先把表建好
insert into backup
select *from employee 倒是可以的不过一般说的备份,不过是这样备份啊,这样在同一个数据库,备份的作用不大,只是万一主表有数据不对,可从备份表风景点找而已,而数据库一旦出问题,两个表一样不能用
那create table 再insert into 这么写应该可以吧?
----------------------
insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl; 要求目标表(destTbl)存在,由于目标表已经存在,所以我们除了插入源表(srcTbl)的字段外,还可以插入常量,如例中的:5。
试了一下这句,不能用于mysql呀
from Employee
group by dept2: select name
from Employee
where to_char(age,'yyyymm') between '198201' and '198206';3: select *
from
(
select *
from
(
select * from employee order by salary
)
where rownum <=20 minus select *
from
(
select * from employee order by salary
)
where rownum <=10
)
order by name4: create table EmpBck as select * from Employee;
是有些复杂,但是取得结果是正确的。
而且对于同样的查询语句,oracle 执行时候并不是花掉两倍时间,而是比一次多一点
最外层的查询其实只是为了排序,必要时可以去掉:
----------------------------------------------------------------
select *
from
(
select * from employee order by salary
)
where rownum <=20 minus select *
from
(
select * from employee order by salary
)
where rownum <=10
select *
from(
select *,rownum rn
from Employee
order by salary desc)
where rn>=11 and rn <=20
这样同样能取得正确的结果,效率就会比你的高点
from
(
select * from employee order by salary
)
where rownum <=20 minus select *
from
(
select * from employee order by salary
)
where rownum <=10 你的语句里
select *
from
(
select * from employee order by salary
)
where rownum <=20 要执行一次全表扫描,再order by 一下
然后
select *
from
(
select * from employee order by salary
)
where rownum <=10
再执行次全表扫描,再order by一下
这里已经是*2了
然后再minus一下,
假设一次查询的效率是1, 你的语句的效率就是2.X 而用
select *
from(
select *,rownum rn
from Employee
order by salary desc)
where rn>=11 and rn <=20
全表扫描一次,order by 一次,就出来结果了,效率比你的快一倍在数据量不大的时候是影响不大,等数据库里有几十万条的时候,你再来比较下就知道了
我用数据测试不到正确结果,下面是我的数据:
-----------------------------------------
NAME AGE DEPT SALARY
a01 1982/01/01 12:23:15 d01 100
a02 1982/01/02 12:23:15 d02 231
a03 1982/01/03 12:23:15 d03 10
a04 1982/01/04 12:23:15 d04 0
a05 1982/01/05 12:23:15 d05 999
a06 1982/01/06 12:23:15 d06 1000
a07 1982/01/07 12:23:15 d07 6847
a08 1982/01/08 12:23:15 d08 185
a09 1982/01/09 12:23:15 d09 2000
a10 1982/01/10 12:23:15 d10 3000
a11 1982/01/11 12:23:15 d11 1500
a12 1982/01/12 12:23:15 d12 1800
a13 1982/01/13 12:23:15 d13 2400
a14 1982/01/14 12:23:15 d14 6700
a15 1982/01/15 12:23:15 d15 9810
a16 1982/01/16 12:23:15 d16 8543
a17 1982/01/17 12:23:15 d17 5678
a18 1982/01/18 12:23:15 d18 9632
a19 1982/01/19 12:23:15 d19 1999
a20 1982/01/20 12:23:15 d20 7000
--------------------------------------------------你查询的结果是:
NAME AGE DEPT SALARY RN
a11 1982/01/11 12:23:15 d11 1500 11
a12 1982/01/12 12:23:15 d12 1800 12
a13 1982/01/13 12:23:15 d13 2400 13
a14 1982/01/14 12:23:15 d14 6700 14
a15 1982/01/15 12:23:15 d15 9810 15
a16 1982/01/16 12:23:15 d16 8543 16
a17 1982/01/17 12:23:15 d17 5678 17
a18 1982/01/18 12:23:15 d18 9632 18
a19 1982/01/19 12:23:15 d19 1999 19
a20 1982/01/20 12:23:15 d20 7000 20我的结果是:
NAME AGE DEPT SALARY
a07 1982/01/07 12:23:15 d07 6847
a09 1982/01/09 12:23:15 d09 2000
a10 1982/01/10 12:23:15 d10 3000
a13 1982/01/13 12:23:15 d13 2400
a14 1982/01/14 12:23:15 d14 6700
a15 1982/01/15 12:23:15 d15 9810
a16 1982/01/16 12:23:15 d16 8543
a17 1982/01/17 12:23:15 d17 5678
a18 1982/01/18 12:23:15 d18 9632
a20 1982/01/20 12:23:15 d20 7000难道你的还是正确的?
用我的语句肯定不会出现
NAME AGE DEPT SALARY RN
a11 1982/01/11 12:23:15 d11 1500 11
a12 1982/01/12 12:23:15 d12 1800 12
a13 1982/01/13 12:23:15 d13 2400 13
a14 1982/01/14 12:23:15 d14 6700 14
a15 1982/01/15 12:23:15 d15 9810 15
a16 1982/01/16 12:23:15 d16 8543 16
a17 1982/01/17 12:23:15 d17 5678 17
a18 1982/01/18 12:23:15 d18 9632 18
a19 1982/01/19 12:23:15 d19 1999 19
a20 1982/01/20 12:23:15 d20 7000 20
这样的结果
这条
a15 1982/01/15 12:23:15 d15 9810 15
应该是
a15 1982/01/15 12:23:15 d15 9810 20
我去测试下
WITH employee AS
(SELECT 'a01' NAME, '1982/01/01 12:23:15' age, 'd01' dept, 100 salary
FROM DUAL
UNION ALL
SELECT 'a02', '1982/01/02 12:23:15', 'd02', 231
FROM DUAL
UNION ALL
SELECT 'a03', '1982/01/03 12:23:15', 'd03', 10
FROM DUAL
UNION ALL
SELECT 'a04', '1982/01/04 12:23:15', 'd04', 0
FROM DUAL
UNION ALL
SELECT 'a05', '1982/01/05 12:23:15', 'd05', 999
FROM DUAL
UNION ALL
SELECT 'a06', '1982/01/06 12:23:15', 'd06', 1000
FROM DUAL
UNION ALL
SELECT 'a07', '1982/01/07 12:23:15', 'd07', 6847
FROM DUAL
UNION ALL
SELECT 'a08', '1982/01/08 12:23:15', 'd08', 185
FROM DUAL
UNION ALL
SELECT 'a09', '1982/01/09 12:23:15', 'd09', 2000
FROM DUAL
UNION ALL
SELECT 'a10', '1982/01/10 12:23:15', 'd10', 3000
FROM DUAL
UNION ALL
SELECT 'a11', '1982/01/11 12:23:15', 'd11', 1500
FROM DUAL
UNION ALL
SELECT 'a12', '1982/01/12 12:23:15', 'd12', 1800
FROM DUAL
UNION ALL
SELECT 'a13', '1982/01/13 12:23:15', 'd13', 2400
FROM DUAL
UNION ALL
SELECT 'a14', '1982/01/14 12:23:15', 'd14', 6700
FROM DUAL
UNION ALL
SELECT 'a15', '1982/01/15 12:23:15', 'd15', 9810
FROM DUAL
UNION ALL
SELECT 'a16', '1982/01/16 12:23:15', 'd16', 8543
FROM DUAL
UNION ALL
SELECT 'a17', '1982/01/17 12:23:15', 'd17', 5678
FROM DUAL
UNION ALL
SELECT 'a18', '1982/01/18 12:23:15', 'd18', 9632
FROM DUAL
UNION ALL
SELECT 'a19', '1982/01/19 12:23:15', 'd19', 1999
FROM DUAL
UNION ALL
SELECT 'a20', '1982/01/20 12:23:15', 'd20', 7000
FROM DUAL)
SELECT *
FROM (SELECT a.*, row_number() over (ORDER BY salary DESC) rn
FROM employee a
)
WHERE rn >= 11 AND rn <= 20
结果
Row# NAME AGE DEPT SALARY RN1 a19 1982/01/19 12:23:15 d19 1999 11
2 a12 1982/01/12 12:23:15 d12 1800 12
3 a11 1982/01/11 12:23:15 d11 1500 13
4 a06 1982/01/06 12:23:15 d06 1000 14
5 a05 1982/01/05 12:23:15 d05 999 15
6 a02 1982/01/02 12:23:15 d02 231 16
7 a08 1982/01/08 12:23:15 d08 185 17
8 a01 1982/01/01 12:23:15 d01 100 18
9 a03 1982/01/03 12:23:15 d03 10 19
10 a04 1982/01/04 12:23:15 d04 0 20
执行计划
Plan
SELECT STATEMENT ALL_ROWSCost: 41 Bytes: 940 Cardinality: 20
24 VIEW Cost: 41 Bytes: 940 Cardinality: 20
23 WINDOW SORT PUSHED RANK Cost: 41 Bytes: 680 Cardinality: 20
22 VIEW Cost: 40 Bytes: 680 Cardinality: 20
21 UNION-ALL
1 FAST DUAL Cost: 2 Cardinality: 1
2 FAST DUAL Cost: 2 Cardinality: 1
3 FAST DUAL Cost: 2 Cardinality: 1
4 FAST DUAL Cost: 2 Cardinality: 1
5 FAST DUAL Cost: 2 Cardinality: 1
6 FAST DUAL Cost: 2 Cardinality: 1
7 FAST DUAL Cost: 2 Cardinality: 1
8 FAST DUAL Cost: 2 Cardinality: 1
9 FAST DUAL Cost: 2 Cardinality: 1
10 FAST DUAL Cost: 2 Cardinality: 1
11 FAST DUAL Cost: 2 Cardinality: 1
12 FAST DUAL Cost: 2 Cardinality: 1
13 FAST DUAL Cost: 2 Cardinality: 1
14 FAST DUAL Cost: 2 Cardinality: 1
15 FAST DUAL Cost: 2 Cardinality: 1
16 FAST DUAL Cost: 2 Cardinality: 1
17 FAST DUAL Cost: 2 Cardinality: 1
18 FAST DUAL Cost: 2 Cardinality: 1
19 FAST DUAL Cost: 2 Cardinality: 1
20 FAST DUAL Cost: 2 Cardinality: 1
用你的语句结果
Row# NAME AGE DEPT SALARY1 a07 1982/01/07 12:23:15 d07 6847
2 a09 1982/01/09 12:23:15 d09 2000
3 a10 1982/01/10 12:23:15 d10 3000
4 a13 1982/01/13 12:23:15 d13 2400
5 a14 1982/01/14 12:23:15 d14 6700
6 a15 1982/01/15 12:23:15 d15 9810
7 a16 1982/01/16 12:23:15 d16 8543
8 a17 1982/01/17 12:23:15 d17 5678
9 a18 1982/01/18 12:23:15 d18 9632
10 a20 1982/01/20 12:23:15 d20 7000执行计划
Plan
SELECT STATEMENT ALL_ROWSCost: 49 Bytes: 680 Cardinality: 20
38 TEMP TABLE TRANSFORMATION
22 LOAD AS SELECT
21 UNION-ALL
1 FAST DUAL Cost: 2 Cardinality: 1
2 FAST DUAL Cost: 2 Cardinality: 1
3 FAST DUAL Cost: 2 Cardinality: 1
4 FAST DUAL Cost: 2 Cardinality: 1
5 FAST DUAL Cost: 2 Cardinality: 1
6 FAST DUAL Cost: 2 Cardinality: 1
7 FAST DUAL Cost: 2 Cardinality: 1
8 FAST DUAL Cost: 2 Cardinality: 1
9 FAST DUAL Cost: 2 Cardinality: 1
10 FAST DUAL Cost: 2 Cardinality: 1
11 FAST DUAL Cost: 2 Cardinality: 1
12 FAST DUAL Cost: 2 Cardinality: 1
13 FAST DUAL Cost: 2 Cardinality: 1
14 FAST DUAL Cost: 2 Cardinality: 1
15 FAST DUAL Cost: 2 Cardinality: 1
16 FAST DUAL Cost: 2 Cardinality: 1
17 FAST DUAL Cost: 2 Cardinality: 1
18 FAST DUAL Cost: 2 Cardinality: 1
19 FAST DUAL Cost: 2 Cardinality: 1
20 FAST DUAL Cost: 2 Cardinality: 1
37 SORT ORDER BY Cost: 9 Bytes: 680 Cardinality: 20
36 VIEW EASYTVC. Cost: 8 Bytes: 680 Cardinality: 20
35 MINUS
28 SORT UNIQUE Cost: 4 Bytes: 680 Cardinality: 20
27 COUNT STOPKEY
26 VIEW EASYTVC. Cost: 3 Bytes: 680 Cardinality: 20
25 SORT ORDER BY STOPKEY Cost: 3 Bytes: 680 Cardinality: 20
24 VIEW EASYTVC. Cost: 2 Bytes: 680 Cardinality: 20
23 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6605_112501D Cost: 2 Bytes: 880 Cardinality: 20
34 SORT UNIQUE Cost: 4 Bytes: 340 Cardinality: 10
33 COUNT STOPKEY
32 VIEW EASYTVC. Cost: 3 Bytes: 680 Cardinality: 20
31 SORT ORDER BY STOPKEY Cost: 3 Bytes: 680 Cardinality: 20
30 VIEW EASYTVC. Cost: 2 Bytes: 680 Cardinality: 20
29 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6605_112501D Cost: 2 Bytes: 880 Cardinality: 20
把我的语句改成升序出来的结果和你的一样
SELECT *
FROM (SELECT a.*, row_number() over (ORDER BY salary ) rn
FROM employee a
)
WHERE rn >= 11 AND rn <= 20Row# NAME AGE DEPT SALARY RN1 a09 1982/01/09 12:23:15 d09 2000 11
2 a13 1982/01/13 12:23:15 d13 2400 12
3 a10 1982/01/10 12:23:15 d10 3000 13
4 a17 1982/01/17 12:23:15 d17 5678 14
5 a14 1982/01/14 12:23:15 d14 6700 15
6 a07 1982/01/07 12:23:15 d07 6847 16
7 a20 1982/01/20 12:23:15 d20 7000 17
8 a16 1982/01/16 12:23:15 d16 8543 18
9 a18 1982/01/18 12:23:15 d18 9632 19
10 a15 1982/01/15 12:23:15 d15 9810 20
上面用rownum的是有点错误,要在外面再包层
SELECT STATEMENT ALL_ROWSCost: 41 Bytes: 940 Cardinality: 20
24 VIEW Cost: 41 Bytes: 940 Cardinality: 20
23 WINDOW SORT PUSHED RANK Cost: 41 Bytes: 680 Cardinality: 20
22 VIEW Cost: 40 Bytes: 680 Cardinality: 20 你的
SELECT STATEMENT ALL_ROWSCost: 49 Bytes: 680 Cardinality: 20
38 TEMP TABLE TRANSFORMATION
22 LOAD AS SELECT
37 SORT ORDER BY Cost: 9 Bytes: 680 Cardinality: 20
36 VIEW EASYTVC. Cost: 8 Bytes: 680 Cardinality: 20
35 MINUS
28 SORT UNIQUE Cost: 4 Bytes: 680 Cardinality: 20
27 COUNT STOPKEY
26 VIEW Cost: 3 Bytes: 680 Cardinality: 20
25 SORT ORDER BY STOPKEY Cost: 3 Bytes: 680 Cardinality: 20
24 VIEW Cost: 2 Bytes: 680 Cardinality: 20
23 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6605_112501D Cost: 2 Bytes: 880 Cardinality: 20
34 SORT UNIQUE Cost: 4 Bytes: 340 Cardinality: 10
33 COUNT STOPKEY
32 VIEW Cost: 3 Bytes: 680 Cardinality: 20
31 SORT ORDER BY STOPKEY Cost: 3 Bytes: 680 Cardinality: 20
30 VIEW Cost: 2 Bytes: 680 Cardinality: 20
29 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D6605_112501D Cost: 2 Bytes: 880 Cardinality: 20可以看看差别有多大
select *
from(
select * ,rownum rn
from(
select *
from Employee
order by salary desc) )
where rn>=11 and rn <=20
1、select dept,sum(salary) from employee group by dept
2、select name from employee where age between round((sysdate-date'1982-07-01')/365,1)
and round((sysdate-date'1982-01-01')/365,1)
3、SELECT *
FROM (SELECT a.*, row_number() over (ORDER BY salary ) rn
FROM employee a
)
WHERE rn >= 11 AND rn <= 20
4、create table EmpBck as select * from Employee where 1=1;
where rownum<='20' order by salary desc) b
where rownum<=20
minus
select rownum,b.* from (select rownum,a.* from employee a
where rownum<='20' order by salary desc) b
where rownum<11这样速度慢一点。工资从高到低的
from Employee
group by dept;
select name
from Employee
where to_char(age,'yyyymm') between '198201' and '198206';