我现在有这样的四张表,
Employee(employee-name,street,city)
Works(employee-name,company-name,salary)
Company(company-name,city)
Manages(employee-name,manager-name)
假设现在Company中的公司是中国银行,工商银行,农业银行,怎么写一下的几个语句查询?!找出工资高于其所在公司员工平均工资的所有员工
!找出员工最多的公司
!找出工资总额最小的公司
!找出平均工资高于“中国银行”平均工资的所有公司谢谢。
Employee(employee-name,street,city)
Works(employee-name,company-name,salary)
Company(company-name,city)
Manages(employee-name,manager-name)
假设现在Company中的公司是中国银行,工商银行,农业银行,怎么写一下的几个语句查询?!找出工资高于其所在公司员工平均工资的所有员工
!找出员工最多的公司
!找出工资总额最小的公司
!找出平均工资高于“中国银行”平均工资的所有公司谢谢。
解决方案 »
- 如何查找:哪些存储过程中包含字段(或字符)?
- SQL2005企业管理器不能正常的保留登录密码.
- 如何恢复mmc
- 请问如何将一个没有ID列的表加上一个ID列?并能把这个ID列用做WHERE条件?
- sqlserver2005 怎么备份数据库到其他电脑?
- 一个消除死锁的问题
- 同一条记录中,有两个值和另一张表关联,如何联合查询
- 剔除两张表中不同字段的相同内容
- 曾解决的问题现出现新问题,请marco08和mengmou及各位好手再帮看看
- SQL Server安装问题?
- 一个变量传入问题,有老师能看出来我错在哪儿了吗?
- 一个简单的存储过程,报错: Must declare the variable '@sTableName'。不知错在哪里?
select * from works a,
(select avg(salary) as z,company-name from works group by companyname) b where a.salary>b.z and a.company-name=b.company-name
!找出员工最多的公司
select top 1 count(1) as t,employee-name from Employee group by employee-name order by count(1) desc
!找出工资总额最小的公司
select top 1 sum(salary) as z,company-name from works group by company-name order by sum(salary)
!找出平均工资高于“中国银行”平均工资的所有公司
同上
select * from
(
select *, avgsalary=avg(salary)over(partition by company_name) from Works
) as t
where salary > avgsalary--找出员工最多的公司
select top 1 company_name from Works group by company_name order by count(*) desc--找出工资总额最小的公司
select top 1 company_name from Works group by company_name order by sum(salary)--找出平均工资高于“中国银行”平均工资的所有公司
select company_name, avgsalary=avg(salary) from Works group by company_name
having avg(salary) > (select avg(salayr) from Works where company_name=中国银行)
select e.*
from employee e
join works w
on e.employee_name=w.employee_name
where w.salary>(select avg(salary) from works)2.
select top 1 c.company_name,count(*) cnt
from company c
join works w
on c.company_name=w.company_name
group by c.comany_name
order by 2 desc3.
select c.company_name
from company c
where c.company_name=(select top 1 company_name from works group by company_name order by sum(salary) asc)4.
select c.company_name
from company c,works w
where c.company_name=w.company_name
group by c.company_name
having avg(w.salary)>(select avg(salary) from works where company_name='中国银行')
select top 1 company_name from Works group by company_name order by count(*) desc
Employee(employee-name,street,city)
Works(employee-name,company-name,salary)
Company(company-name,city)
Manages(employee-name,manager-name)
假设现在Company中的公司是中国银行,工商银行,农业银行,怎么写一下的几个语句查询? !找出工资高于其所在公司员工平均工资的所有员工
select employee-name from Works where salary>(select avg(salary) from Works )!找出员工最多的公司
select top 1 company-name from
(select company-name,count(employee-name) as count_employee from Works group by company-name) a
order by a.count_employee desc!找出工资总额最小的公司
select top 1 company-name from
(select company-name,sum(salary) as sum_companyfrom Works group by company-name) a
order by a.sum_companyfrom !找出平均工资高于“中国银行”平均工资的所有公司
select company-name from
(
select company-name,avg(salary) as avg_salary from Works
group by company-name
) a
where a.avg_salary>
select avg(salary) from Works where company-name='中国银行'