mysql - 代表同一 ID 检索数据时遇到错误

Question

我有 3 个表，第一个是申请人

Ap_id       Username          Email            password
 1          Elen_zey     Alan@gmail.com        123456
 2          Morgan_jam    Morgan@gmail.com     443556
 3          Alex_Hales     Alex@gmail.com      534545

申请人个人信息

Ap_id       FirstName     LastName        Contact       CNIC
 1           Alan          Zey          214343555   4663-14334-1
 2           Morgan        Jam          984209482   2663-14334-4
 3           Alex          Hales        343453453   7663-14334-2

申请人专业信息

Ap_id    duration    organization     Designation   Total Experience
 1         10          10 Pearls       Intern          20 Months
 1         10          10 Pearls       DBA             20 Months
 2         36          Radium Inc       SQA             36 Months
 3         46          Sper Inc        Intern          46 Months

我想检索申请人的电子邮件、联系电话、名字和姓氏，其中总经验等于且大于 10 个月。我尝试了这个查询

SELECT GROUP_CONCAT(DISTINCT CONCAT(aper.FirstName," ",aper.LastName)) AS 
Name, GROUP_CONCAT(DISTINCT ap.Email), GROUP_CONCAT(DISTINCT aper.Contact), 
GROUP_CONCAT(DISTINCT apro.totalExperience) AS Experience FROM 
applicant_personal_info aper INNER JOIN applicant ap ON aper.ApplicantID = 
ap.ApplicantID INNER JOIN applicant_professional_info apro ON 
aper.ApplicantID = apro.ApplicantID WHERE apro.totalExperience >=10 

我得到这样的结果

Name : 'Alan Zey, Morgan Jam, Alex Hales',
Email : 'Alan@gmail.com, Morgan@gmail.com,Alex@gmail.com',
Contact : '214343555,984209482,343453453',
Experience : '20,36,46'

但我想要这样的结果

Name : Alan Zey,
Email : Alan@gmail.com,  
Contact : 214343555,
Experience : 20

Name : Morgan Jam,
Email : Mirgan@gmail.com,  
Contact : 984209482   ,
Experience : 36

Name : Alex Hales,
Email : Alex@gmail.com,  
Contact : 343453453,
Experience : 46

由于申请人专业信息中有多个记录，因此可以使用任何记录，因为在总经验列中，有针对同一申请人 ID 的所有持续时间列的总和。

我怎样才能实现这个目标？

Answer 1

摆脱所有 GROUP_CONCAT() 调用，它们将所有记录合并到具有逗号分隔值的单行中。

使用 GROUP BY aper.ApplicantID 获取每个申请人一行，并使用 MAX(apro.totalExperience) 获取其所有专业信息记录中的最高体验最终结果。

SELECT DISTINCT CONCAT(aper.FirstName," ",aper.LastName) AS Name, ap.Email, aper.Contact, MAX(apro.totalExperience) AS Experience
FROM applicant_personal_info aper 
INNER JOIN applicant ap ON aper.ApplicantID = ap.ApplicantID 
INNER JOIN applicant_professional_info apro ON aper.ApplicantID = apro.ApplicantID 
WHERE apro.totalExperience >= 10
GROUP BY aper.ApplicantID