MySQL查询辅助——总结部分IN子句

Question

我有一个看起来像这样的表:

emp_id | code   | min  
1234   | TLPAID | 480  
4321   | TLPAID | 480  
4321   | UPAB   | 4  
4321   | UUAB   | 50  
5555   | TLPAID | 480  
5555   | ALLEUP | 10  

...有数百种可能的代码，但我有兴趣对其中的 6 个求和并检查它是否小于 10。

我有一个问题:

SELECT emp_id, sum(min) as time, SEC_TO_TIME(sum(min)*60) as time_formatted 
FROM codes 
WHERE code IN ('ALLEPD',  'ALLEUP',  'LATEBL',  'NCNS',  'UPAB',  'UUAB') 
GROUP BY emp_id HAVING time < 10 

此查询效果很好，除非员工没有这 6 个代码中的任何一个 - 那么他们显然不会被退回。这就是我的问题:我需要他们出现在结果中，因为他们的时间为 0，小于 10。

现在，员工将始终拥有代码 TLPAID，所以我想知道是否有一种方法可以将该代码包含在上面提到的 6 中(这样员工就会包含在查询中结果)，但仍然只对这 6 个求和？

期望的结果:

emp_id | min  
1234   | 0  
4321   | 54  
5555   | 10  

关于我应该如何处理这个问题有什么想法吗？

Answer 1

一种方法是从代码表中获取所有 emp_id 的列表，并将该列表用作 LEFT JOIN 操作的“驱动”表。

像这样:

SELECT e.emp_id
     , IFNULL(SUM(c.min),0) AS time
     , SEC_TO_TIME(IFNULL(SUM(c.min),0)*60) AS time_formatted
  FROM (SELECT d.emp_id FROM codes d GROUP BY d.emp_id) e
  LEFT
  JOIN codes c
    ON c.emp_id = e.emp_id
   AND c.code IN ('ALLEPD',  'ALLEUP',  'LATEBL',  'NCNS',  'UPAB',  'UUAB')
 GROUP 
    BY e.emp_id
HAVING time < 10

注意事项:

行 View e 中的查询获取不同 emp_id 的列表。我们可以将该查询放在括号中，并像在表中一样在 FROM 子句中使用它。我们需要为这个派生表分配一个别名(在本例中，我们刚刚选择了 e)。

LEFT JOIN 返回 e 中的每个 emp_id，以及 codes 中的匹配行。 (我们选择给该表引用一个 c 的别名。)

WHERE 子句中的谓词被移动到 JOIN 的 ON 子句，连同“匹配”来自代码的行与来自 e 的行的谓词。

我们将 SUM() 表达式包装在 IFNULL 函数中，以便在 SUM() 为 NULL 时返回零。

我们在所有列引用上添加表别名以 1) 避免“歧义列”错误，并且(可能更重要的是)帮助读者解读查询在做什么，读者不会想知道列引用引用的是哪个表到。

(为避免为 emp_id 返回 NULL，我们可以向内联 View 查询添加 WHERE d.emp_id IS NOT NULL。)