day 20试题

--根据EMP_ID求bug最多的--
select
EMP_ID
,COUNT(1) bug_count
,convert(varchar(7),DATE_FOUND,120) month_bug
into sdata.dbo.emp_idbioa
from sdata.dbo.ERD_BUG a
where convert(varchar(7),DATE_FOUND,120) =convert(varchar(7),dateadd(month,-1,getdate()),120)
group by
EMP_ID
,convert(varchar(7),DATE_FOUND,120)
select * from sdata.dbo.emp_idbioa

--求bug_count=最大的bug_count的 EMP_ID--
select
EMP_ID
,COUNT(1) bug_count
,convert(varchar(7),DATE_FOUND,120) month_bug
into sdata.dbo.emp_id_max_bug
from sdata.dbo.ERD_BUG a
where convert(varchar(7),DATE_FOUND,120) =convert(varchar(7),dateadd(month,-1,getdate()),120)
group by
EMP_ID
,convert(varchar(7),DATE_FOUND,120)
having COUNT(1)=
( select
MAX(bug_count) bug_count
from sdata.dbo.emp_idbioa)
select * from sdata.dbo.emp_id_max_bug

 

select distinct b.ENAME from sdata.dbo.emp_id_max_bug a
left join sdata.dbo.ERD_EMP b
on a.EMP_ID=b.EMP_ID

select * from sdata.dbo.ERD_EMP

create table sdata.dbo.ERD_EMP(
EMP_ID varchar(255)
,ENAME varchar(255)
,DERT_ID varchar(255)
)
insert into sdata.dbo.ERD_EMP values ('1','laomo','9001')
insert into sdata.dbo.ERD_EMP values ('2','xiaoming','9002')
insert into sdata.dbo.ERD_EMP values ('3','wang','9003')