SQL查询以获取联接表 [英] SQL query to get a joinned table

问题描述

我有两个表需要联接，并且需要获取可用于绘制的数据。

I have two tables that I need to join and need to get the data that I can use to plot.

两个表的样本数据为：

  **table1**

mon_pjt  month  planned_hours
pjt1    01-10-2019  24
pjt2    01-01-2020  67
pjt3    01-02-2019  12



   **table2**
    date    project hrs_consumed
07-12-2019  pjt1    7
09-09-2019  pjt2    3
12-10-2019  pjt1    4
01-02-2019  pjt3    5
11-10-2019  pjt1    4

样本输出，其中实际小时数是表2中hrs_consumed列的总和。以下是示例输出：

Sample Output, where the actual hours are summation of column hrs_consumed in table2. Following is the sample output:

project label   planned_hours   actual_hours
pjt1    Oct-19  24                8
pjt1    Dec-19  0                 7
pjt2    Sep-19  0                 3
pjt2    Jan-20  67                0
pjt3    Feb-19  12                5

我尝试了以下查询，但给出了错误：

I have tried the following query but it gives error:

Select Sum(a.hrs_consumed), a.date, a.planned_hours
 From (SELECT t1.date, t2.month, t1.project, t1.hrs_consumed, t2.planned_hours
       from table1 t1 JOIN
            table2 t2 
            on t2.month = t1.date
       UNION
       SELECT t1.date, t2.month, t1.mon_pjt, t2.hrs_consumed, t1.planned_hours
       from table t1 JOIN
            table2 t2 
            on t1.date != t2.month
     ) 

我尝试了另一种方法也分别提取两个表并在javascript中尝试joi n对其进行排序，但那也是徒劳的。

I have tried another way also extracting two tables separately and in javascript trying to join it and sort it but that was also vain.

推荐答案

SELECT project, label,planned_hours,(planned_hours-hours_consumed) AS actual_hours 

FROM(
SELECT t1.mon_pjt AS project,date_format(t1.month,'%M-%Y') AS label,
t1.planned_hours,0 AS hours_consumed

FROM table1 t1

UNION

SELECT t2.project,date_format(t2.date,'%M-%Y') AS label,0 as planned_hours,

sum(t2.hours_consumed) AS hours_consumed

FROM table1 t2

GROUP BY project)t

GROUP BY t.project
ORDER BY project

这篇关于SQL查询以获取联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！