56. 获取所有员工的emp_no

本文介绍了一种使用SQL进行数据查询的方法,通过将部门员工信息表与员工奖金信息表进行自然左连接,来获取所有员工的编号、部门编号以及对应的奖金类型和接收日期。此方法适用于需要整合多个数据表并筛选特定信息的场景。

题目描述

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied,没有分配具体的员工不显示
CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
输出格式:

e.emp_nodept_nobtyperecevied
10001d00112010-01-01
10002d00122010-10-01
10003d00432011-12-03
10004d00412010-01-01
10005d003 
10006d002 
10007d005 
10008d005 
10009d006 
10010d005 
10010d006

这个题出的,,,一点都不用心。 

SELECT 
  d.emp_no, 
  d.dept_no, 
  eb.btype, 
  eb.recevied
FROM 
  dept_emp D 
NATURAL LEFT JOIN emp_bonus eb;

 

如下SQL需查找并统计年假未休请协助修改, 并优化oracle如下SQL性能,提高执行效率和减少资源占用: WITH V_DATA AS( SELECT HCP.SF_LSHR_NO_GET(A.EMP_CODE,19) WORK_PLACE, A.EMP_CODE,A.EMP_NAME,A.ANNUAL_LEAVE,A.YEAR FROM HCP.v_clk_tz_annual_leave A WHERE annual_leave>0 AND YEAR = 2025 --AND EMP_CODE='TZ143941' --AND ((TO_CHAR(SYSDATE, 'mm') IN ('04', '05', '06', '07', '08', '09') AND -- TRUNC(SYSDATE) IN -- (SELECT MAX(B.A) -- FROM (SELECT TRUNC(SYSDATE, 'mm') + ROWNUM - 1 A -- FROM DBA_OBJECTS -- WHERE ROWNUM < 32) B -- WHERE TO_CHAR(B.A, 'day') = 'friday ')) OR -- (TO_CHAR(SYSDATE, 'mm') IN ('10', '11') AND -- TO_CHAR(SYSDATE, 'Day') = 'Friday ') OR -- TO_CHAR(SYSDATE, 'mm') = '12') ), DEPT_PEOPLE AS( SELECT EMP_CODE,MEANING,HCP.SF_LSHR_NO_GET(MEANING, 8) V_EMAIL FROM ( SELECT T.EMP_CODE,MEANING,ROWNUM FROM V_DATA T JOIN HCP.KS_LOOKUP_VALUE KLV ON (KLV.REVERSE2 = SUBSTR(HCP.SF_LSHR_NO_GET(T.EMP_CODE, 15), LENGTH(HCP.SF_LSHR_NO_GET(T.EMP_CODE, 15))-5) OR KLV.REVERSE2 = HCP.SF_LSHR_NO_GET(T.EMP_CODE, 2)) WHERE KLV.LOOKUP_TYPE = 'DEPT_PEOPLE' AND KLV.ENABLED = 'Y' ORDER BY KLV.REVERSE2 DESC) WHERE ROWNUM = 1 ), V_CNT AS( SELECT DECODE(HPB.WORK_PLACE, 0, '一园', 1, '二园', 2, '三园', HPB.WORK_PLACE) AS PLANT, HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 14) DIV, HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 15) DEPT_NAME, HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 13) CLASS_NAME, COUNT(HPB.EMP_CODE) UNPEOPLE, SUM(HPB.ANNUAL_LEAVE) UNDAY, L.V_EMAIL || ';' || HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 9), 8) AS TOEMAIL, HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 10), 8) || ';' || HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 11), 8) AS CCEMAIL FROM V_DATA HPB JOIN DEPT_PEOPLE L ON HPB.EMP_CODE=L.EMP_CODE GROUP BY DECODE(HPB.WORK_PLACE, 0, '一园', 1, '二园', 2, '三园', HPB.WORK_PLACE), HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 14), HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 15), HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 13), L.V_EMAIL || ';' || HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 9), 8), HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 10), 8) || ';' || HCP.SF_LSHR_NO_GET(HCP.SF_LSHR_NO_GET(HPB.EMP_CODE, 11), 8) UNION ALL SELECT '合计', '', '', '', UNPEOPLE, UNDAY, '', '' FROM (SELECT COUNT(EMP_CODE) UNPEOPLE, SUM(ANNUAL_LEAVE) UNDAY FROM V_DATA) WHERE UNDAY IS NOT NULL ) SELECT PLANT 厂区, DIV 处级, DEPT_NAME 部门名称, CLASS_NAME 课别名称, UNPEOPLE 未休人数, UNDAY 未休天数, TOEMAIL, CCEMAIL FROM V_CNT ORDER BY PLANT, DIV, DEPT_NAME, CLASS_NAME;
最新发布
07-19
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值