SQL架构：
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行
然后查看表事务，并在有OPERATION_ID时写入1
您可以使用exists:
select sm.*,
(exists (select 1
from transactions t
where t.serial_number = sm.serial_number and t.operation_id = 113
)
) as has_113,
(exists (select 1
from transactions t
where t.serial_number = sm.serial_number and t.operation_id = 116
)
) as has_116,
(exists (select 1
from transactions t
where t.serial_number = sm.serial_number and t.operation_id = 160
)
) as has_160
from snmaster sm;
对于事务(serial_number,operation_id)的索引，这可能是最快的方法。
在MySQL中，exists返回一个布尔值。数据库将1视为true，而将0视为false，这就是为什么这样做可以满足您的需要。在其他数据库中，您需要将exists包装在case表达式中。
