Oracle高级查询_技术笔记

斯巴达汉子 2022-02-01

按照部门统计工人数，按照如下格式输出
方式一：函数的方式
select count(*) Total,
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
from emp;

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

方式二：子查询的方式
select
(select count(*) from emp) Total,
(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"
from dual;
dual 伪表

0赞 · 0采集
慕设计4302654 2021-10-13

案列三：
所有员工的入职日期：
注意：列的别名是纯数字的话必须将列明加上双引号
解决方式一：分析过程，使用函数解决得到查询结果
解决方式二：使用子查询的方式解决，利用Oracle的一张伪表dual表

0赞 · 0采集
慕九州州 2021-05-10

问题：统计员工入职年份：
1.使用函数的方式：
select count(*) from Total,
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0))"1980",
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0))"1982"
from emp;
to_char：将A按照XXX的格式转换成字符串
decode：hiredate是1980就返回1，否则返回0
2.使用子查询的方式解决（使用到oqacle的维表，dual）
(select count(*) emp) Total,
(select count(*) from emp where to_char(hiredate,'YYYY')='1980')"1980",
(select count(*) from emp where to_char(hiredate,'YYYY')='1982
3.存储过程来解决（ORACLE之PL/SQL基础中的课程中有提到）

0赞 · 0采集
风铃在想什么 2021-05-04

这题一拿过来，我首先想到了子查询直接用count函数计算
select
(select count(*) from emp) "Total",
(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"
from dual;

看到这个位置之后才知道可以借助decode返回数字来做sum累加，故意自己改成case when then else end

select count(*) Total,
sum(case to_char(hiredate,'yyyy') when '1980' then 1 else 0 end ) "1980",
sum(case to_char(hiredate,'yyyy') when '1981' then 1 else 0 end ) "1981",
sum(case to_char(hiredate,'yyyy') when '1982' then 1 else 0 end ) "1982",
sum(case to_char(hiredate,'yyyy') when '1987' then 1 else 0 end ) "1987"
from emp;

0赞 · 1采集
zycsyd 2020-08-07

sum(decode(to_char(hiredate,'yyyy'),'1990',1,0))
hiredate在1990的总人数
decode --判断
decode 变量，'对比值'，y,n

0赞 · 0采集
qq_慕标5384168 2020-03-04

示例三
按部门统计员工人数，按照如下格式输出
所有员工入职日期
select hiredate from emp
1、使用函数方式
select count(*) Total,
sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0))"1981",
sum(decode(to_char(hiredate,'yyyy'),'1982',1,0))"1982",
sum(decode(to_char(hiredate,'yyyy'),'1987',1,0))"1987"
from emp;
2、使用子查询方式
select
(select count(*) from emp) Total,
(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987",
from dual;

0赞 · 2采集
Clievens 2019-08-25

select count(*) as total,
sum(case to_char(t.hiredate,'yyyy') when'1981' then 1 else 0 end) "1981",
sum(case to_char(t.hiredate,'yyyy') when'1980' then 1 else 0 end) "1980",
sum(case to_char(t.hiredate,'yyyy') when'1987' then 1 else 0 end) "1987",
sum(case to_char(t.hiredate,'yyyy') when'1982' then 1 else 0 end) "1982"

from emp t;

0赞 · 0采集
慕粉2136255539 2019-08-14

方法二子查询

截图
0赞 · 0采集
慕粉2136255539 2019-08-14

方法一用函数

截图
0赞 · 0采集
慕粉2136255539 2019-08-14

问题

截图
0赞 · 0采集
慕仰5014332 2019-07-26

用to_char 将hiredate中的年部分提取出来‘YYYY’
select count(*) Total,
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
// sum(decode(extract(year from hiredate),'1981',1,0)) "1981"
from emp；

0赞 · 0采集
Amousy 2019-03-31

SQL> select count(*) Total,
2 sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
3 sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
4 sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",
5 sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
6 from emp;

TOTAL 1980 1981 1982 1987
---------- ---------- ---------- ---------- ----------
14 1 10 1 2

0赞 · 1采集
ldchenrj 2019-03-07

用to_char 将hiredate中的年部分提取出来‘YYYY’

截图
0赞 · 0采集
weibo_吉祥物mz_0 2019-01-09

伪表dual可以做临时表
decode函数的使用（相当于if条件语句），其中日期要转字符串，to_char()函数中第二个参数是日期格式，YYYY-MM-DD HH：MM：SS；
数字别名要加双引号

截图
0赞 · 0采集
eNothing 2018-10-19

select count(*) Total,
sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
from emp;

0赞 · 0采集
浪去 2018-06-27

decode函数可以用于字符串处理

截图
0赞 · 0采集
慕斯9756157 2018-06-03

select
(select count(*) total from emp) total,
(select count(*) from emp where to_char(hiredate,'YYYY')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'YYYY')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'YYYY')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'YYYY')='1987') "1987"
from dual

0赞 · 0采集
慕斯9756157 2018-06-03

select count(e.empno) total,
sum(decode(to_char(e.hiredate,'YYYY'),'1980',1,0)) "1980"，
sum(decode(to_char(e.hiredate,'YYYY'),'1981',1,0)) "1981"，
sum(decode(to_char(e.hiredate,'YYYY'),'1982',1,0)) "1982"，
sum(decode(to_char(e.hiredate,'YYYY'),'1987',1,0)) "1987"
from emp e

0赞 · 0采集
周班班 2018-05-31

select sum(sal) ,
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980" ,
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981" ,
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982" ,
sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
from emp;

0赞 · 0采集
龙_王 2018-04-05

Select
(Select Count(*) From emp)total,
(Select Count(*) From emp Where to_char(hiredate,'yyyy')='1980') "1980",
(Select Count(*) From emp Where to_char(hiredate,'yyyy')='1981') "1981",
(Select Count(*) From emp Where to_char(hiredate,'yyyy')='1982') "1982",
(Select Count(*) From emp Where to_char(hiredate,'yyyy')='1987') "1987"
From dual;

Select um(Decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
Sum(Decode(to_char(hiredate,'yyyy'),'1981',1,0))"1981",
Sum(Count(*) totla ,
SDecode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
Sum(Decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
From emp ;

0赞 · 0采集

小乌or小鸟 2018-04-04

SELECT COUNT(*) TOTAL,
       SUM(DECODE(TO_CHAR(HIREDATE,'yyyy'),'1980',1,0)) "1980", 
       SUM(DECODE(TO_CHAR(HIREDATE,'yyyy'),'1981',1,0)) "1981",
       SUM(DECODE(TO_CHAR(HIREDATE,'yyyy'),'1982',1,0)) "1982",
       SUM(DECODE(TO_CHAR(HIREDATE,'yyyy'),'1987',1,0)) "1987"
FROM EMP A

0赞 · 0采集

慕神6074173 2018-03-24

按部门统计员工的人数：
函数查询：
select count(*) Total, sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980", sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981", sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982", sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987" from emp;
子查询：
select (select count(*) from emp) Total, (select count(*) from emp where to_char(hiredate,'YYYY')='1980') "1980", (select count(*) from emp where to_char(hiredate,'YYYY')='1981') "1981", (select count(*) from emp where to_char(hiredate,'YYYY')='1982') "1982", (select count(*) from emp where to_char(hiredate,'YYYY')='1987') "1987" from dual ;

0赞 · 0采集
qq_堯壽_0 2018-01-24

001

截图
0赞 · 0采集
酱油乱晃 2018-01-15

decode(条件，判断条件，ture返回，false返回)； to_char（parameter,格式）返回新格式内容；

0赞 · 0采集
进击第一护妻狂魔 2017-11-23

select count(*) Total, sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980", sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981", sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982", sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987" from emp;//按部门统计员工人数 =select (select count(*) from emp) Total, (select count(*) from emp where to_char(hiredate,'YYYY')='1980') "1980", (select count(*) from emp where to_char(hiredate,'YYYY')='1981') "1981", (select count(*) from emp where to_char(hiredate,'YYYY')='1982') "1982",(select count(*) from emp where to_char(hiredate,'YYYY')='1987') "1987" from dual;

0赞 · 0采集
qq__3647 2017-08-24

decode(条件，判断条件，ture返回，false返回)； to_char（parameter,格式）返回新格式内容；

截图
0赞 · 1采集
sky丨天 2017-08-18

子表查询

截图
0赞 · 0采集
sky丨天 2017-08-18

多表查询

截图
0赞 · 0采集
qq_喂丶我的男人不准看_0 2017-06-28

decode====if else

0赞 · 0采集
KingStar1 2017-06-05

ghhj

截图
0赞 · 0采集

数据加载中...