贫困农户系统中常用命令.doc

1、批量删除 2010 年数据SET XACT_ABORT ON BEGIN TRANSACTION DELETE FROM RegisterTable WHERE TID%100 = 10;DELETE FROM FamilyMember WHERE TID%100 = 10;DELETE FROM RequirementProject WHERE TID%100 = 10;DELETE FROM SupportProject WHERE TID%100 = 10;IF error!=0ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION批量删除 2012 年

2、数据SET XACT_ABORT ON BEGIN TRANSACTION DELETE FROM RegisterTable WHERE TID%100 = 12;DELETE FROM FamilyMember WHERE TID%100 = 12;DELETE FROM RequirementProject WHERE TID%100 = 12;DELETE FROM SupportProject WHERE TID%100 = 12;IF error!=0ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION删除年份重复DELETE FROM Fami

3、lyMemberWHERE TID%100 = 11 AND MemberID IN(SELECT MemberID FROM FamilyMember AWHERE EXISTS(SELECT * FROM FamilyMember B WHERE B.MemberID!=A.MemberID AND substring(A.MemberID, 1, 16) = substring(B.MemberID, 1, 16) AND substring(A.MemberID, 19, 4) = substring(B.MemberID, 19, 4)DELETE FROM RequirementP

4、rojectWHERE TID%100 = 11 AND ProjectID IN(SELECT ProjectID FROM RequirementProject AWHERE EXISTS(SELECT * FROM RequirementProject B WHERE B.ProjectID!=A.ProjectID AND substring(A.ProjectID, 1, 16) = substring(B.ProjectID, 1, 16) AND substring(A.ProjectID, 19, 4) = substring(B.ProjectID, 19, 4)编码重复SE

5、LECT * FROM RegisterTable AWHERE TID%100 !=10 AND EXISTS(SELECT * FROM RegisterTable B WHERE B.FarmerID=A.FarmerID AND B.TID != A.TID)省扶贫标准批量修改成国家标准：UPDATE RegisterTable SET SupportLevel= 1 WHERESupportLevel=2去掉不同年份建档重复户- 注意 :执行前做好数据备份SET XACT_ABORT ON BEGIN TRANSACTION UPDATE RegisterTable SET Exte

6、ndCharField7 = chongfu WHERE TID IN(SELECT TID FROM vuRegisterTable AWHERE EXISTS(SELECT TID FROM vuRegisterTable B WHERE B.TID=16 AND Health=17、在校生的劳动力状况由无劳力修改为劳动力，然后把打工状况修改为其他UPDATE FamilyMember SET WorkPlace = 5, Workable = 1 WHERE School = 18. 文化程度为学龄前儿童的人员， 劳动力状况改为 3 无劳动能力,打工状况改为 5 其它 UPDATE Fa

7、milyMember SET WorkPlace = 5, Workable = 3 WHERE Education = 79.将所有家庭人员劳动力状况为“2 丧失劳动力”的打工状况修改为“5 其它”UPDATE FamilyMember SET WorkPlace = 5 WHERE Workable = 2;10. 是否公开家庭信息 否改为是UPDATE RegisterTable SET PulicInfo = 1 WHERE PulicInfo=211. 是否计划生育户 否改为是UPDATE RegisterTable SET JihuaShengyu= 1 WHERE JihuaSh

8、engyu=212、 性别错误批量修复UPDATE FamilyMember SET Sex = dbo.fn_GetSexFromIDC(IDNumber) WHERE dbo.fn_ValidateIDC(IDNumber)=113、年龄错误批量修复UPDATE FamilyMember SET Age = dbo.fn_GetAgeFromIDCAndYear(IDNumber,2010) WHERE Age!= dbo.fn_GetAgeFromIDCAndYear(IDNumber,2010) AND dbo.fn_ValidateIDC(IDNumber)=1 14、人均纯收入没有

9、填写怎么查出来SELECT * FROM RegisterTable where averageincome is null;15、将人均纯收入没填的修改为 0UPDATE RegisterTable SET AverageIncome = 0 WHERE AverageIncome IS NULL16、没有人口的查询SELECT (SELECT COUNT(*) FROM FamilyMember WHERE TID = RegisterTable.TID) AS 实际人口数, FamilyMemberCount as 填写人口数, RegisterTable.* FROM Register

10、Table WHERE FamilyMemberCount != (SELECT COUNT(*) FROM FamilyMember WHERE TID = RegisterTable.TID)找人均收入高于 1W 的 ,或者低于 100 的select * from RegisterTable where AverageIncome 10000 ro AverageIncome 100清除注册信息delete from sysconfig where (Name IN (Inited, RegionName, RegionId)换村命令命令作用：数据录错村的情况下，从错误的村转到正确的村。

11、以下命令将把户表数据从 A 村（错误的村）换到 B 村（正确的村）例如: A 村编码为 110101001001，B 村编码为 110101001002，则在命令执行器中，依次执行下面四条命令注意： 使用命令前一定要先备份好数据！注意： 该命令需要 B 村没有数据才能执行注意： 执行过程中出现执行失败，请立即还原数据UPDATE RegisterTable SET FarmerID = 110101001002 * 10000 + FarmerID % 10000,RegionID = 110101001002 ,TID = 110101001002 * 1000000 + TID % 100

12、0000,Village = (SELECT Name FROM RegionCode WHERE RegionID = 110101001002)WHERE FarmerID / 10000 = 110101001001;UPDATE FamilyMember SET TID = (110101001002 * 1000000 + TID % 1000000 ),MemberID = CAST(110101001002 * 1000000 + TID % 1000000 ) as char(18) + SUBSTRING(MemberID, 19,4)WHERE TID /100 / 100

13、00 = 110101001001;UPDATE RequirementProject SET TID = (110101001002 * 1000000 + TID % 1000000 ),ProjectID = CAST(110101001002 * 1000000 + TID % 1000000 ) as char(18) + SUBSTRING(ProjectID, 19,4)WHERE TID /100 / 10000 = 110101001001;UPDATE SupportProject SET TID = (110101001002 * 1000000 + TID % 1000000 ),ProjectID = CAST(110101001002 * 1000000 + TID % 1000000 ) as char(18) + SUBSTRING(ProjectID, 19,4)WHERE TID /100 / 10000 = 110101001001;批量修改受扶持年份命令（举例是 2011 年）update dbo.supportproject set supportyear = 2011没受扶持的,受扶持年份为空update dbo.supportproject set supportyear = null where name = null