| 解决方案： |
查询重复产品结构：
SELECT cpspcode,cpscode,count(cpscode)
FROM ProductStructures
group by cpspcode,cpscode having count(cpscode)>1
1、删除产品结构子表ProductStructures中重复记录
--查询重复记录：
SELECT cpspcode,cpscode,max(autoid) as autoid
into tmpPS
FROM ProductStructures
group by cpspcode,cpscode having count(cpscode)>1
--删除错误重复记录
delete from ProductStructures
where autoid in ( select b.autoid
from tmpps a
join ProductStructures b on a.cpspcode=b.cpspcode and a.cpscode=b.cpscode
where a.autoid<>b.autoid)
--删除临时表
drop table tmpps
2、产品结构简化子表ProductStructuresEX中记录数不等于产品结构子表ProductStructures：
SELECT count(*) FROM ProductStructures :共105130条记录；
SELECT count(*) FROM ProductStructuresEx:共105124条记录，相差6笔记录；
--通过构建临时表查询不符记录：
select cpspcode,count(cpscode) as ct
into t
from ProductStructures
group by cpspcode
select cpspcode,count(cpscode) as ct
into tEx
from ProductStructuresEx
group by cpspcode
select *
from t
join tex on t.cpspcode=tex.cpspcode
where t.ct<>tex.ct
--获取错误父项编码cpspcode='3176F2540DYX100')，并删除临时表；
drop table t drop table tex
--查询，查询结果中显示子表比简化子表多出6个存货子项:
select *
from productstructures
where cpscode not in (select distinct cpscode from productstructuresex where cpspcode='3176F2540DYX100')
and cpspcode='3176F2540DYX100'
(请与用户协商确认后删除或对简化子表补充缺少的6笔记录，下列语句供参考)
删除（productstructures）：
delete from productstructures
where cpscode not in (select distinct cpscode from productstructuresex where cpspcode='3176F2540DYX100')
and cpspcode='3176F2540DYX100'
补充（productstructuresex）：
insert into productstructuresEX(cpspcode,cpscode,ipsquantity)
select cpspcode,cpscode,ipsquantity
from productstructures
where cpscode not in (select distinct cpscode from productstructuresex where cpspcode='3176F2540DYX100')
and cpspcode='3176F2540DYX100'
3、检查，此时查询产品结构数据主子表及相应简化表，数据相符且无重复记录。
SELECT count(*)
FROM ProductStructures
SELECT count(*)
FROM ProductStructuresEx
SELECT count(*)
FROM ProductStructure
SELECT count(*)
FROM ProductStructureEx |