業(yè)務(wù)初始化數(shù)據(jù)驗(yàn)證方案

1、業(yè)務(wù)數(shù)據(jù)初始化驗(yàn)證方案一、驗(yàn)證是否新增字段檢查CC02表，增加如下2個(gè)字段。1、市屬區(qū)屬(city_owned):是指是否屬于市局結(jié)算人員。是：true，否： false。 正確2、原發(fā)證網(wǎng)點(diǎn)(oldcnet):備份cnet字段，以便備查，區(qū)分2012年1月1 日之前的市屬區(qū)屬人員。正確二、檢查市屬區(qū)屬的正確性、完整性1、正確性檢索市屬區(qū)屬為空的記錄。正確返回為0不為0語(yǔ)句：select * from cc02 where city_owned is null or city_owned=分析：有25筆記錄，只有失業(yè)證號(hào)、身份證號(hào)、cstate=3為失效、其他 信息字段都為空檢索市屬區(qū)屬二tr

2、ue且原發(fā)證網(wǎng)點(diǎn)不等于市就業(yè)服務(wù)中心的記錄數(shù)。 正確返回為0正確select count(*) from cc02 where city_owned=1 and substr(oldcnet,1,2)00;檢索市屬區(qū)屬二false且原發(fā)證網(wǎng)點(diǎn)等于市就業(yè)服務(wù)中心的記錄數(shù)。正 確返回為0正確select count(*) from cc02 where city_owned=0 and substr(oldcnet,1,2)=00;檢索發(fā)證網(wǎng)點(diǎn)所在區(qū)不等于戶(hù)籍區(qū)的記錄數(shù)。正確返回為0或者戶(hù)籍 區(qū)為空。不為0語(yǔ)句：select cnet,carea,cstree,oldcnet,CINPUTD fr

3、om cc02 where substr (cnet,1,2) substr (carea,1,2) and city_owned=1 and carea is not null and carea分析：失業(yè)登記有bug,戶(hù)籍行政區(qū)與街道所在行政區(qū)不一致檢索發(fā)證網(wǎng)點(diǎn)所在街道不等于戶(hù)籍街道的記錄數(shù)。正確返回為0或者 戶(hù)籍街道為空。正確語(yǔ)句：SELECT cnet,carea,cstree,oldcnet,CINPUTD,CINPUTFROM cc02WHEREsubstr (cnet,1,4) substr (cstree,1,4)ANDcity_owned =1ANDcarea IS NOTN

4、ULLANDcarea ;分析：有4筆記錄是測(cè)試數(shù)據(jù)2、完整性檢索記錄總數(shù)。330390select count(*) from cc02;檢索原發(fā)證網(wǎng)點(diǎn)等于市就業(yè)服務(wù)中心的記錄總數(shù)。136294select count(*) from cc02 where oldcnet=0000001;檢索市屬區(qū)屬二true的記錄總數(shù)136294select count(*) from cc02 where city_owned=1;檢索原發(fā)證網(wǎng)點(diǎn)等于市就業(yè)服務(wù)中心且戶(hù)籍區(qū)為空的記錄總數(shù)。0select count(*) from cc02 where substr(oldcnet,1,2)=00 and

5、 carea is null and carea ;檢索市屬區(qū)屬二false且戶(hù)籍區(qū)不為空的記錄總數(shù)。193873select count(*) from cc02 where city_owned=0 and carea is not null and carea ;=(2)+(5)330167 相等語(yǔ)句：selectcnet,carea,cstree,oldcnet,CINPUTD,CINPUT,cstate,a.aac021,b.aac021fromcc02 aleft join(select aac021 from cc02 where substr(oldcnet,1,2)=00 u

6、nionselect aac021 from cc02 where city_owned=0 and carea is not null and carea )b on a.aac021二b.aac021where b.aac021 is null and carea is not null and carea;0600000060601201 1-12-28 00:00:00.0吳華恩1G1105970-NULL0101010600000060601201 1-12-28 00:00:00.0吳華恩1G1025969-NULL0600000060601201 1-12-28 00:00:00

7、.0吳華恩1G1035969-NULL0300000030301201 1-12-28 00:00:00.0吳華恩4N111111126-NULL分析：分析：有4筆記錄是測(cè)試數(shù)據(jù)，去掉該4筆記錄，其中1筆失業(yè)證 號(hào)以X開(kāi)頭，狀態(tài)為失效，只有身份證、失業(yè)證號(hào)信息，其它信息為空。（2）=（3）+（4）136294 相等selectcnet,carea,cstree,oldcnet,CINPUTD,CINPUT,cstate,a.aac021,b.aac021 from（SELECT *FROM cc02WHERE city_owned = 1）a LEFT JOIN （SELECT aac021

8、FROM cc02 WHERE oldcnet = 0000001） b ON a.aac021 = b.aac021where b.aac021 is null;分析：有4筆記錄是測(cè)試數(shù)據(jù)，去掉該4筆記錄，結(jié)果是正確的三、檢查原發(fā)證網(wǎng)點(diǎn)的正確性檢索生產(chǎn)數(shù)據(jù)庫(kù)中的發(fā)證網(wǎng)點(diǎn)等于市就業(yè)服務(wù)中心的記錄總數(shù)。136294 select count(*) from cc02 where substr(cnet,1,2)=00;檢索測(cè)試數(shù)據(jù)庫(kù)中的原發(fā)證網(wǎng)點(diǎn)等于市就業(yè)服務(wù)中心的記錄總數(shù)。136294select count(*) from cc02 where substr(oldcnet,1,2)=00;

9、檢索生產(chǎn)數(shù)據(jù)庫(kù)中的發(fā)證網(wǎng)點(diǎn)等于南山區(qū)的記錄總數(shù)。28970select count(*) from cc02 where substr(cnet,1,2)=03;檢索測(cè)試數(shù)據(jù)庫(kù)中的原發(fā)證網(wǎng)點(diǎn)等于南山區(qū)的記錄總數(shù)。28970 select count(*) from cc02 where substr(oldcnet,1,2)=03;檢索生產(chǎn)數(shù)據(jù)庫(kù)中的發(fā)證網(wǎng)點(diǎn)等于南山區(qū)沙河街道的記錄總數(shù)。1458 select count(*) from cc02 where substr(cnet,1,4)=0304;檢索測(cè)試數(shù)據(jù)庫(kù)中的原發(fā)證網(wǎng)點(diǎn)等于南山區(qū)沙河街道的記錄總數(shù)。1458 select count(*) from cc02 where substr(oldcnet,1,4)=0304;檢索生產(chǎn)數(shù)據(jù)庫(kù)中的發(fā)證網(wǎng)點(diǎn)等于寶安區(qū)的記錄總數(shù)。32827select count(*) from cc02 where substr(cnet,1,2)=05;檢索測(cè)試數(shù)據(jù)庫(kù)中的原發(fā)證網(wǎng)點(diǎn)等于寶安區(qū)的記錄總數(shù)。32827 select count(*) from cc02 where substr(oldcnet,1,2)=05;檢索生產(chǎn)數(shù)據(jù)庫(kù)中的發(fā)證網(wǎng)點(diǎn)等于寶安區(qū)新安街道的記錄總數(shù)。12157 select count(*) from cc02 where