用于从3个表中获取数据的SQL查询

A MySQL question, apologize for being a beginner. I would like to get data from 3 different tables, as you can see in the attached screenshot.

一个MySQL问题,为初学者道歉。我想从3个不同的表中获取数据,如附图所示。

The given company maintains the employees' travels, but for technical reasons, in two different tables. Two types of travel are possible. If the employee chooses the first one, it's stored in the first table. Only this kind of travels are stored in this table. If he chooses the second one, it's stored in the second table, together with several other types of working times. (The difference between the two travel types is tax related, but that's out of interest here.) I would like to generate a 3 column result set which shows the travel days for everybody in this year (see the screenshot above). It's difficult to use either SUM or COUNT, because the data structure is quite different in the two tables. I tried to use the SQL query below, but that just unions the two different query what I would like to merge instead. I would like to see all emplyees in the result set, regardless if they travelled at all. If they didn't, I would like to see NULL values there.

给定公司维护员工的旅行,但由于技术原因,在两个不同的表格中。有两种类型的旅行。如果员工选择第一个,则将其存储在第一个表中。此表中仅存储此类旅行。如果他选择第二个,它将与其他几种工作时间一起存储在第二个表格中。 (这两种旅行类型之间的差异与税收有关,但这里没有兴趣。)我想生成一个3列结果集,显示今年每个人的旅行天数(见上面的截图)。很难使用SUM或COUNT,因为两个表中的数据结构完全不同。我尝试使用下面的SQL查询,但这只是联合两个不同的查询我想要合并的内容。我希望看到结果集中的所有员工,无论他们是否一直在旅行。如果他们没有,我想在那里看到NULL值。

SELECT Employees.Name as name1, SUM(TravelType1.TravelDurationDays) as Travel FROM `Employees` INNER JOIN `TravelType1` ON Employees.EmployeeID=TravelType1.EmployeeID WHERE (TravelType1.StartTime>='01.01.2015' and TravelType1.EndTime<='31.12.2015') GROUP BY Employees.Name

UNION

SELECT Employees.Name as name1, COUNT(*) as Travel FROM `Employees` INNER JOIN `TravelType1` ON Employees.EmployeeID=TravelType2.EmployeeID WHERE TravelType2.TravelDay>='01.01.2015' and TravelType2.TravelDay<='31.12.2015' and TravelType2.Travel='yes' GROUP BY Employees.Name

I'm not sure it's possible at all in one SQL statement, because of the really different data format in the two source tables. I already tried some conbinations of LEFT JOIN and RIGHT JOIN, but could't achieve anything yet.

我不确定它在一个SQL语句中是否可行,因为两个源表中的数据格式确实不同。我已经尝试了LEFT JOIN和RIGHT JOIN的一些组合,但还没有实现任何目标。

Any help is appreciated, thanks!

任何帮助表示赞赏,谢谢!

2 个解决方案

#1

This can be solved with 2 sub-selects:

这可以通过2个子选择来解决:

SELECT
   e.Name,
   (SELECT
      SUM(TravelDurationDays)
   FROM
      TravelType1
   WHERE
      EmployeeID = e.EmployeeID
   ) TravelType1Days,
   (SELECT
      COUNT(*)
   FROM
      TravelType2
   WHERE
      EmployeeID = e.EmployeeID AND
      Travel = 'yes'
   ) TravelType2Days
FROM
   Employees e

#2

I implemented with joins.

我用连接实现了。

SELECT e.Name, 
  SUM(TravelType1.TravelDurationDays), 
  SUM(IF(TravelType2.Travel = 'yes' and TravelType2.Day >= '01.01.2015' and TravelType2.Day <= '31.12.2015', 1, 0))
FROM Employees e 
  LEFT JOIN TravelType1 ON e.EmployeeID = TravelType1.EmployeeID
  LEFT JOIN TravelType2 ON e.EmployeeID = TravelType2.EmployeeID
GROUP BY e.EmployeeID;

#1