贫困农户信息管理系统常用命令

批量删除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

批量删除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

删除年份重复

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, 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

substring(A.ProjectID, 1, 16) = substring(B.ProjectID, 1, 16) AND substring(A.ProjectID, 19, 4) = substring(B.ProjectID, 19, 4))

)

编码重复

SELECT * FROM RegisterTable A

WHERE TID%100 !=10 AND EXISTS(SELECT * FROM RegisterTable B WHERE B.FarmerID=A.FarmerID AND B.TID != A.TID)

省扶贫标准批量修改成国家标准：

UPDA TE RegisterTable SET SupportLevel= 1 WHERE SupportLevel=2

去掉不同年份建档重复户

-- 注意:执行前做好数据备份

SET XACT_ABORT ON

BEGIN TRANSACTION

UPDA TE RegisterTable SET ExtendCharField7 = 'chongfu' WHERE TID IN

(

SELECT TID FROM vuRegisterTable A

WHERE EXISTS(SELECT TID FROM vuRegisterTable B

WHERE B.TID

)

);

DELETE FROM FamilyMember WHERE 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 RegisterTable WHERE TID IN

(SELECT TID FROM RegisterTable WHERE ExtendCharField7 = 'chongfu');

IF @@error!=0

ROLLBACK TRANSACTION

ELSE COMMIT TRANSACTION

1、批量修改养老保险命令{是变为否}：

UPDA TE RegisterTable SET Y anglaoBaoxian = 2 WHERE Y anglaoBaoxian =1

2、批量修改合作医疗命令{否变为是}：

UPDA TE RegisterTable SET HezuoYiliao = 1 WHERE HezuoYiliao =2

3、批量修改扶贫标准命令{国家扶贫标准改为省级扶贫标准}：UPDA TE RegisterTable SET SupportLevel= 2 WHERE SupportLevel=1

4、批量修改农民专业合作经济组织命令{是变为否}：

UPDA TE RegisterTable SET NongheZuzhi= 2 WHERE NongheZuzhi =1

5、16周岁以下人员全部批量修改为无劳动力的命令

UPDA TE FamilyMember SET Workable = 3 WHERE Age < 16

6. 16周岁以上身体健康人员全部批量修改为有劳动力的命令UPDA TE FamilyMember SET Workable = 1 WHERE Age>=16 AND Health=1

7、在校生的劳动力状况由无劳力修改为劳动力，然后把打工状况修改为其他

UPDA TE FamilyMember SET WorkPlace = 5, Workable = 1 WHERE School = 1

8. 文化程度为学龄前儿童的人员，劳动力状况改为3无劳动能力,打工状况改为5其它

UPDA TE FamilyMember SET WorkPlace = 5, Workable = 3 WHERE Education = 7

9.将所有家庭人员劳动力状况为“2丧失劳动力”的打工状况修改为“5其它”

UPDA TE FamilyMember SET WorkPlace = 5 WHERE Workable = 2;

10. 是否公开家庭信息否改为是

UPDA TE RegisterTable SET PulicInfo = 1 WHERE PulicInfo=2

11. 是否计划生育户否改为是

UPDA TE RegisterTable SET JihuaShengyu= 1 WHERE JihuaShengyu=2

12、性别错误批量修复

UPDA TE FamilyMember SET Sex = dbo.fn_GetSexFromIDC(IDNumber) WHERE dbo.fn_V alidateIDC(IDNumber)=1

13、年龄错误批量修复

UPDA TE FamilyMember SET Age = dbo.fn_GetAgeFromIDCAndY ear(IDNumber,2010) WHERE Age!= dbo.fn_GetAgeFromIDCAndY ear(IDNumber,2010) AND dbo.fn_V alidateIDC(IDNumber)=1

人均纯收入没有填写怎么查出来

SELECT * FROM RegisterTable where averageincome is null;

命令名称：导出扶贫户家庭人员信息

命令说明:查询扶贫户信息，包含省、市、县、乡、村、社、姓名，姓别、身份证件号、电话号码，查询后可导出

使用说明：将下面的命令复制到系统设置-> 命令执行器，然后点“执行”。执行完后，点“导出Excel”

SELECT Province AS 省,City AS 市,County AS 县,Town AS 乡镇,V illage AS 村,Team AS 村民小组,Name AS 姓名,

CASE WHEN Sex=1 THEN '男' ELSE '女' END AS 性别,

IDNumber AS 身份证,

Tel1 AS 电话

FROM vuFamilyMemberDetail

WHERE FarmerType = 1