问题描述:

I have 2 tables Employees and department

Table employee columns

empid

name

salary

location

Table department columns

deptno

deptname

empid

mangerid

Now I want to find department-wise the highest salary and then increment them by 2000

网友答案:
update emp
set sal = sal + 2000
where empid in (
select empid from dept where (deptno,sal) in (
select d.deptno,max(e.sal)
    from emp e,
    dept d
    where e.empid = d.empid
    group by deptno));
网友答案:

ed: Hah It didn't occur to me that you actually meant update the rows, I was thinking you just wanted to retrieve the values. @Ajith Sasidharan's answer is the fuller one.

It seems odd that your department table references the employee table instead of the other way around, however this should do what you want:

SELECT
  d.deptno, 
  d.deptname, 
  max(e.salary+2000)
FROM
  department d
INNER JOIN
  employee e
ON 
  e.empid = d.empid
GROUP BY 
  d.deptno, d.deptname;
相关阅读:
Top