内连接、外链接、嵌套查询解决人员信息一对一条记录的的实现----廊坊人事档案系统

我就是我 2022-09-17 13:22 102阅读 0赞

前几天系统从客户使用中反应出的问题是如图所示：

![Center][]

就是人员的信息会随着一个人的学籍、技术档案、档案工资、党员信息、计生信息的逐步完善后，数据条数变的越来越多，我们开发的时候没有考虑太细的（需求也有点问题），展现给用户的数据太多、较多的重复。

## 原因 ##

以基础信息的是一个人的为记录，而学籍成绩来说可能有多条刚开始是大专后、后获取了后取学历拿到本科，这样通过笛卡尔积、外连接等的产生了多条显示的信息，给用户造成了多条信息显示误解。（其他信息与上述原因相同，所以会产生一个人显示多大十几条详细的记录，造成了数据的大量冗余。）

米老师也给讲了，在这个系统中应该是以人为一个对象来展示才对，而多条记录的显示（以次数为对象）放到历史记录的变更来显示更好。

## 目的 ##

实现一人对照一条信息的显示（包含所有的信息）

## 解决的方案 ##

这些一条条的记录后面可以改成是信息的变更记录来显示（后面开发另加页面）

首先是去信息的重复，在一个这次使用到的知识是通过自考的数据库系统原理的数据库的连接（内连接、外连接、左外连接、右外连接、DISTINCT函数等知识），这些知识以前知识简单的了解并没有深刻的去学习，这次算是深刻的学习了

代码实现：（以档案工资的不重复为例）

SELECT ID, name, sex, idNumber, unitProperties, kindOfSalary, practiceStartDate, internshipPeriod, conversionDate, station, postSalary, salaryRank, salaryRankSalary, adjustWhy, executeDate, performanceBasedPay, rewardingPerformance, subsidies, approvalDate, remark, nowoRiginalTitle, number, pri, state, admin, createTime FROM dbo.V_FileWages WHERE (number IN (SELECT MAX(number) AS number FROM dbo.V_FileWages AS V_FileWages_1 GROUP BY pri))

选择出输入某一个人的最新修改某一个方面的记录：先通过人员的外键（ GROUP BY pri）分组,在通过每组中查找属于某一个人的最新的一条记录就行了，想法很是简单，但是自己对SQL语句的熟练度太差了，在两天的测试中试了好多方法，这个解决的方法中学习使用in、sql语句的嵌套、分组等（很简单了，以前实践少，耽误点时间）。

## 综合信息查询中-信息显示实现 ##

客户要求：多个表联合查询，查询出属于一个人的所有信息的记录

SELECT dbo.V_XINFamilyPlanning.pregnancydate, dbo.V_XINFamilyPlanning.duedate, dbo.V_XINFamilyPlanning.planabout, dbo.V_XINFamilyPlanning.childtime, dbo.V_XINFamilyPlanning.birthcertificateid, dbo.V_XINFamilyPlanning.childname, dbo.V_XINFamilyPlanning.childsex, dbo.V_XINFamilyPlanning.childbirthday, dbo.V_XINFamilyPlanning.childid, dbo.V_XINFamilyPlanning.contraceptivemeasures, dbo.V_XINFamilyPlanning.contraceptivedate, dbo.V_XINFamilyPlanning.operationpalce, dbo.V_XINFamilyPlanning.logininformationfee, dbo.V_XINFamilyPlanning.state, dbo.V_XINFamilyPlanning.admin, dbo.V_XINFamilyPlanning.CreateTime, dbo.V_XINFamilyPlanning.basicnumber, dbo.V_XINFamilyPlanning.familysettime, dbo.V_XINFamilyPlanning.cardnum, dbo.V_XINFamilyPlanning.houserholderrelation, dbo.V_XINFamilyPlanning.category, dbo.V_XINFamilyPlanning.firstmarriagedate, dbo.V_XINFamilyPlanning.hushaistatus, dbo.V_XINFamilyPlanning.marriageregistrationdate, dbo.V_XINFamilyPlanning.weddingcardnumber, dbo.V_XINFamilyPlanning.entersystemdate, dbo.V_XINFamilyPlanning.outsystemdate, dbo.V_XINFamilyPlanning.causeofdropout, dbo.V_XINFamilyPlanning.spousename, dbo.V_XINFamilyPlanning.spouserelationshiphouseholder, dbo.V_XINFamilyPlanning.spousebirthday, dbo.V_XINFamilyPlanning.spouseid, dbo.V_XINFamilyPlanning.spousemaritalstatus, dbo.V_XINFamilyPlanning.spousefirstmarrigedate, dbo.V_XINFamilyPlanning.spousecategory, dbo.V_XINFamilyPlanning.spousenation, dbo.V_XINFamilyPlanning.spouseculture, dbo.V_XINFamilyPlanning.spouselive, dbo.V_XINFamilyPlanning.spousenativeplace, dbo.V_XINFamilyPlanning.spousehushaistatus, dbo.V_XINFamilyPlanning.spouseworkplace, dbo.V_XINFamilyPlanning.spouseentysystemdate, dbo.V_XINFamilyPlanning.spouseoutsystemdate, dbo.V_XINFamilyPlanning.womantelephone, dbo.V_XINFamilyPlanning.mantelephone, dbo.V_XINFamilyPlanning.remarks AS fremarks, dbo.V_XINFamilyPlanning.number AS V_XINFamilyPlanningnumber, dbo.V_XINFamilyPlanning.createdate, dbo.V_XINFileWages.unitProperties, dbo.V_XINFileWages.kindOfSalary, dbo.V_XINFileWages.practiceStartDate, dbo.V_XINFileWages.internshipPeriod, dbo.V_XINFileWages.conversionDate, dbo.V_XINFileWages.station, dbo.V_XINFileWages.postSalary, dbo.V_XINFileWages.salaryRank, dbo.V_XINFileWages.salaryRankSalary, dbo.V_XINFileWages.adjustWhy, dbo.V_XINFileWages.executeDate, dbo.V_XINFileWages.performanceBasedPay, dbo.V_XINFileWages.rewardingPerformance, dbo.V_XINFileWages.subsidies, dbo.V_XINFileWages.approvalDate, dbo.V_XINFileWages.remark AS fwremark, dbo.V_XINFileWages.nowoRiginalTitle, dbo.V_XINFileWages.number AS V_XINFileWagesnumber, dbo.V_XINFileWages.pri, dbo.V_XINFileWages.state AS V_XINFileWagesstate, dbo.V_XINFileWages.admin AS V_XINFileWagesadmin, dbo.V_XINFileWages.createTime AS V_XINFileWagescreateTime, dbo.V_XINMainTecnce.highestEntranceDate, dbo.V_XINMainTecnce.highestGraduationDate, dbo.V_XINMainTecnce.highestLearningStyle, dbo.V_XINMainTecnce.highestGraduateCollege, dbo.V_XINMainTecnce.highestMajor, dbo.V_XINMainTecnce.highestStudyMode, dbo.V_XINMainTecnce.highestDegree, dbo.V_XINMainTecnce.highestGraduationCardNumber, dbo.V_XINMainTecnce.highestEducationBackground, dbo.V_XINMainTecnce.highestInternetRegister, dbo.V_XINMainTecnce.Mremark, dbo.V_XINMainTecnce.educationBackground, dbo.V_XINMainTecnce.entranceDate, dbo.V_XINMainTecnce.number AS V_XINMainTecncenumber, dbo.V_XINMainTecnce.graduationDate, dbo.V_XINMainTecnce.learningstyle, dbo.V_XINMainTecnce.graduateCollege, dbo.V_XINMainTecnce.major, dbo.V_XINMainTecnce.studyMode, dbo.V_XINMainTecnce.graduationCardNumber, dbo.V_XINMainTecnce.degree, dbo.V_XINMainTecnce.remark AS V_XINMainTecnceremark, dbo.V_XINMainTecnce.state AS mtremark, dbo.V_XINMainTecnce.admin AS V_XINMainTecnceadmin, dbo.V_XINMainTecnce.internetRegister, dbo.V_XINMainTecnce.createTime AS V_XINMainTecncecreateTime, dbo.V_XINPartyInformation.organizationRelationshipToTime, dbo.V_XINPartyInformation.whereTheAccess, dbo.V_XINPartyInformation.joinPartyDate, dbo.V_XINPartyInformation.conversionDate AS Expr28, dbo.V_XINPartyInformation.isConversionDate, dbo.V_XINPartyInformation.detailedAddress, dbo.V_XINPartyInformation.employer, dbo.V_XINPartyInformation.unitProperties AS Expr30, dbo.V_XINPartyInformation.unitPhone, dbo.V_XINPartyInformation.IsDeparture, dbo.V_XINPartyInformation.whichCountries, dbo.V_XINPartyInformation.isAbroad, dbo.V_XINPartyInformation.settleYear, dbo.V_XINPartyInformation.email, dbo.V_XINPartyInformation.partyBase, dbo.V_XINPartyInformation.payMonths, dbo.V_XINPartyInformation.money, dbo.V_XINPartyInformation.closingDate, dbo.V_XINPartyInformation.orgRelationshipDate, dbo.V_XINPartyInformation.orgRelationshipWhere, dbo.V_XINPartyInformation.phone, dbo.V_XINPartyInformation.remark AS piremark, dbo.V_XINPartyInformation.state AS V_XINPartyInformationstate, dbo.V_XINPartyInformation.pri AS V_XINPartyInformationpri, dbo.V_XINPartyInformation.createTime AS V_XINPartyInformationcreateTime, dbo.V_XINPartyInformation.admin AS V_XINPartyInformationadmin, dbo.V_XINPartyInformation.number AS V_XINPartyInformationnumber, dbo.V_XINPartyInformation.station AS V_XINPartyInformationstation, dbo.V_XINTechnicalRecords.nowoRiginalTitle AS V_XINTechnicalRecordsnowoRiginalTitle, dbo.V_XINTechnicalRecords.nowEonDate, dbo.V_XINTechnicalRecords.nowCertificateNumber, dbo.V_XINTechnicalRecords.nowApprovalAuthority, dbo.V_XINTechnicalRecords.originalTitle, dbo.V_XINTechnicalRecords.originalEonDate, dbo.V_XINTechnicalRecords.originalCertificateNumber, dbo.V_XINTechnicalRecords.originalApprovealAuthority, dbo.V_XINTechnicalRecords.remark AS ntrremark, dbo.V_XINTechnicalRecords.nowstate, dbo.V_XINTechnicalRecords.pri AS V_XINTechnicalRecordspri, dbo.V_XINTechnicalRecords.oldstate, dbo.V_XINTechnicalRecords.number AS V_XINTechnicalRecordsnumber, dbo.V_XINTechnicalRecords.createTime AS V_XINTechnicalRecordscreateTime, dbo.T_BasicInformation.nation, dbo.T_BasicInformation.birthday, dbo.T_BasicInformation.birthplace, dbo.T_BasicInformation.regional, dbo.T_BasicInformation.politicalStatus, dbo.T_BasicInformation.maritalStatus, dbo.T_BasicInformation.position, dbo.T_BasicInformation.domicilePlace, dbo.T_BasicInformation.myidentity, dbo.T_BasicInformation.relation, dbo.T_BasicInformation.workingtime, dbo.T_BasicInformation.receivemode, dbo.T_BasicInformation.intotime, dbo.T_BasicInformation.oldworkplace, dbo.T_BasicInformation.nowworkplace, dbo.T_BasicInformation.inervice, dbo.T_BasicInformation.telephone, dbo.T_BasicInformation.hometelephone, dbo.T_BasicInformation.Paymentstandard, dbo.T_BasicInformation.paymentsmonth, dbo.T_BasicInformation.payments, dbo.T_BasicInformation.stoptime, dbo.T_BasicInformation.balance, dbo.T_BasicInformation.isArrearage, dbo.T_BasicInformation.personnelout, dbo.T_BasicInformation.personnelouttime, dbo.T_BasicInformation.personneloutaddress, dbo.T_BasicInformation.registered, dbo.T_BasicInformation.registeredid, dbo.T_BasicInformation.registeredcardid, dbo.T_BasicInformation.householder, dbo.T_BasicInformation.registeredmove, dbo.T_BasicInformation.registeredstoptime, dbo.T_BasicInformation.isrecord, dbo.T_BasicInformation.fileintotime, dbo.T_BasicInformation.fileouttime, dbo.T_BasicInformation.filetowhere, dbo.T_BasicInformation.partyishere, dbo.T_BasicInformation.familyishere, dbo.T_BasicInformation.remarks AS bremarks, dbo.T_BasicInformation.photo, dbo.T_BasicInformation.createTime AS T_BasicInformationcreateTime, dbo.T_BasicInformation.admin AS T_BasicInformationadmin, dbo.T_BasicInformation.state AS T_BasicInformationstate, dbo.T_BasicInformation.sex, dbo.T_BasicInformation.idNumber, dbo.T_BasicInformation.number, dbo.T_BasicInformation.ID, dbo.T_BasicInformation.num, dbo.T_BasicInformation.name, dbo.T_BasicInformation.usedName FROM dbo.T_BasicInformation LEFT OUTER JOIN dbo.V_XINFileWages ON dbo.T_BasicInformation.number = dbo.V_XINFileWages.pri AND (dbo.V_XINFileWages.state = 'T' OR dbo.V_XINFileWages.state IS NULL) LEFT OUTER JOIN dbo.V_XINMainTecnce ON dbo.T_BasicInformation.number = dbo.V_XINMainTecnce.pri AND (dbo.V_XINMainTecnce.state = 'T' OR dbo.V_XINMainTecnce.state IS NULL) LEFT OUTER JOIN dbo.V_XINPartyInformation ON dbo.T_BasicInformation.number = dbo.V_XINPartyInformation.pri AND (dbo.V_XINPartyInformation.state = 'T' OR dbo.V_XINPartyInformation.state IS NULL) LEFT OUTER JOIN dbo.V_XINTechnicalRecords ON dbo.T_BasicInformation.number = dbo.V_XINTechnicalRecords.pri AND (dbo.V_XINTechnicalRecords.nowstate = 'T' OR dbo.V_XINTechnicalRecords.nowstate IS NULL) LEFT OUTER JOIN dbo.V_XINFamilyPlanning ON dbo.T_BasicInformation.number = dbo.V_XINFamilyPlanning.basicnumber AND (dbo.V_XINFamilyPlanning.state = 'T' OR dbo.V_XINFamilyPlanning.state IS NULL) WHERE (dbo.T_BasicInformation.state = 'T')

通过自己写左外连接解决问题，其实代码很是简单，主要是在理解层次上花了些功夫，还有很多自己没有发现的错误与问题出现。

## 总结 ##

对于这次的数据的维护，主要是在时间要短、尽量不动代码、效率高的情况下来完成，通过修改了调用每个信息显示的视图来完成。体会到前期软件的设计好了后期的维护还是很方面、简单。

自己暴露出的问题：对需求的理解与把握不到位，在与客户的交流过程中学会要倾听客户的描述等客户介绍完再相互交流沟通，不要有以咱们是学习软件的就能很清楚客户的所需的想法；SQL语句差远着呢，知识只是简单的了解但是实践的少，做起来就底气不足（多多实践才是硬道理）。

[Center]: /images/20220828/5571184bc0914183836993d58d682349.png