mysql - 如何连接具有公共(public)列的三个不同的 SQL 查询

Question

第一个查询:

select MONTHNAME(batchStartDate) AS MONTHS,
    MONTH(batchStartDate) AS MONTHNUMBER,
    SUM(totalCandidatesInBatch) AS CANDIDATES_ENROLLED 
from batch 
group by MONTHS 
order by MONTHNUMBER;

第二个查询:

select MONTHNAME(resultApprovedOndate) AS MONTHS,
    MONTH(resultApprovedOndate) AS MONTHNUMBER,
    SUM(totalCertified) AS CANDIDATES_CERTIFIED 
from batch 
where totalPass is not null AND 
    totalFail is not null AND 
    resultApprovedOnDate<=curDate() and 
    resultApprovedOnDate is not null 
group by MONTHS 
order by MONTHNUMBER;

第三个查询:

select MONTHNAME(assessmentDate) AS MONTHS,
    MONTH(assessmentDate) AS MONTHNUMBER,
    (SUM(totalPass)+SUM(totalFail)) AS CANDIDATES_ASSESSED 
from batch 
where totalPass is not null AND 
    totalFail is not null AND 
    resultApprovedOnDate<=curDate() and 
    resultApprovedOnDate is not null 
group by MONTHS 
order by MONTHNUMBER;

正如您所看到的，这些查询具有公共(public)列 MONTHS 和 MONTHNUMBER ，我想加入这三个查询的结果。我尝试过 UNION 但不起作用。

谁能告诉我发生了什么事以及解决方案是什么？

输出第一个查询

 # MONTHS, MONTHNUMBER, CANDIDATES_ENROLLED
 January,       1,       50
 February,      2,       35
 March,         3,       205
 April,         4,       85
 May,           5,       616
 June,          6,       26

第二个查询

 # MONTHS, MONTHNUMBER, CANDIDATES_CERTIFIED
  January,       1,            34
    April,       4,            72
     July,       7,            484
   August,       8,            30

第三个查询

 # MONTHS, MONTHNUMBER, CANDIDATES_ASSESSED
 February,       2,           101
    March,       3,           134
      May,       5,           32
     July,       7,           75
   August,       8,           75
September,       9,           150
  October,       10,           75
 November,       11,           77
 December,       12,          152

我希望输出以表单形式显示

# MONTHS, MONTHNUMBER, CANDIDATES_ENROLLED, CANDIDATES_CERTIFIED, CANDIDATES_ASSESSED

此外，如果表中缺少行，则该值应填充零或 null。例如，在第三个查询输出中缺少一月，则评估的候选者应为 0 或 null。

Answer 1

看这个链接: https://www.w3schools.com/sql/sql_join.asp

它向您展示了不同类型的联接(我假设您想要进行“联接”)。

并集用于将两个或多个集合相互组合。

使用联接，您可以根据表之间的相关列进行联接