mssql sqlserver 面试题-基础脚本编写(sql脚本编写)-3


摘要:
下文为公司最近根据实际业务编写的sql基础脚本面试16例,也是基本的sql脚本操作,您学习sql必须知道的知识,供大家参阅
主要考察 group by、having、where、连接、row_number 、聚合函数、插入数据、删除数据、查询操作
实验环境:sqlserver 2008 R2


 
  create table [用户表](userId int,userName nvarchar(10))
  create table [客户表](cusId int,cusName nvarchar(10))
  create table [维修主表](userId int,cusId int,writeDate datetime,billNo varchar(30),JinE decimal(18,2)) 
   go

   ---生成数据
   insert into [用户表](userId,userName) values(1,'张三'),(2,'猫猫'),(3,'经理')

   insert into [客户表](cusId ,cusName)values(1,'麻花藤'),(2,'东哥'),(6,'顺子')

   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(1,1,'2018-5-17 10:00:02','A01',100)
   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(1,2,'2018-5-17 11:02:02','A02',200)
   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(2,1,'2018-5-18 10:00:02','A03',30)
   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(1,1,'2018-5-19 10:00:02','A04',60) 
   insert into [维修主表](userId,cusId,writeDate,billNO,JinE)values(1,2,'2018-5-20 10:00:02','A05',60) 
   go
  
--面试1: 获取用户2018年5月接待客户总单数和总金额
    select count(distinct cusId) as [总接待客户],sum(jinE) as [总金额] from [维修主表] where writeDate between  '2018-5-1 0:00:00' and '2018-5-31 23:59:59' 
--面试2: 获取2018年5月来店总次数大于2次的客户
 select a.* from  [客户表] a 
 left join 
 (
select *,row_number() over(partition by cusId order by userId) as keyId
 from [维修主表] where writeDate between  '2018-5-1 0:00:00' and '2018-5-31 23:59:59' ) as b
 on  a.cusId =b.cusId 
 where b.keyId>2 
--面试3: 获取客户名等于"东哥"的维修记录信息
select a.*,b.cusName from [维修主表] a right join [客户表] b on a.cusId =b.cusId  where  b.cusName ='东哥'
--面试4: 获取维修金额大于100的客户信息
    select b.cusName,b.cusId from [维修主表] a right join [客户表] b on a.cusId =b.cusId  where  a.jine>100
--面试5: 获取未来店维修的客户信息
select * from [客户表] where not exists(select null from [维修主表] where [维修主表].cusId =[客户表].cusId)
--面试6: 获取同时被两个及以上店员接待过的客户信息
select * from [客户表] 
where exists(
 select null from  (select count(distinct userId) as userQty,cusId from [维修主表] group by cusId  
  having count(distinct userId)>=2)   t where t.cusId = [客户表].cusId 
 )

--面试7: 获取未接待过客户的店员信息
  select * from [用户表] 
   where not exists(
      select null from [维修主表] where [维修主表].userId = [用户表].userId
   )
   
--面试8: 生成2018年5月店员接待客户的业绩报表
  select a.*,isnull(b.[业绩],0) as [业绩]
     from [用户表]  a left join 
       (
           select  userId ,sum(jinE) as [业绩] 
           from [维修主表]  where writeDate between  '2018-5-1 0:00:00' and '2018-5-31 23:59:59'  group by userId 
        ) as b 
    on a.userId =b.userId  
    
--面试9: 获取2018年5月客户平均消费金额
   select a.cusId,a.cusName,avg(isnull(b.[业绩],0)) as [平均业绩]
     from [客户表]  a left join 
       (
           select  cusId,sum(jinE) as [业绩] 
           from [维修主表]  where writeDate between  '2018-5-1 0:00:00' and '2018-5-31 23:59:59'  group by cusId 
        ) as b 
    on a.cusId =b.cusId  
    group by a.cusId,a.cusName
--面试10: 删除维修单号等于"A04"的维修记录
 delete from [维修主表] where billNo ='A04'
--面试11:获取不同客户,但维修金额相同的维修信息及客户信息
    select * from [维修主表] c
   left join  [客户表] d on c.cusId =d.cusId
   where exists
   (
   select null from [维修主表] a
    left join [维修主表] b on a.cusId !=b.cusId and a.JinE =b.JinE
      where b.billNO =c.billNO
   ) 
--面试12:获取维修客户总数
select count(distinct cusId) as [客户总数] from [维修主表] 
--面试13: 获取维修金额大于30的单据,并按维修金额倒序排列
   select *  from [维修主表] 
 where jinE >30 order by jine desc 
--面试14: 获取最大维修金额的维修记录数
   select * from 
   (
     select *,row_number() over (order by jine desc ) as keyId from [维修主表] 
     ) A where A.keyId=1
--面试15:删除店员 “猫猫”所属的维修记录
 delete from [维修主表] 
where exists(
 select null from [用户表] where [用户表].userName='猫猫' and [用户表].userId =[维修主表].userId
)
--面试16:获取维修单金额大于平均维修金额的维修记录
 select * from [维修主表] where jine>(
  select avg(JinE) from [维修主表]
 )
  

   ---清理表

 go
  drop table [维修主表] 
  drop table  [客户表]
  drop table [用户表]