sql 高手看看这段代码，在线等，20分

来源：百度知道 编辑：UC知道 时间：2024/05/12 10:06:47
insert into dpur36 (nyf,cjhy,cwlh,cscbm,chtbs,njhsl,nkcsl,nxysl,nqrbs,nbh)
select distinct a.nyf,b.cjhy,b.cwlh,b.cscbm,b.chtbs,sum(a.njhtrsl) njhsl,sum(c.nkcsl) nkcsl,sum(a.njhtrsl) nxysl,0,1
from dmps61 a,dinv90 b,dinv01 c ,dinv80 d
where a.cwlh+a.cscbm+a.chtbs=b.cwlh+b.cscbm+b.chtbs and b.cwlh+b.cscbm+b.chtbs=c.cwlh+c.cscbm+c.chtbs and c.ckh = d.cckdm and d.clx in ('E','T') and
a.nnf=(select max(dmps61.nnf) from dmps61) and
a.nyf=(select max(dmps61.nyf) from dmps61 where dmps61.nnf=(select max(dmps61.nnf)
from dmps61))
group by a.nyf,b.cjhy,b.cwlh,b.cscbm,b.chtbs
union
select distinct a.nyf,b.cjhy,b.cwlh,b.cscbm,b.chtbs,a.njhtrsl,0 nkcsl,a.njhtrsl,0,1
from #a a,dinv90 b
where a.cwlh+a.cscbm+a.chtbs=b.cwlh+b.cscbm+b.chtbs and
a.nnf=(select max(dmps61.nnf) from dmps61) and
a.nyf=(select max(dmps61.nyf) from dmps61 where dmps61.nnf=(select max(dmps61.nnf)
from dmps61))
出现的问题是违反主键约束，不能插入重复

你先用这个查询一下.

select distinct a.nyf,b.cjhy,b.cwlh,b.cscbm,b.chtbs,sum(a.njhtrsl) njhsl,sum(c.nkcsl) nkcsl,sum(a.njhtrsl) nxysl,0,1
from dmps61 a,dinv90 b,dinv01 c ,dinv80 d
where a.cwlh+a.cscbm+a.chtbs=b.cwlh+b.cscbm+b.chtbs and b.cwlh+b.cscbm+b.chtbs=c.cwlh+c.cscbm+c.chtbs and c.ckh = d.cckdm and d.clx in ('E','T') and
a.nnf=(select max(dmps61.nnf) from dmps61) and
a.nyf=(select max(dmps61.nyf) from dmps61 where dmps61.nnf=(select max(dmps61.nnf)
from dmps61))
group by a.nyf,b.cjhy,b.cwlh,b.cscbm,b.chtbs
union
select distinct a.nyf,b.cjhy,b.cwlh,b.cscbm,b.chtbs,a.njhtrsl,0 nkcsl,a.njhtrsl,0,1
from #a a,dinv90 b
where a.cwlh+a.cscbm+a.chtbs=b.cwlh+b.cscbm+b.chtbs and
a.nnf=(select max(dmps61.nnf) from dmps61) and
a.nyf=(select max(dmps61.nyf) from dmps61 where dmps61.nnf=(select max(dmps61.nnf)
from dmps61))

看有没有同样的记录..他给你这个提示.肯定是你重复插入了数据..

------------------