我有这张Student
桌子:
Id companyId status
----------------------------------------
101 1001 In-Progress
102 1001 In-Progress
103 1001 Final
104 1002 In-Progress
105 1003 Pending With Company
106 1003 In-Progress
107 1004 In-Progress
108 1004 In-Progress
109 1005 In-Progress
110 1005 Completed
111 1006 In-Progress
112 1006 Canceled
113 1007 In-Progress
114 1007 Pending with Student
我想在这些条件下输出:
在上述条件下,o/p 将如下所示:
Id companyId status
--------------------------------
104 1002 In-Progress
107 1004 In-Progress
108 1004 In-Progress
109 1005 In-Progress
111 1006 In-Progress
我们可以通过使用 NOT IN 来实现这一点
SELECT *
FROM student
WHERE status = 'In-Progress'
AND companyId NOT IN (SELECT companyId FROM student
WHERE status IN ('Final', 'Pending With Company ', 'Pending with Student'));
但我正在寻找一种不使用NOT IN
.
您可以使用左连接并选择不匹配的行来实现它:
SELECT * FROM student a LEFT JOIN
( SELECT companyId FROM student WHERE status in
('Final','Pending With Company ','Pending with Student')) b
on a.companyid=b.companyId
where b.companyId is null
and a.status = 'In-Progress'
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句