select department.name as department,salary,count(department) as no_of_people from empolyee inner join department on empolyee.dept_id=department.dept_id group by department.name,salary;
select department.name as department,salary,count(department) as no_of_people from empolyee inner join department on empolyee.dept_id=department.dept_id where salary>1000 group by department.name,salary;
SELECT a.name,b.salary,b.no_of_people FROM (SELECT dept_id,name,row_number()over(ORDER BY name) ORDER_id FROM department t WHERE dept_id<>1) a, (SELECT t.salary salary,COUNT(t.salary) no_of_people ,row_number()over(ORDER BY COUNT(t.salary) DESC)order_id FROM empolyee t WHERE t.salary>1000 GROUP BY t.salary) b WHERE a.ORDER_id=b.order_id真变态的需求
格式 1 account 2000.000000000000000 3 2 business 4000.000000000000000 1
from empolyee inner join department on empolyee.dept_id=department.dept_id
group by department.name,salary;
from empolyee inner join department on empolyee.dept_id=department.dept_id where salary>1000
group by department.name,salary;
business的dept_id应该是3,不是2
vincent的dept_id应该是2,不是4
这叫敢于挑战权威,一种很好的职业素养。呵呵
SELECT a.name,b.salary,b.no_of_people
FROM (SELECT dept_id,name,row_number()over(ORDER BY name) ORDER_id FROM department t WHERE dept_id<>1) a,
(SELECT t.salary salary,COUNT(t.salary) no_of_people ,row_number()over(ORDER BY COUNT(t.salary) DESC)order_id
FROM empolyee t
WHERE t.salary>1000
GROUP BY t.salary) b
WHERE a.ORDER_id=b.order_id真变态的需求
1 account 2000.000000000000000 3
2 business 4000.000000000000000 1
应该是选出每个部门的平均工资和员工人数。仅是猜测,如果是这个命题的话,还比较简单了。