34,575
社区成员
发帖
与我相关
我的任务
分享
--检查临时表
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempwk'))
Drop table #tempwk
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempPer'))
Drop table #tempPer
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempList'))
Drop table #tempList
-- 周一开始
select fDate into #tempwk from (
select DATEADD(wk,DATEDIFF(wk,0,getdate()),0) fDate
union
select DATEADD(wk,DATEDIFF(wk,0,getdate()),1)
union
select DATEADD(wk,DATEDIFF(wk,0,getdate()),2)
union
select DATEADD(wk,DATEDIFF(wk,0,getdate()),3)
union
select DATEADD(wk,DATEDIFF(wk,0,getdate()),4)
union
select DATEADD(wk,DATEDIFF(wk,0,getdate()),5)
union
select DATEADD(wk,DATEDIFF(wk,0,getdate()),6)
) T
--人员
select fName into #tempPer from (
select '张三' fName
union
select '李四'
) T
select fDate, fName into #tempList from #tempwk,#tempPer
--人员
insert into #tempPer(fName) values('王五')
--周人员列表
insert into #tempList(fName,fDate) values('王五','2013-04-22')
insert into #tempList
select fDate, fName from #tempwk,#tempPer
where ???(这里如何不重复插入数据 1.张三、李四不重复插入 2.王五 22号不重复插入)
select * from #tempList
insert into #tempList
select a.fDate, b.fName
from #tempwk a,#tempPer b
where not exists(select 1 from #tempList c where c.fName=b.fName and c.fDate=a.fDate)