sql 显示部门的人员信息，没有人员的部门显示为0【不使用 jion，如何实现】

[cjolj]

rt

[lxd99423]

--不用join就这么写吧：
select a.id,a.name,
isnull((select max(b.name) from employee b where b.dep_id = a.id),'0') as name
from Department a

[edithbianyuhua]

非leftjoin:
select d.id,d.name,e.name from Department d,employee e where d.id=e.Dep_id
union
select id,name,null from department where id not in (select distinct dep_id from employee)

leftjoin:
select d.id,d.name,e.name from Department d left join employee e on d.id=e.Dep_id

[pt1314917]

select a.部门名称,isnull(b.人数,0) from 部门表 a left join 
(select 部门id,人数=count(1) from 人员表 group by 部门id)b
on a.部门id=b.部门id

[jimoshatan]

/*人员信息表应该有部门名称*/

select  distinct a.部门名称,(select count(*) from 人员信息表 where 部门名称=a.部门名称)s_num
from 人员信息表 a

[cjolj]

是不是我说的不够明确？我现在给出表结构
employee 表
id name Dep_id

Department 表
id name

要求的显示结果：
d.id d.name e.name
1 第一部门 张三
2 第二部门 李四
3 第二部门 王五
4 第三部门 null

第三部门没有人员信息，但是要显示出来

[drysea]

为什么不能使用join？

[seethe]

select d.id,d.name,e.name from Department d,employee e
where d.id=e.Dep_id
union
select id,name,null from department where id not in
(select distinct dep_id from employee)

[ChinaJiaBing]

isnull(空,0)...

[zzz1975]

典型一个left jion 链接

[izbox]

select Department.id,Department.name,Count(*) from Department,employee
where Department.name=employee.name
group by Department.id,Department.name

[izbox]

select Department.id,Department.name,(case isnull(employee.name,'Null') when 'null' then 0 else employee.name end) from Department,employee
where Department.id=employee.Dep_id
group by Department.id,Department.name

[izbox]

Create table #a(id int,name nvarchar(50),depID int)
create table #b(id int,name nvarchar(50))

insert into #a(id,name,depID) values(1,'a',1)
insert into #a(id,name,depID) values(2,'b',2)
insert into #a(id,name,depID) values(3,'c',3)
insert into #b(id,name) values(1,'中国')
insert into #b(id,name) values(2,'美国')
insert into #b(id,name) values(3,'俄罗斯')
insert into #b(id,name) values(4,'日本')


select #b.id,#b.name,case isnull(#a.name,'null') when 'null' then '0' else #a.name end from #a right outer join #b
on #a.depID = #b.id