Question

请用一条sql语句求出各部门总和，最高工资者的姓名

员工表Employee 字段:empId 员工id,empname员工姓名,dept部门id
工资表Wage 字段:empId 员工id,wage 工资
我的答案:
select name , sumWage from (
select SUM(wage) sumWage,dept from Employee e,Wage w
where e.empId = w.empId
group by dept) a,
(select max(e.empname) name ,dept from Employee e,Wage w
where e.empId = w.empId
and wage in (
select MAX(wage) maxWage from Employee e,Wage w
where e.empId = w.empId
group by e.dept)
group by dept
) b where a.dept = b.dept

求简洁的。

Answer 1

开窗函数可以实现，我试着写了一个（好长时间没用了，估计会有点错误，你试试，有问题的话交流一下）
SELECT
max(t.empname) name,
max(wage) maxWage,
max(sumWage) sumWage,
t.dept
FROM
(
SELECT
e.empname,
w.wage,
e.dept,
sum(wage) over(partition by e.dept) sumWage
FROM
Employee e,
Wage w
WHERE
e.empId = w.empId
)
t
GROUP BY
t.dept

Answer 2

select e.empname from Employee e where e.empId in (select A.ewpId from (select Max(w.wage) , w.ewpId from Wage w) as A )

Answer 3

ORA-03113 end-of-file on communication channel

Cause: An unexpected end-of-file was processed on the communication
channel. The problem could not be handled by the Net8 two-task software. This
message could occur if the shadow two-task process associated with a Net8
connect has terminated abnormally, or if there is a physical failure of the
interprocess communication vehicle, that is, the network or server machine
went down.