有点难度的面试题目

[flers]

题目：
　　有两个表，一个是jdjlb〔检定记录表〕，一个是khyqb(客户仪器),它们公共的字段是:jsdh,xh,khbh,yqmc,eyqmc,xhgg,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk
条件：KHBH[客户编号],yqmc[仪器名称],xhgg[型号规格],jsh[机身号]确定该记录在库中的唯一性。jdjlb〔检定记录表)每天更新，所以对一同一个客户来说有相同的仪器名称，并且以往的记录也存在这个记录表中。
要求：
先从jdjlb〔检定记录表)中得到该表中最新的记录，可根据jsdh最大的是最新来判定。然后分别与khyqb(客户仪器)中的记录进行对比，如果是完全相同则UPDATE字段，如果khyqb(客户仪器),不存在则INSERT，最主要的目的是保持khyqb(客户仪器)表不重复但字段又是最新的状态。
要求写存储过程，让程序在晚间更新调用。

[pengdali]

update khyqb set jsbh=tem.jsbh,xh=tem.xh,eyqmc=tem.eyqmc,yqzt=tem.yqzt,jdjg=tem.jdjg,zsbh=tem.zsbh,jzdate=tem.jzdate,yxq=tem.yxq,khmc=tem.khmc,sjrq=tem.sjrq,yqzk=tem.yqzk
from (
select jsdh,xh,khbh,yqmc,eyqmc,xhgg,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk from jdjlb tem where jdjlb=(select max(jdjlb) from jdjlb where KHBH=tem.KHBH and yqmc=tem.yqmc and xhgg=tem.xhgg and jsh=tem.jsh)
) tem
where khyqb.KHBH=tem.KHBH and khyqb.yqmc=tem.yqmc and khyqb.xhgg=tem.xhgg and khyqb.jsh=tem.jsh

insert khyqb (jsdh,xh,khbh,yqmc,eyqmc,xhgg,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk)
select jsdh,xh,khbh,yqmc,eyqmc,xhgg,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk from jdjlb tem where
not exists(select 1 from khyqb where KHBH=tem.KHBH and yqmc=tem.yqmc and xhgg=tem.xhgg and jsh=tem.jsh) and
jdjlb=(select max(jdjlb) from jdjlb where KHBH=tem.KHBH and yqmc=tem.yqmc and xhgg=tem.xhgg and jsh=tem.jsh)

[zjcxc]

--那删除后再新增不是更好?

--处理的存储过程
create proc p_proc
as
--更新已经存在的记录
update khyqb set jsdh=b.jsdh,xh=b.xh,eyqmc=b.eyqmc
,yqzt=b.yqzt,jdjg=b.jdjg,zsbh=b.zsbh,jzdate=b.jzdate
,yxq=b.yxq,khmc=b.khmc,sjrq=b.sjrq,yqzk=b.yqzk
from khyqb a inner join jdjlb b
on a.khbh=b.khbh and a.yqmc=b.yqmc and a.xhgg=b.xhgg and a.jsh=b.jsh
where jsdh=(select max(jsdh) from jdjlb
where khbh=b.khbh and yqmc=b.yqmc and xhgg=b.xhgg and jsh=b.jsh)

--插入不存在的记录
insert into khyqb(jsdh,xh,khbh,yqmc,eyqmc,xhgg
,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk)
select jsdh,xh,khbh,yqmc,eyqmc,xhgg
,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk
from jdjlb b where jsdh=(select max(jsdh) from jdjlb
where khbh=b.khbh and yqmc=b.yqmc and xhgg=b.xhgg and jsh=b.jsh)

[zjcxc]

--创建作业定时执行

--创建作业
exec msdb..sp_add_job @job_name='数据处理'

--创建作业步骤
declare @sql varchar(800),@dbname varchar(250)
select @sql='exec p_proc' --数据处理的命令
,@dbname=db_name() --执行数据处理的数据库名,需要换成存储过程所在的数据库

exec msdb..sp_add_jobstep @job_name='数据处理',
@step_name = '数据更新',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔

--创建调度
EXEC msdb..sp_add_jobschedule @job_name = '数据处理',
@name = '时间安排',
@freq_type = 4, --每天
@freq_interval = 1, --每天执行一次
@active_start_time = 00000 --0点执行

[pengdali]

update khyqb set jsbh=tem.jsbh,xh=tem.xh,eyqmc=tem.eyqmc,yqzt=tem.yqzt,jdjg=tem.jdjg,zsbh=tem.zsbh,jzdate=tem.jzdate,yxq=tem.yxq,khmc=tem.khmc,sjrq=tem.sjrq,yqzk=tem.yqzk
from (
select jsdh,xh,khbh,yqmc,eyqmc,xhgg,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk from jdjlb tem where jsdh=(select max(jsdh) from jdjlb where KHBH=tem.KHBH and yqmc=tem.yqmc and xhgg=tem.xhgg and jsh=tem.jsh)
) tem
where khyqb.KHBH=tem.KHBH and khyqb.yqmc=tem.yqmc and khyqb.xhgg=tem.xhgg and khyqb.jsh=tem.jsh

insert khyqb (jsdh,xh,khbh,yqmc,eyqmc,xhgg,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk)
select jsdh,xh,khbh,yqmc,eyqmc,xhgg,jsh,yqzt,jdjg,zsbh,jzdate,yxq,khmc,sjrq,yqzk from jdjlb tem where
not exists(select 1 from khyqb where KHBH=tem.KHBH and yqmc=tem.yqmc and xhgg=tem.xhgg and jsh=tem.jsh) and
jsdh=(select max(jsdh) from jdjlb where KHBH=tem.KHBH and yqmc=tem.yqmc and xhgg=tem.xhgg and jsh=tem.jsh)

[pnnm]

星星好耀眼哦
:）