以下是题目:
create table employee
(
employeeId int primary key,
empName varchar2(20) not null,
sex varchar2(10) not null,
birth date not null
)
create table salary
(
employeeId int 外键,
month int, 月份
salary int 工资
)
employeeId name sex birth
001 张三 男 1989-06-10
002 李四 男 1988-10-23
003 小花 女 1986-2-10employeeId month salary
001 1 100
001 2 200
001 3 300
002 1 400
002 2 500
003 1 600
1.查询性别为女的本月过生日的员工信息
2.写出员工最后一个月发工资的信息的sql语句
create table employee
(
employeeId int primary key,
empName varchar2(20) not null,
sex varchar2(10) not null,
birth date not null
)
create table salary
(
employeeId int 外键,
month int, 月份
salary int 工资
)
employeeId name sex birth
001 张三 男 1989-06-10
002 李四 男 1988-10-23
003 小花 女 1986-2-10employeeId month salary
001 1 100
001 2 200
001 3 300
002 1 400
002 2 500
003 1 600
1.查询性别为女的本月过生日的员工信息
2.写出员工最后一个月发工资的信息的sql语句
where sex='女' and to_char(birth,'MM')=to_char(sysdate,'MM');select * from salary a
where not exists(select * from salary b
where a.employeeid=b.employeeid and a.month<b.month);
select m.* from salary m where month = (select max(month) from salary where employeeId = m.employeeId)
select m.* from salary m where not exists (select 1 from salary where employeeId = m.employeeId and month > m.month)select m.* , n.* from employee m ,salary n
where m.employeeId = n.employeeId and n.month = (select max(month) from salary where employeeId = n.employeeId)
select m.* , n.* from employee m ,salary n
where m.employeeId = n.employeeId and not exists (select 1 from salary where employeeId = n.employeeId and month > n.month)
第一个更改为如下:select t.* from employee t where to_month(birth,'MM') = to_month(getdate,'MM')
1
select employeeId,name,sex,birth
from employee
where sex='女' and to_char(birth ,'mm')=to_char(sysdate,'mm')2
select a.employeeId,a.empName,a.sex,a.birth,b.month,b.salary
from employee a
(select employeeId,month,salary,row_number() over(partition by employeeId order by month desc) rn
from salary
) b
where b.rn=1 and a.employeeId=b.employeeId --or
select a.employeeId,a.empName,a.sex,a.birth,b.month,b.salary
from employee a,salary b
where a.employeeId=b.employeeId
and not exists(select 1 from salary c where b.employeeId=c.employeeId and b.month<c.month)
第一个更再改为如下:select t.* from employee t where to_char(birth,'MM') = to_char(getdate,'MM')