mysql - 如何使用sql在另一个表的帮助下从两个表中获取值

Question

这是我的 sql 模式:

Table 1: scan_1             Table 2: scan_2          Table 3: scan_detail

P_no        work            P_no        work           P_no       work_date
 1          YES              1          YES             1         2017/11/03
 2          YES              2          NO              2         2017/12/31
 3          NO               3          NO              3         2017/12/13

我想从 scan_1 和 scan_2 中计算 P_no，其中日期范围属于 scan_detail。我使用此查询来计算 scan_1 和 scan_2 中的 P_no，但无法在我的查询中添加 scan_detail。

SELECT COUNT(*) AS pick FROM (SELECT P_no, work FROM scan_1 WHERE work = 'YES' UNION SELECT P_no, work FROM scan_2 WHERE work = 'YES') AS total

在上面的查询中，我想从 scan_detail 添加日期范围，例如

  select count(P_no) from scan_detail where work_date >='2017/11/03' and work_date <= '2017/12/31'

Answer 1

您可以对扫描 1 和 2 的并集结果集进行连接

SELECT 
  COUNT(*) AS pick 
FROM scan_detail d
JOIN (SELECT P_no,`work` FROM scan_1 WHERE `work` = 'YES' 
      UNION
      SELECT P_no,`work` FROM scan_2 WHERE `work` = 'YES'
  ) AS s  USING(P_no) 
WHERE d.work_date >='2017/11/03' AND d.work_date <= '2017/12/31'