SQL实现比对两表：存在值显示1不存在则显示0

-- 序列号主表
CREATE TABLE SNMASTER ( 
  SERIAL_NUMBER varchar(50),
  WORK_ORDER int(5),
  ASSY_ID int(3)
);
  
-- 过站流转表
CREATE TABLE TRANSACTIONS ( 
  SERIAL_NUMBER varchar(50),
  OPERATION_ID int(5),
  CREATED_ON DATETIME
);
  
-- 主表测试数据
INSERT INTO SNMASTER VALUES('0910A40877',56794,841);
INSERT INTO SNMASTER VALUES('0910A40886',56794,841);
INSERT INTO SNMASTER VALUES('0910A40895',56794,841);
INSERT INTO SNMASTER VALUES('0910A40902',56794,841);
INSERT INTO SNMASTER VALUES('0910A40911',56794,841);
INSERT INTO SNMASTER VALUES('0910A40920',88545,841);
INSERT INTO SNMASTER VALUES('0910A40939',88545,841);
INSERT INTO SNMASTER VALUES('0910A40948',88545,841);
INSERT INTO SNMASTER VALUES('0910A40957',97846,842);
INSERT INTO SNMASTER VALUES('0910A40966',97846,842);
  
-- 流转表测试数据
INSERT INTO TRANSACTIONS VALUES('0910A40886',113,'2020-02-27 13:39:46');
INSERT INTO TRANSACTIONS VALUES('0910A40877',116,'2020-02-27 13:39:48');
INSERT INTO TRANSACTIONS VALUES('0910A40886',116,'2020-02-27 13:40:03');
INSERT INTO TRANSACTIONS VALUES('0910A40939',113,'2020-02-27 13:40:20');
INSERT INTO TRANSACTIONS VALUES('0910A40886',160,'2020-02-27 13:40:48');

SERIAL_NUMBER  113  116  160
 0910A40877    0    1    0
 0910A40886    1    1    1
 0910A40895    0    0    0
 0910A40902    0    0    0
 0910A40911    0    0    0

• 结果行仅选取SNMASTER表的前5条序列号记录
• 针对每条序列号，关联匹配TRANSACTIONS表中对应工序ID的流转记录
• 存在匹配记录则对应单元格值为1，无匹配则为0，最终按“序列号 + 三个工序列”的结构输出

SELECT
  s.SERIAL_NUMBER,
  MAX(CASE WHEN t.OPERATION_ID = 113 THEN 1 ELSE 0 END) AS `113`,
  MAX(CASE WHEN t.OPERATION_ID = 116 THEN 1 ELSE 0 END) AS `116`,
  MAX(CASE WHEN t.OPERATION_ID = 160 THEN 1 ELSE 0 END) AS `160`
FROM (
  -- 先锁定统计范围：主表前5条序列号
  SELECT SERIAL_NUMBER
  FROM SNMASTER
  LIMIT 5
) s
-- 左关联流转表，只匹配需要统计的三个工序，减少无效数据
LEFT JOIN TRANSACTIONS t
  ON s.SERIAL_NUMBER = t.SERIAL_NUMBER
  AND t.OPERATION_ID IN (113, 116, 160)
-- 按序列号分组，聚合每个序列号的工序匹配结果
GROUP BY s.SERIAL_NUMBER;

实现说明 #

• 采用子查询先圈定统计范围，避免后续关联查询时扩大主表数据量
• 左连接保证没有任何流转记录的序列号也会被保留，不会被过滤掉
• 用CASE判断工序匹配关系，搭配MAX聚合实现“有记录就标1，无记录就标0”的效果，避免单序列号同工序多条流转记录导致的结果重复计数

内容的提问来源于stack exchange，提问作者Stephen Stilwell