在同一个表上加入2个查询，并将列添加到结果中

I am pretty new to MYSQL and need assistance to complete a query.

我是MYSQL的新手，需要帮助才能完成查询。

I have 2 separate queries that I need put into just one query that I can use in PHP.

我有两个单独的查询，我需要将其放入一个我可以在PHP中使用的查询中。

I am using the following SQL to get the number of records in a table called 'import' that have a Job_Completed_Date less than or equal to the Job_SLA_Due_Date.

我使用以下SQL来获取名为“import”的表中记录的数量，其中Job_Completed_Date小于或等于Job_SLA_Due_Date。

My where clause checks for jobs done in the last 6 months in the 'import' table.

我的where子句检查“import”表中过去6个月内完成的作业。

SELECT Year(job_completed_date), 
       Month(job_completed_date), 
       Count(*) AS Completed_On_Schedule 
FROM   import 
WHERE  job_completed_date >= Last_day(Now()) + interval 1 day - interval 6 month 
       AND job_completed_date <= job_sla_due_date 
       AND ( job_status = 'C' 
              OR job_status = 'C2' 
              OR job_status = 'C8' ) 
GROUP  BY Year(job_completed_date), 
          Month(job_completed_date) 
ORDER  BY job_completed_date ASC

The result output is fine and produces the following....

结果输出很好，产生以下....

Year    Month  Completed_On_Schedule 
2017    3      1155 
2017    4      838 
2017    5      881 
2017    6      1095 
2017    7      1073 
2017    8      295

My second query is similar using the same table but I am looking for the total number of jobs that have been completed in the last 6 months but I am not worried about the Job_SLA_Due_Date. The SQL I am using is .......

我的第二个查询类似于使用相同的表，但我正在寻找在过去6个月内完成的作业总数，但我并不担心Job_SLA_Due_Date。我正在使用的SQL是.......

SELECT Year(job_completed_date), 
       Month(job_completed_date), 
       Count(*) AS Total_Completed 
FROM   import 
WHERE  job_completed_date >= Last_day(Now()) + interval 1 day - interval 6 month 
       AND ( job_status = 'C' 
              OR job_status = 'C2' 
              OR job_status = 'C8' ) 
GROUP  BY Year(job_completed_date), 
          Month(job_completed_date) 
ORDER  BY job_completed_date ASC

The result output is as expected and produces the following.....

结果输出符合预期，并产生以下.....

    Year  Month  Total_Completed 
    2017  3      1441 
    2017  4      1101 
    2017  5      1144 
    2017  6      1334 
    2017  7      1211 
    2017  8      311

What I need now is output like this......

我现在需要的是这样输出......

    Year   Month Completed_On_Schedule Total_Completed %_On_Schedule 
    2017    3    1155                  1441            80.1 
    2017    4    838                   1101            76.1 
    2017    5    881                   1144            77 
    2017    6    1095                  1334            82 
    2017    7    1073                  1211            88.6 
    2017    8    295                   311             94.8

So in a nutshell I need to join my 2 queries together and add another column called %_Completed_On_Schedule which is the percentage of jobs completed on schedule i.e. the percentage that the Completed_On_Schedule column is of the Total_Completed column.

因此，简而言之，我需要将我的2个查询连接在一起并添加另一个名为％_Completed_On_Schedule的列，该列是按计划完成的作业的百分比，即Completed_On_Schedule列与Total_Completed列的百分比。

Any help will be really appreciated.

任何帮助将非常感激。

Regards

问候

Alan

艾伦

3 个解决方案

#1

Just use a conditional COUNT on your SELECT.

只需在SELECT上使用条件COUNT即可。

Aditional tip use IN instead of multiple OR

有条件的提示使用IN而不是多个OR

SELECT Year(job_completed_date), 
       Month(job_completed_date), 
       COUNT(CASE WHEN job_completed_date <= job_sla_due_date 
                  THEN 1 END) as Completed_On_Schedule,
       COUNT(*) AS Total_Completed,
       COUNT(CASE WHEN job_completed_date <= job_sla_due_date 
                  THEN 1 END) * 100.0 / 
       COUNT(*) AS Total_Completed as `%_On_Schedule`

FROM   import 
WHERE  job_completed_date >= Last_day(Now()) + interval 1 day - interval 6 month 
       AND job_status IN ('C', 'C2', 'C8') 
GROUP  BY Year(job_completed_date), 
          Month(job_completed_date) 
ORDER  BY job_completed_date ASC

For answer how you join two queries together here is an example (even when you don't need it for this case):

为了回答如何将两个查询连接在一起，这里是一个示例（即使您在这种情况下不需要它）：

SELECT Q1.*, Q2.*
FROM ( <your first query> ) as Q1
JOIN ( <your second query> ) as Q2
  ON (Q1.year, Q1.month) = (Q2.year, Q2.month)

#2

I have added in your query a column with the total, and a CASE clausule. Besides, i have removed the AND for the SLA because we have it in the CASE_

我在您的查询中添加了一个包含total的列和一个CASE clausule。此外，我已经删除了SLA的AND，因为我们在CASE_中有它

SELECT Year(job_completed_date), 
       Month(job_completed_date), 
       SUM(CASE 
               WHEN job_completed_date <= job_sla_due_date THEN 1 ELSE 0
           END) AS Completed_On_Schedule,
       Count(*) AS Total_Completed
FROM   import 
WHERE  job_completed_date >= Last_day(Now()) + interval 1 day - interval 6 month  
       AND ( job_status = 'C' 
              OR job_status = 'C2' 
              OR job_status = 'C8' ) 
GROUP  BY Year(job_completed_date), 
          Month(job_completed_date) 
ORDER  BY job_completed_date ASC

#3

For simplicity I am referring to your queries as QUERY_ONE and QUERY_TWO, you'll of course need to replace these references with the actual queries.

为简单起见，我将您的查询称为QUERY_ONE和QUERY_TWO，您当然需要将这些引用替换为实际查询。

SELECT a.Year, a.Month, a.Completed_On_Schedule, b.Total_Completed, 
((a.Completed_On_Schedule / b.Total_Completed) * 100) AS `%_On_Schedule` 
FROM (QUERY_ONE) AS a JOIN (QUERY_TWO) AS b ON a.Year = b.Year AND a.Month = b.Month;

This query will join your two tables on rows matching on Year and Month, then extract the desired values from each table and create your percent on schedule column. You need to apply ticks to your %_On_Schedule name since % is a key term in MySQL, I would recommend not using this symbol for this reason but it isn't a big deal really.

此查询将在“年”和“月”上匹配的行上连接两个表，然后从每个表中提取所需的值并创建计划百分比列。你需要对你的％_On_Schedule名称应用滴答，因为％是MySQL中的一个关键术语，我建议不要因为这个原因使用这个符号，但它确实不是什么大问题。

#1