mysql - 使用子查询的查询需要返回原始查询中的所有行(即使子查询返回空)

Question

我正在尝试编写一个 SQL 查询，该查询返回每个员工在测试中得分的最大值，每个测试都在其自己的列中。
到目前为止，我已经想出了:

SELECT E.id, 
       Concat(E.firstname, ' ', E.middlename, ' ', E.lastname) AS FullName, 
       score1.maxscore, 
       score2.maxscore 
FROM   employees AS E, 
       (SELECT empid, 
               Max(Cast(score AS DECIMAL)) AS MaxScore 
        FROM   filledsoc 
        WHERE  socid = '87bf5fba-5702-4304-869c-8707956d34d0' 
        GROUP  BY empid) AS score1, 
       (SELECT empid, 
               Max(Cast(score AS DECIMAL)) AS MaxScore 
        FROM   filledsoc 
        WHERE  socid = '340363d9-e9a2-478b-b819-d3a56d337561' 
        GROUP  BY empid) AS score2 
WHERE  E.id = score1.empid 
       AND E.id = score2.empid 

这只是一个测试查询，最终的查询将动态构建。我的问题是查询仅返回所有列都有值的结果。
如果其中一个子查询返回空，则该员工将被一起跳过。
我需要的是一个查询，该查询返回表“employees”中的所有行，并仅在存在时才填写 Score1.maxscore 和 Score2.maxscore 。如果它不是“0”或者什么都没有问题......

对此的任何帮助将不胜感激。

米歇尔

Answer 1

SELECT E.ID, CONCAT(E.FirstName, ' ', E.MiddleName, ' ', E.LastName) AS FullName, 
(
  SELECT MAX(CAST(F1.Score AS DECIMAL))
  FROM FilledSOC AS F1
  WHERE F1.SocID = '87bf5fba-5702-4304-869c-8707956d34d0' 
  AND E.ID = F1.EmpID
) AS Score_1,
(
  SELECT MAX(CAST(F2.Score AS DECIMAL))
  FROM FilledSOC AS F2
  WHERE F2.SocID = '340363d9-e9a2-478b-b819-d3a56d337561' 
  AND E.ID = F2.EmpID
) AS Score_2
FROM Employees AS E