《2022年贫困农户信息管理系统常用命令 .pdf》由会员分享，可在线阅读，更多相关《2022年贫困农户信息管理系统常用命令 .pdf（7页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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!=0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION 批量删除
2、2012 年数据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!=0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION 名师资料总结 - -
3、 -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - 删除年份重复DELETE FROM FamilyMember WHERE TID%100 = 11 AND MemberID IN ( SELECT MemberID FROM FamilyMember A WHERE EXISTS (SELECT * FROM FamilyMember B WHERE B.MemberID!=A.MemberID AND substring(A.MemberID,
4、1, 16) = substring(B.MemberID, 1, 16) AND substring(A.MemberID, 19, 4) = substring(B.MemberID, 19, 4) ) DELETE FROM RequirementProject WHERE TID%100 = 11 AND ProjectID IN ( SELECT ProjectID FROM RequirementProject A WHERE EXISTS (SELECT * FROM RequirementProject B WHERE B.ProjectID!=A.ProjectID AND
5、substring(A.ProjectID, 1, 16) = substring(B.ProjectID, 1, 16) AND substring(A.ProjectID, 19, 4) = substring(B.ProjectID, 19, 4) ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - 编码重复SELECT * FROM RegisterTable A WHERE TID%100 !=10 AND EXIST
6、S(SELECT * FROM RegisterTable B WHERE B.FarmerID=A.FarmerID AND B.TID != A.TID) 省扶贫标准批量修改成国家标准：UPDATE RegisterTable SET SupportLevel= 1 WHERE SupportLevel=2 去掉不同年份建档重复户- 注意 :执行前做好数据备份SET XACT_ABORT ON BEGIN TRANSACTION UPDATE RegisterTable SET ExtendCharField7 = chongfu WHERE TID IN ( SELECT TID FRO
7、M vuRegisterTable A WHERE EXISTS(SELECT TID FROM vuRegisterTable B WHERE B.TIDA.TID AND B.MasterName = A.MasterName AND B.MasterIDNumber = A.MasterIDNumber 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - ) ); DELETE FROM FamilyMember WHERE
8、TID IN (SELECT TID FROM RegisterTable WHERE ExtendCharField7 = chongfu); DELETE FROM RequirementProject WHERE TID IN (SELECT TID FROM RegisterTable WHERE ExtendCharField7 = chongfu); DELETE FROM SupportProject WHERE TID IN (SELECT TID FROM RegisterTable WHERE ExtendCharField7 = chongfu); DELETE FROM
9、 RegisterTable WHERE TID IN (SELECT TID FROM RegisterTable WHERE ExtendCharField7 = chongfu); IF error!=0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION 1、批量修改养老保险命令是变为否 ：UPDATE RegisterTable SET YanglaoBaoxian = 2 WHERE YanglaoBaoxian =1 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理
10、 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - 2、批量修改合作医疗命令否变为是 ：UPDATE RegisterTable SET HezuoYiliao = 1 WHERE HezuoYiliao =2 3、批量修改扶贫标准命令国家扶贫标准改为省级扶贫标准：UPDATE RegisterTable SET SupportLevel= 2 WHERE SupportLevel=1 4、批量修改农民专业合作经济组织命令是变为否 ：UPDATE RegisterTable SET NongheZuzhi= 2 WHERE NongheZuzhi =1 5
11、、16周岁以下人员全部批量修改为无劳动力的命令UPDATE FamilyMember SET Workable = 3 WHERE Age =16 AND Health=1 7、在校生的劳动力状况由无劳力修改为劳动力，然后把打工状况修改为其他UPDATE FamilyMember SET WorkPlace = 5, Workable = 1 WHERE School = 1 8. 文化程度为学龄前儿童的人员，劳动力状况改为3 无劳动能力 ,打工状况改为5其它名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - -
12、- - - - - 第 5 页，共 7 页 - - - - - - - - - UPDATE FamilyMember SET WorkPlace = 5, Workable = 3 WHERE Education = 7 9.将所有家庭人员劳动力状况为“2 丧失劳动力”的打工状况修改为“5 其它”UPDATE FamilyMember SET WorkPlace = 5 WHERE Workable = 2; 10. 是否公开家庭信息否改为是UPDATE RegisterTable SET PulicInfo = 1 WHERE PulicInfo=2 11. 是否计划生育户否改为是UPDA
13、TE RegisterTable SET JihuaShengyu= 1 WHERE JihuaShengyu=2 12、 性别错误批量修复UPDATE FamilyMember SET Sex = dbo.fn_GetSexFromIDC(IDNumber) WHERE dbo.fn_ValidateIDC(IDNumber)=1 13、年龄错误批量修复UPDATE FamilyMember SET Age = dbo.fn_GetAgeFromIDCAndYear(IDNumber,2010) WHERE Age!= dbo.fn_GetAgeFromIDCAndYear(IDNumber
14、,2010) AND dbo.fn_ValidateIDC(IDNumber)=1 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - 人均纯收入没有填写怎么查出来SELECT * FROM RegisterTable where averageincome is null; 命令名称： 导出扶贫户家庭人员信息命令说明 : 查询扶贫户信息，包含省、市、县、乡、村、社、姓名，姓别、身份证件号、电话号码，查询后可导出使用说明： 将下面的
15、命令复制到系统设置 - 命令执行器， 然后点 “ 执行 ” 。执行完后， 点“ 导出 Excel ”SELECT Province AS 省,City AS 市,County AS 县,Town AS 乡镇 ,Village AS 村,Team AS 村民小组 ,Name AS 姓名 , CASE WHEN Sex=1 THEN 男 ELSE 女 END AS 性别 , IDNumber AS 身份证 , Tel1 AS 电话FROM vuFamilyMemberDetail WHERE FarmerType = 1名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -