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

发布时间：2012-06-16 11:49:37 来源：文档文库

小中大

字号：

手机查看

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

编码重复

SELECT * 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 ExtendCharField7 = 'chongfu' WHERE TID IN(SELECT TID FROM vuRegisterTable AWHERE EXISTS(SELECT TID FROM vuRegisterTable B WHERE B.TID

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

UPDATE RegisterTable SET YanglaoBaoxian = 2 WHERE YanglaoBaoxian =1

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、16周岁以下人员全部批量修改为无劳动力的命令

UPDATE FamilyMember SET Workable = 3 WHERE Age < 16

6. 16周岁以上身体健康人员全部批量修改为有劳动力的命令

UPDATE FamilyMember SET Workable = 1 WHERE Age>=16 AND Health=1

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

UPDATE FamilyMember SET WorkPlace = 5, Workable = 1 WHERE School = 1

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

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. 是否计划生育户否改为是

UPDATE 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,2010) AND dbo.fn_ValidateIDC(IDNumber)=1

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

SELECT * FROM RegisterTable where averageincome is null;

命令名称：导出扶贫户家庭人员信息
命令说明: 查询扶贫户信息，包含省、市、县、乡、村、社、姓名，姓别、身份证件号、电话号码，查询后可导出
使用说明：将下面的命令复制到系统设置-> 命令执行器，然后点“执行”。执行完后，点“导出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