SQL ---- 查询统计

if exists(select 1 from sysobjects where [name]='sys_dept')
drop table sys_dept
go

create table sys_dept
(
deptId int
)

go

insert into sys_dept (deptId) values(10050001)
insert into sys_dept (deptId) values(10050002)
insert into sys_dept (deptId) values(10050003)
insert into sys_dept (deptId) values(10050004)

go

select * from sys_dept

go

if exists(select 1 from sysobjects where [name]='sys_person')
drop table sys_person
go
create table sys_person
(
personId int,
deptId int,
chick_date datetime
)
go

insert into sys_person (deptId,chick_date) values(10050001,'2001-02-03')
insert into sys_person (deptId,chick_date) values(10050001,'2001-08-01')
insert into sys_person (deptId) values(10050001)
insert into sys_person (deptId,chick_date) values(10050002,'2001-10-03')
insert into sys_person (deptId,chick_date) values(10050002,' 2001-02-09')
insert into sys_person (deptId) values(10050002)
insert into sys_person (deptId,chick_date) values(10050003,'2001-04-05')
insert into sys_person (deptId) values(10050004)

go

select * from sys_person


select d.deptId,max(p.chick_date) as get_key_date,count(p.chick_date) as key_count from sys_dept d left join sys_person p on d.deptId=p.deptId group by d.deptId