用一条sql语句查询？

[YangWenChaoX]

有如下两张表：
员工表：employee(employeeId,name,departName[部门]);
工资表：salary(employeeId,salary[薪水]);
两表的关系式一对一的。请用一条sql语句查询各部门员工的平均工资？
如果可以给我分析一下思路的话，更好？可以加分. 谢谢！

[lg3605119]

select a.departName,avg(b.salary) from employee a left join salary b on a.employeeId = b.employeeId group by a.departName

[htl258_Tony]

select a.departName,a.name,avg(isnull(b.salary,0)) 平均工资
from employee a
   left join salary b
     on a.employeeId=b.employeeId
group by a.departName,a.name

[qizhengsheng]

[Quote=引用 1 楼 lg3605119 的回复:]
SQL codeselect a.departName,avg(b.salary) from employee a left join salary b on a.employeeId = b.employeeId group by a.departName
[/Quote]
up

[htl258_Tony]

select a.departName,avg(isnull(b.salary,0)) 平均工资 
from employee a
   left join salary b
     on a.employeeId=b.employeeId
group by a.departName
--按部门分组,AVG函数求平均值,没什么好解释的.

MODIFY

[sdhdy]

select a.departName,avg(b.salary) from employee a,salary b where a.employeeId=b.employeeId
group by a.departName
order by a.departName

[htl258_Tony]

为什么要用ISNULL呢,看看以下例子就知道了:

DECLARE @ta TABLE(id int,col varchar(10))
INSERT @ta VALUES(1,'a');
INSERT @ta VALUES(2,'b');
INSERT @ta VALUES(3,'c');

DECLARE @tb TABLE(id int,num int)
INSERT @tb VALUES(1,10);
INSERT @tb VALUES(2,20);

select avg(b.num) from @ta a left join @tb b on a.id=b.id
/*
-----------
15
警告: 聚合或其他 SET 操作消除了 Null 值。

(1 行受影响)
*/
select avg(isnull(b.num,0)) from @ta a left join @tb b on a.id=b.id
/*
-----------
10

(1 行受影响)
*/

[--小F--]

[Quote=引用 4 楼 htl258 的回复:]
SQL codeselect a.departName,avg(isnull(b.salary,0)) 平均工资
from employee a
left join salary b
on a.employeeId=b.employeeId
group by a.departName
--按部门分组,AVG函数求平均值,没什么好解释的.

MODIFY
[/Quote]
支持 用isnull比较严谨

[JonasFeng]

SELECT E.DEPARTMENT,SUM(S.SALARY) AS ALLSALARY 
FROM SALARY S, EMPLOYEE E 
WHERE S.employeeId = E.employeeId 
GROUP BY E.DEPARTMENT

[JonasFeng]

[Quote=引用 8 楼 JonasFeng 的回复:]
SQL codeSELECT E.DEPARTMENT,SUM(S.SALARY) AS ALLSALARY
FROM SALARY S, EMPLOYEE E
WHERE S.employeeId = E.employeeId
GROUP BY E.DEPARTMENT
[/Quote]

改下，看错了。以为是SUM

SQL codeSELECT E.DEPARTMENT,avg(isnull(S.SALARY,0)) AS avgSALARY 
FROM SALARY S, EMPLOYEE E 
WHERE S.employeeId = E.employeeId 
GROUP BY E.DEPARTMENT

[xiequan2]

select a.departName,avg(b.salary) from employee a,salary b where a.employeeId = b.employeeId group by a.departName

[you_tube]

2005:

select a.departname,Avgsalary =  sum(b.salary) over (partition by a.departname)  / count(1) over (partition by a.departname) 
from employee a,salary b 
where a.employeeId=b.employeeId

[you_tube]

modify

select * from (
select a.departname,Avgsalary =  sum(b.salary) over (partition by a.departname)  / count(1) over (partition by a.departname) 
from employee a,salary b 
where a.employeeId=b.employeeId )
T

[you_tube]

忘了加个
distinct

[YangWenChaoX]

你给的语句根本就不对！ 没试过，对吧？