日期:2014-05-19  浏览次数:20754 次

一个简单的面试题求解!在线等,解决即给分!
请教:下有两张表,以下为表结构

create   table   dep(
depid   int   identity   primary   key,   --部门号
depname   varchar(20)       --   部门名称
)

create   table   emp(
empid   int   identity   primary   key,             --员工号
empname   varchar(20),                 --员工姓名
salary   money,                   --工资
depid   int                     --部门号
)

1、请用一条sql语句查询出各部门的平均工资。结果显示为如下形式:

      部门号               平均工资
    --------------------------
          1                   2500.4521
          2                   2845.0121

2、请列出工资大于本部门平均工资的员工姓名和部门号以及统计此部门的人数。结果显示为如下形式:

        EMPNAME           DEPID         部门人数
    ---------------------------------      
          ZHANGSAN           1                 15
          LISI                   2                 10

------解决方案--------------------
1.
select depid 部门号, avg(salary) 平均工资
from emp
group by depid
------解决方案--------------------
1、
select t1.depid, avg(t2.salary)
from dep t1
inner join dbo.emp t2 on t1.depid=t2.depid
group by t1.depid
------解决方案--------------------
---1、
select 部门号=depid,平均工资=avg(salary) from emp group by depid

select 部门号=a.depname,平均工资=avg(b.salary)
from dep a inner join emp b on a.depid=b.depid
group by depid

---2、

select empname,depid,部门人数=count(1)
from emp a inner join (select depid,salary=avg(salary) from emp group by depid) b on a.depid=b.depid and a.salary> b.salary
------解决方案--------------------
2、
SELECT T1.empname,T1.depid,T2.PersonNum
FROM dbo.emp T1
INNER JOIN
(select t1.depid, avg(t2.salary) AS salary, COUNT(empid) AS PersonNum
from dep t1
inner join dbo.emp t2 on t1.depid=t2.depid
group by t1.depid) T2 ON T1.depid=T2.depid
WHERE T1.salary> T2.salary
------解决方案--------------------
1.select depid 部门号,avg(salary)平均工资 from emp group by depid
2.select empname,emp.depid,s 部门人数 from emp Inner Join (select depid,avg(salary) a,count(1) s from emp group by depid) b on emp.depid=b.depid and salary> a