请教一个复杂点的sql语句
有一个要写入数据的表IndustryScore结构：
IndustryCodenvarchar
CapableIdint
KeyIdint
MaxScorefloat
MinScorefloat
AvaScorefloat
其中前三个是主键，
通过
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操作，如何用一条语句来写成，谢谢，在线等。。。
[解决办法]
Create Proc dbo.P_WriteIndustryScore
as
set nocount on
Insert Into IndustryScore
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
[解决办法]
1、insert
insert into IndustryScore
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
