use dfby

drop table #t1
select * from #t1

create procedure addSalByDept
as
declare @c int,@i int,@x int,@dno int
--创建临时表将部门编号和每个部门的人数存储在临时表里
select num=identity(int,1,1),deptno,count(*) as 'rs' into #t1 from emp group by deptno having deptno is not null
select @c = count(*) from #t1

set @i = 1
set @x = 0 --记录每个部门有几个人
set @dno = 0
--循环表中的数据，对每一个部门进行判断
while @i <= @c
begin
   select @x = rs from #t1 where num = @i --查询第N条记录的部门人数
   select @dno = deptno from #t1 where num = @i --查询第N条记录的部门编号
  
   --如果部门人数是1~5
   if @x >= 1 and @x <= 5
   begin
     --update emp set sal = sal * 1.1 where deptno = (select deptno from #t1 where num = @i)
     print convert(varchar(10),@dno) + '号部门应加薪10%'
   end
   else
   begin
     if @x > 5 and @x <= 10
        print convert(varchar(10),@dno) + '号部门应加薪20%'
     else
        print convert(varchar(10),@dno) + '号部门应加薪30%'
   end

   set @i = @i + 1

end

--执行存储过程
exec addSalByDept
select * from emp

--判断临时表是否存在
select * from tempdb..sysobjects  where id = object_id('tempdb..#t3')

--判断系统表是否存在
select * from sysobjects where name = '#t2'

--如果临时表存在，则删除再重新创建
if exists(select * from tempdb..sysobjects  where id = object_id('tempdb..#t2'))
  drop table #t2
select empno,ename,sal,hiredate,deptno into #t2 from emp
select * from #t2

select empno,ename,hiredate,datediff(yy,hiredate,getdate()) from emp

select distinct e2.empno,e2.ename,null as 'rs' from emp e1,emp e2 where e1.mgr = e2.empno

drop table #t1
select empno,ename,sal,comm,sal + isnull(comm,0) as total into #t1 from emp where deptno = 10
declare @tax numeric(8,2)
set @tax = 0
select @tax = sal * 1.1 from #t1 where total >= 3500 and total < 9000
select @tax = @tax + sal * 1.2 from #t2 where total >= 9000