请求帮助 就1分了 请求帮助

Question

现在问题是有4个部门  有3个部门有员工问题在于  要求列出每个部门的员工工资总和 ,如果没有就用0表示 
select a.Depart_Id ,a.部门薪水,b.Depart_Namefrom 
dbo.DepartMent b,(select Depart_Id , sum (Salary) 部门薪水
from dbo.Employee 
group by Depart_Id)a
where a.Depart_Id=b.Depart_Id
-------我自己写的这个----------------  可是没有显示没人的哪个部门  问题:   怎么让哪个部门显示并显示工资数为0

Answer 1

---变成左连接,试试下面的select a.Depart_Id ,a.部门薪水,b.Depart_Name
from dbo.DepartMent b left join (select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
on a.Depart_Id*=b.Depart_Id
select a.Depart_Id ,a.部门薪水,b.Depart_Name
from dbo.DepartMent b,(select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
where a.Depart_Id*=b.Depart_Id

Answer 2

---刚刚的有错误，用这个
select a.Depart_Id ,a.部门薪水,b.Depart_Name
from dbo.DepartMent b left join (select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
on a.Depart_Id=b.Depart_Id
select a.Depart_Id ,a.部门薪水,b.Depart_Name
from dbo.DepartMent b,(select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
where a.Depart_Id=*b.Depart_Id

Answer 3

select a.Depart_Id ,a.部门薪水,b.Depart_Name
from dbo.DepartMent b left join (select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
on a.Depart_Id*=b.Depart_Id--------提示:在包含已联接的表的查询中，不能指定外联接操作符。-----
select a.Depart_Id ,a.部门薪水,b.Depart_Name
from dbo.DepartMent b,(select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
where a.Depart_Id*=b.Depart_Id
---------  还是不能显示那个部门工资总和为0___________   不过还是要谢谢你   请关注

Answer 4

---刚刚的有错误，用这个
select a.Depart_Id ,a.部门薪水,b.Depart_Name
from dbo.DepartMent b left join (select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
on a.Depart_Id=b.Depart_Id
select a.Depart_Id ,a.部门薪水,b.Depart_Name
from dbo.DepartMent b,(select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
where a.Depart_Id=*b.Depart_Id----------部门显示出来  可是结果是NULL  要求为0----------------
   你速度好快!~ 继续关注

Answer 5

select a.Depart_Id ,isnull(a.部门薪水,0),b.Depart_Name
from dbo.DepartMent b left join (select Depart_Id , sum (Salary) 部门薪水 from dbo.Employee group by Depart_Id) a
on a.Depart_Id=b.Depart_Id

Answer 6

select t.Depart_id,sum(isnull(Salary,0)) from
(select Department.Depart_id,Salary from Department left join Employee on Employee.Depart_id=Department.Depart_id)
t
group by t.Depart_id

Answer 7

SELECT d.Depart_Id, 
       d.Depart_name,
       (SELECT SUM(Salary)
        FROM employee AS e
        WHERE e.Depart_id=d.Depart_id) AS '部门薪水'
FROM DepartMent AS d