查询每个部门的人员名字,和人员总数.
create table a(id int,name varchar(20),depname varchar(20))
name为人员名字.
depname 为部门名字.
create table a(id int,name varchar(20),depname varchar(20))
name为人员名字.
depname 为部门名字.
(
select depname,count(1) cnt from a group by depname) b
on a.depname=b.depname
select 1,'test1','it' union all
select 2,'test2','it' union all
select 3,'test3','accounting'
select a.depname,name,b.num from a inner join
(
select depname,count(1) num from a group by depname) b
on a.depname=b.depnamedrop table a
case when grouping(depname) = 1 then ''
when grouping(name)=1 then '合计'
else name end 姓名,count(*) 人数
from a
group by depname,name
with rollup
select depname 部门,name 姓名
from a
order by depname
compute count(name) by depname
create table a(id int,name varchar(20),depname varchar(20))
name为人员名字.
depname 为部门名字.select depname as 部门名字, count(*) as 人员总数 from a group by depname
select a1.depname,a1.[name],(select count(*)
from a a2
where a1.depname = a2.depname
group by depname) 员工人数
from a a1
order by a1.depname
create table dianying(id int,name varchar(20))
create table zhongjian(id int ,cid int ,did int)
insert into customer values(3,'ccccc','[email protected]')
insert into dianying values(2,'积极')
insert into zhongjian values(2,1,3)
select * from customer
select * from dianying
select * from zhongjian
select c.name,c.email,d.name from customer as c,dianying as d where c.id=2 and d.id=(select did from zhongjian where cid=2)