解决Mysql数据库实现多表联合查询及按照其中一个字段进行排序问题

大家在做项目时经常会遇到按照其中一个字段排序展示数据库中的信息问题,但是要展示的数据又在不同的表中。这种情况下就要用到表之间的联合查询

例如:有一个表user_t 其中有user_name; user_age; add_time等字段字段;

            另外一个student_t其中有student_name; student_age; add_time等字段

问题:如何按照时间顺序展示这两个表中的数据?

解决方法:

        select  user_name  as  name,  user_age  as age ,add_time  as  time  from  user_t 

        union

        select  student_name as  name,  student_age as age ,add_time  as  time  from  user_t  

        order  by  time  desc(asc);


若有更多的表需要关联,按照同样的方法写就ok了。

       


已标记关键词 清除标记
这些是的结构: CREATE TABLE `rs_emp` ( `EmpSysID` varchar(36) NOT NULL, `EmpNo` varchar(20) NOT NULL, `EmpName` varchar(50) NOT NULL DEFAULT '', `EmpSexSysID` varchar(36) DEFAULT NULL, `CardTypeSysID` varchar(36) DEFAULT NULL, `RateSysID` varchar(36) DEFAULT NULL, `JobSysID` varchar(36) DEFAULT NULL, `DepartSysID` varchar(36) NOT NULL, `StatusSysID` varchar(36) DEFAULT NULL, `EmpEngName` varchar(50) DEFAULT NULL, `EmpHireDate` datetime DEFAULT NULL, `EmpPositiveDate` datetime DEFAULT NULL, `EmpLeaveDate` datetime DEFAULT NULL, `EmpLeaveReason` varchar(255) DEFAULT NULL, `EmpStatusID` tinyint(4) DEFAULT NULL, `EmpMemo` varchar(200) DEFAULT NULL, `CertTypeSysID` varchar(36) DEFAULT NULL, `EmpCertNo` varchar(50) DEFAULT NULL, `EmpBirthDate` varchar(50) DEFAULT NULL, `CountrySysID` varchar(36) DEFAULT NULL, `NationSysID` varchar(36) DEFAULT NULL, `EduSysID` varchar(36) DEFAULT NULL, `EmpGdSchool` varchar(200) DEFAULT NULL, `PolitySysID` varchar(36) DEFAULT NULL, `EmpLanguageAbility` varchar(200) DEFAULT NULL, `EmpMarrySysID` varchar(36) DEFAULT NULL, `EmpZipNo` varchar(50) DEFAULT NULL, `EmpPhoneNo` varchar(50) DEFAULT NULL, `NativeSysID` varchar(36) DEFAULT NULL, `EmpAddress` varchar(200) DEFAULT NULL, `EmpEmail` varchar(200) DEFAULT NULL, `EmpFamilyInfo` varchar(200) DEFAULT NULL, `EmpBiography` mediumtext, `AllowDelete` bit(1) DEFAULT b'1', `EmpLeaveStatus` bit(1) DEFAULT b'0', `IsAttend` char(1) DEFAULT NULL, `OtherCardNo` varchar(10) DEFAULT NULL, `CardFingerNo` bigint(10) DEFAULT NULL, PRIMARY KEY (`EmpSysID`), UNIQUE KEY `AK_RS_Emp` (`EmpNo`), KEY `AK_RS_Emp2` (`EmpSysID`), KEY `AK_RS_Emp3` (`EmpName`), KEY `AK_RS_Emp6` (`RateSysID`), KEY `AK_RS_Emp7` (`StatusSysID`), KEY `AK_RS_Emp8` (`CountrySysID`), KEY `AK_RS_Emp9` (`CertTypeSysID`), KEY `AK_RS_Emp10` (`EduSysID`), KEY `AK_RS_Emp11` (`PolitySysID`), KEY `AK_RS_Emp12` (`NationSysID`), KEY `AK_RS_Emp23` (`EmpSysID`,`EmpNo`), KEY `AK_RS_Emp33` (`EmpLeaveStatus`), KEY `AK_RS_Emp22` (`EmpSysID`,`EmpName`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk CREATE TABLE `rs_depart` ( `DepartSysID` varchar(36) NOT NULL, `DepartPrcID` varchar(100) NOT NULL, `DepartID` varchar(12) NOT NULL, `DepartName` varchar(50) NOT NULL, `DepartUpSysId` varchar(36) DEFAULT NULL, `DepartMemo` varchar(200) DEFAULT NULL, PRIMARY KEY (`DepartSysID`), UNIQUE KEY `AK_RS_Depart` (`DepartID`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=gbk CREATE TABLE `rs_classcerttype` ( `CertTypeSysID` varchar(36) NOT NULL, `CertTypeNo` varchar(20) NOT NULL, `CertTypeName` varchar(50) NOT NULL, PRIMARY KEY (`CertTypeSysID`), UNIQUE KEY `AK_RS_ClassCertType` (`CertTypeNo`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk CREATE TABLE `rs_classcountry` ( `CountrySysID` varchar(36) NOT NULL, `CountryNo` varchar(20) NOT NULL, `CountryName` varchar(50) NOT NULL, PRIMARY KEY (`CountrySysID`), UNIQUE KEY `AK_RS_ClassCountry` (`CountryNo`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk 跟rs_classcountry类似结构的还有几张 最后创建视图,为这几张查询结果,视图: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vrs_emp` AS select `a`.`EmpSysID` AS `EmpSysID`,`a`.`EmpNo` AS `EmpNo`,`a`.`EmpName` AS `EmpName`,`a`.`CardFingerNo` AS `CardFingerNo`,`a`.`EmpSexSysID` AS `EmpSexSysID`,`a`.`CardTypeSysID` AS `CardTypeSysID`,`a`.`RateSysID` AS `RateSysID`,`a`.`JobSysID` AS `JobSysID`,`a`.`DepartSysID` AS `DepartSysID`,`a`.`StatusSysID` AS `StatusSysID`,`a`.`EmpEngName` AS `EmpEngName`,`a`.`EmpHireDate` AS `EmpHireDate`,`a`.`EmpPositiveDate` AS `EmpPositiveDate`,`a`.`EmpLeaveDate` AS `EmpLeaveDate`,`a`.`EmpLeaveReason` AS `EmpLeaveReason`,`a`.`EmpStatusID` AS `EmpStatusID`,`a`.`EmpMemo` AS `EmpMemo`,`a`.`CertTypeSysID` AS `CertTypeSysID`,`a`.`EmpCertNo` AS `EmpCertNo`,`a`.`EmpBirthDate` AS `EmpBirthDate`,`a`.`CountrySysID` AS `CountrySysID`,`a`.`NationSysID` AS `NationSysID`,`a`.`EduSysID` AS `EduSysID`,`a`.`EmpGdSchool` AS `EmpGdSchool`,`a`.`PolitySysID` AS `PolitySysID`,`a`.`EmpLanguageAbility` AS `EmpLanguageAbility`,`a`.`EmpMarrySysID` AS `EmpMarrySysID`,`a`.`EmpZipNo` AS `EmpZipNo`,`a`.`EmpPhoneNo` AS `EmpPhoneNo`,`a`.`NativeSysID` AS `NativeSysID`,`a`.`EmpAddress` AS `EmpAddress`,`a`.`EmpEmail` AS `EmpEmail`,`a`.`EmpFamilyInfo` AS `EmpFamilyInfo`,`a`.`EmpBiography` AS `EmpBiography`,`a`.`AllowDelete` AS `AllowDelete`,`a`.`EmpLeaveStatus` AS `EmpLeaveStatus`,`a`.`IsAttend` AS `IsAttend`,`a`.`OtherCardNo` AS `OtherCardNo`,(to_days(now()) - to_days(`a`.`EmpPositiveDate`)) AS `EmpPositiveDay`,floor(((to_days(curdate()) - to_days(`a`.`EmpBirthDate`)) / 365)) AS `EmpAge`,`b`.`DepartID` AS `DepartID`,`b`.`DepartName` AS `DepartName`,`c`.`StatusNo` AS `StatusNo`,`c`.`StatusName` AS `StatusName`,`d`.`RateNo` AS `RateNo`,`d`.`RateName` AS `RateName`,`e`.`PolityNo` AS `PolityNo`,`e`.`PolityName` AS `PolityName`,`f`.`NativeNo` AS `NativeNo`,`f`.`NativeName` AS `NativeName`,`g`.`NationNo` AS `NationNo`,`g`.`NationName` AS `NationName`,`h`.`JobNo` AS `JobNo`,`h`.`JobName` AS `JobName`,`i`.`EduNo` AS `EduNo`,`i`.`EduName` AS `EduName`,`j`.`CountryNo` AS `CountryNo`,`j`.`CountryName` AS `CountryName`,`k`.`CertTypeNo` AS `CertTypeNo`,`k`.`CertTypeName` AS `CertTypeName`,`m`.`EmpSexID` AS `EmpSexID`,`m`.`EmpSexName` AS `EmpSexName`,`n`.`EmpStatusName` AS `EmpStatusName`,`o`.`EmpMarryID` AS `EmpMarryID`,`o`.`EmpMarryName` AS `EmpMarryName`,`p`.`CardPhysicsNo10` AS `CardPhysicsNo10`,`p`.`CardPhysicsNo8` AS `CardPhysicsNo8`,`p`.`CardSectorNo` AS `CardSectorNo`,`p`.`CardStatusID` AS `CardStatusID`,`p`.`CardStatusDate` AS `CardStatusDate`,`p`.`CardPWD` AS `CardPWD`,`p`.`CardStartDate` AS `CardStartDate`,`p`.`CardEndDate` AS `CardEndDate`,`p`.`CardUseDate` AS `CardUseDate`,`p`.`CardUseTimes` AS `CardUseTimes`,`p`.`RetirementFlag` AS `RetirementFlag`,`p`.`CardBTMoney` AS `CardBTMoney`,`p`.`FaDate` AS `FaDate`,`q`.`CardStatusName` AS `CardStatusName`,`r`.`EmpPhotoPath` AS `EmpPhotoPath` from ((((((((((((((((`rs_emp` `a` join `rs_depart` `b` on((`b`.`DepartSysID` = `a`.`DepartSysID`))) left join `rs_classstatus` `c` on((`c`.`StatusSysID` = `a`.`StatusSysID`))) left join `rs_classrate` `d` on((`d`.`RateSysID` = `a`.`RateSysID`))) left join `rs_classpolity` `e` on((`e`.`PolitySysID` = `a`.`PolitySysID`))) left join `rs_classnative` `f` on((`f`.`NativeSysID` = `a`.`NativeSysID`))) left join `rs_classnation` `g` on((`g`.`NationSysID` = `a`.`NationSysID`))) left join `rs_classjob` `h` on((`h`.`JobSysID` = `a`.`JobSysID`))) left join `rs_classeducation` `i` on((`i`.`EduSysID` = `a`.`EduSysID`))) left join `rs_classcountry` `j` on((`j`.`CountrySysID` = `a`.`CountrySysID`))) left join `rs_classcerttype` `k` on((`k`.`CertTypeSysID` = `a`.`CertTypeSysID`))) left join `vrs_empsex` `m` on((`m`.`EmpSexSysID` = `a`.`EmpSexSysID`))) left join `vrs_empstatus` `n` on((`n`.`EmpStatusID` = `a`.`EmpStatusID`))) left join `vrs_empmarrystatus` `o` on((`o`.`EmpMarrySysID` = `a`.`EmpMarrySysID`))) left join `rs_empcard` `p` on((`p`.`EmpSysID` = `a`.`EmpSysID`))) left join `vrs_empcardstatus` `q` on((`p`.`CardStatusID` = `q`.`CardStatusID`))) left join `rs_empphoto` `r` on((`a`.`EmpSysID` = `r`.`EmpSysID`))) where (`a`.`EmpLeaveStatus` <> 1) 当我在mysql 用explain执行了一下 explain select * from vrs_emp where empsysid<>'' order by empno limit 0,30 出现 1 SIMPLE b system PRIMARY 1Using temporary; Using filesort 该如何优化??都有索引
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页