嵌套查询语句

Question

select no,min(name),min(gz)
from department
group by no
having gz>60
???

Answer 1

select e.department_id,DEPARTMENT_NAME,salary
from employees e,departments d 
where e.department_id = d.department_id
and e.department_id IN (select department_id from employees 
group by department_id
having min(salary) >  
(select min(salary) from employees group by department_id having department_id=60));
只能想到这怎么再提取部门的最大工资呀，

Answer 2

select e.department_id,DEPARTMENT_NAME,salary
from employees e,departments d 
where e.department_id = d.department_id
and e.department_id IN (select department_id from employees 
group by department_id
having min(salary) >  
(select min(salary) from employees group by department_id having department_id=60))
and salary in (select max(salary) from employees group by department_id having department_id <> 60);
结果和想象的比一样？？为什么？

Answer 3

select e.department_id,d.department_name,e.salary
from (select department_id,min(salary) salary from employees group by department_id) e,department d
where e.department_id = d.department_id and
      e.salary > (select min(salary) from employees where department_id=60)

Answer 4

select e.department_id,DEPARTMENT_NAME,salary
from employees e,departments d 
where e.department_id = d.department_id
and e.department_id IN (select department_id from employees 
group by department_id
having min(salary) >  
(select min(salary) from employees group by department_id having department_id=60))
//and salary in (select max(salary) from employees group by department_id having //department_id <> 60);
lz把你的最后一个and条件去了应该就满足你的要求了

Answer 5

//
select e.department_id,DEPARTMENT_NAME,salary
from employees e,departments d 
where e.department_id = d.department_id
and e.department_id 
                  IN 
                    (select department_id 
                      from employees 
                      group by department_id
                      having min(salary) >  
                      (select min(salary) 
                      from employees 
                      group by department_id 
                      having department_id=60))  //挑出符合部门最小工资大于60号部门最小工资的部门号
and salary 
                 IN 
                   (select max(salary) 
                   from employees 
                   group by department_id 
                   having department_id <> 60);//挑出除60号部门以外的部门的最大工资

Answer 6

select e.department_id,d.department_name,min(salary) 
from employees e,departments d 
where e.department_id=d.department_id 
group by e.department_id,d.department_name 
having min(salary) > any (select min(salary) 
                          from employees 
                          where department_id=60);

Answer 7

select e.department_id,d.department_name,e.minsalary
  from departments d,
    (
    select department_id,min(salary) minsalary
      from employees group by department_id
      having min(salary) > (select min(salary) from employees where department_id=60)
    ) e
  where d.department_id=e.department_id;

Answer 8

select t1.*
  from departments t1,
       (select distinct t.department_id,
                        min(t.salary) over(PARTITION by t.department_id) num
          from employees t) t2,
       (select min(t.salary) ms from employees t where t.department_id = 60) t3
 where t1.department_id = t2.department_id
   and t2.num > t3.ms

Answer 9

晕死了,昨天想了那么长时间
今天问老师 才知道还有结果集这个概念;
 可是我并不满足与结果正确,请高手帮忙看看
select e.department_id,DEPARTMENT_NAME,salary
from employees e,departments d 
where e.department_id = d.department_id
and e.department_id 
                  IN 
                    (select department_id 
                      from employees 
                      group by department_id
                      having min(salary) >  
                      (select min(salary) 
                      from employees 
                      group by department_id 
                      having department_id=60))  //挑出符合部门最小工资大于60号部门最小工资的部门号
and salary 
                 IN 
                   (select max(salary) 
                   from employees 
                   group by department_id 
                   having department_id <> 60);//挑出除60号部门以外的部门的最大工资
按照我的思路看看
告诉我我的逻辑错误是什么,为什么结果不对
谢谢高手!!