select a.部门名称,isnull(b.人数,0) from 部门表 a left join (select 部门id,人数=count(1) from 人员表 group by 部门id)b on a.部门id=b.部门id
/*人员信息表应该有部门名称*/select distinct a.部门名称,(select count(*) from 人员信息表 where 部门名称=a.部门名称)s_num from 人员信息表 a
是不是我说的不够明确?我现在给出表结构 employee 表 id name Dep_idDepartment 表 id name要求的显示结果: d.id d.name e.name 1 第一部门 张三 2 第二部门 李四 3 第二部门 王五 4 第三部门 null第三部门没有人员信息,但是要显示出来
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)
典型一个left jion 链接
select Department.id,Department.name,Count(*) from Department,employee where Department.name=employee.name group by Department.id,Department.name
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
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
--不用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
非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
select a.部门名称,isnull(b.人数,0) from 部门表 a left join
(select 部门id,人数=count(1) from 人员表 group by 部门id)b
on a.部门id=b.部门id
from 人员信息表 a
employee 表
id name Dep_idDepartment 表
id name要求的显示结果:
d.id d.name e.name
1 第一部门 张三
2 第二部门 李四
3 第二部门 王五
4 第三部门 null第三部门没有人员信息,但是要显示出来
where d.id=e.Dep_id
union
select id,name,null from department where id not in
(select distinct dep_id from employee)
select Department.id,Department.name,Count(*) from Department,employee
where Department.name=employee.name
group by Department.id,Department.name
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
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
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
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