SQL Server NOT IN 和 LIKE 一起使用

Question

我这里有一个示例 dbfiddle，我的目标是从CUS表的email列中过滤出电子邮件CMT其中表没有cmt = 3记录CUS表的email字段LIKE 'Ack%'。

例如：根据样本数据集，预期结果应该只有Ack:[e-mail]，因为order_no = 186350 and 186351在cmt字段中没有Comment 3的CUS表上的email字段的开头也有Ack。

我尝试过但没有成功：

SELECT U.email
FROM CUS U inner join CMT M ON U.order_no = M.ord_no
WHERE M.cmt like 'Ack%'
AND U.order_no NOT IN (
SELECT L.ord_no
FROM CMT L
WHERE L.cmt_sql_no = 3
);

感谢您的帮助！

示例数据集：

CREATE TABLE CMT (
id INT,
ord_type char(1),
ord_no char(8),
line_sql_no smallint,
cmt_sql_no smallint,
cmt nvarchar(4000)
);
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (1,'O','186349',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (2,'O','186349',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (3,'O','186349',0,3,'Comment 3');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (4,'O','186350',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (5,'O','186350',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (6,'O','186351',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (7,'O','186351',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (8,'O','186352',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (9,'O','186352',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (10,'O','186352',0,3,'Comment 3');
SELECT * FROM CMT
CREATE TABLE CUS (
id INT,
ord_type char(1),
order_no nchar(10),
status char(1),
email nchar(50)
);
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (1,'O','186349','4','Ack:[e-mail]');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (2,'O','186350','4','Inv:[e-mail]');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (3,'O','186351','4','Ack:[e-mail]');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (4,'O','186352','1','Inv:[e-mail]');
SELECT * FROM CUS

Answer 1

这似乎最好使用不存在的相关性来解决，以下内容从示例数据集中生成所需的结果：

select c.email
from customer_comment c
where email like 'Ack:%'
and not exists (
select * from OELINCMT_SQL o
where o.ord_no = c.order_no 
and o.cmt_sql_no = 3
);

DB<>小提琴