mssql sqlserver 面试题-基础部分(sql脚本编写)-1


摘要:
今天去参加c#开发工程师面试,最后尽然面试了sql脚本的写法
实验环境: sqlserver 2008 R2


   create table test(keyId int identity,qty int,info varchar(30))
   insert into test(qty,info)values(1,'a'),(2,'b'),(3,'C'),(6,'d'),(7,null),(8,'f')
   insert into test(qty,info)values(20,'a'),(6,'a'),(1,'m'),(1,'g'),(1,'f'),(10,null)
    go
   ---面试1.取表test,4~6行记录,keyId倒序排列,尽量采用多种方法---考虑最分页的掌握
     --方法1:使用top
	 select top 3 * from test where keyId not in (select top 3 keyId from test order by keyId desc  )  order by keyId desc 
	 --方法2:top +max 
	 select top 3 * from test where keyId <(select min(keyId) from (select top 3 keyId from test order by keyId desc  )as testExtend)  order by keyId desc 
	 --方法3:开窗函数 row_number 重新排序后,然后使用between
	 select * from (
	  select *,row_number() over(order by keyId desc ) as keyId2 from test ) as testExtend 
	  where testExtend.keyId2 between 4 and 6 
-------------------------------------------------------------------------
--面试2:请估算以下脚本的运行结果 ---考察对count的掌握
  select count(*) from test 
  select count(info) from test 
  ---分别输出 12 和10
  go
 ------------------------------------------------------------------------
 --面试3:获取info列重复超过2条的数据 ---考察对聚合函数筛选的用法
 select * from test where info in (
 select info from test group by info  having count(info)>2
 )
    
  go 
  truncate table test 
  drop table test