有一个要写入数据的表IndustryScore结构:
IndustryCode nvarchar
CapableId int
KeyId int
MaxScore float
MinScore float
AvaScore float
其中前三个是主键,
通过
SELECT vocationCode, CapId, KeyId,MAX(Data) AS maxS, MIN(Data) AS minS, AVG(Data) AS avgS
FROM CompanyKeyData
GROUP BY CapId, KeyId, vocationCode
检索出的结果要写入IndustryScore,如果IndustryCode,CapableId, KeyId三个值数据库中已经有相同的了,则对相应的值进行修改,如果
没有,则执行insert操作,如何用一条语句来写成,谢谢,在线等
IndustryCode nvarchar
CapableId int
KeyId int
MaxScore float
MinScore float
AvaScore float
其中前三个是主键,
通过
SELECT vocationCode, CapId, KeyId,MAX(Data) AS maxS, MIN(Data) AS minS, AVG(Data) AS avgS
FROM CompanyKeyData
GROUP BY CapId, KeyId, vocationCode
检索出的结果要写入IndustryScore,如果IndustryCode,CapableId, KeyId三个值数据库中已经有相同的了,则对相应的值进行修改,如果
没有,则执行insert操作,如何用一条语句来写成,谢谢,在线等
SELECT vocationCode, CapId, KeyId,MAX(Data) AS maxS, MIN(Data) AS minS, AVG(Data) AS avgS
FROM CompanyKeyData as pqh
Where not Exists(select 1 from IndustryScore as pqs
where pqh.IndustryCode=pqs.vocationCode
and pqs.CapableId=pqh.CapId and pqs.KeyId=pqh.KeyId)
GROUP BY pqh.CapId,pqh.KeyId,pqh.vocationCode
update IndustryScore
set MaxScore=MAX(Data),MinScore=MIN(Data),AvaScore=AVG(Data)
FROM CompanyKeyData
where
CompanyKeyData.IndustryCode=IndustryScore.vocationCode
and IndustryScore.CapableId=CompanyKeyData.CapId
and CompanyKeyData.KeyId=IndustryScore.KeyId)
GROUP BY CompanyKeyData.CapId, CompanyKeyData.KeyId, CompanyKeyData.vocationCode
set nocount off
go
select * from
(
SELECT vocationCode, CapId, KeyId,MAX(Data) AS maxS, MIN(Data) AS minS, AVG(Data) AS avgS
FROM CompanyKeyData
GROUP BY CapId, KeyId, vocationCode
) t
where cast(vocationCode as varchar) + cast(CapId as varchar) + cast(KeyId as varchar) not in
(select cast(IndustryCode as varchar) + cast(CapableId as varchar) + cast(KeyId as varchar) from IndustryScore) 2、update
update IndustryScore
set MaxScore = t.maxS,
MinScore = t.minS,
AvaScore = t.avgs
from IndustryScore m,
(
SELECT vocationCode, CapId, KeyId,MAX(Data) AS maxS, MIN(Data) AS minS, AVG(Data) AS avgS
FROM CompanyKeyData
GROUP BY CapId, KeyId, vocationCode
) t
where m.IndustryCode = t.vocationCode and m.CapableId = t.CapId and m.KeyId = t.KeyId
比如这样:select ……insert newtable form table1 left join table2 on table1。field=table2.field
修改一下 ,我没有测试,理论上没有问题了Create Proc dbo.P_WriteIndustryScoreasset nocount on--先把聚合的数据写到临时表里
SELECT vocationCode, CapId as Cap_Id, KeyId as Key_Id,
MAX(Data) AS maxS,
MIN(Data) AS minS,
AVG(Data) AS avgS Into #Pqh
FROM CompanyKeyData
GROUP BY CapId, KeyId, vocationCodeInsert Into IndustryScore
SELECT * FROM #Pqh as pqh
Where not Exists(select 1 from IndustryScore as pqs
where pqh.IndustryCode=pqs.vocationCode
and pqs.CapableId=pqh.CapId and pqs.KeyId=pqh.KeyId)update IndustryScore
set
MaxScore=maxS,
MinScore=minS,
AvaScore=avgS
FROM #Pqh
where IndustryCode=vocationCode
and CapableId=Cap_Id
and KeyId=Key_Id)
Truncate Table #Pqh
Drop Table #Pqhset nocount off
go