只显示正在进行的记录，不显示最终记录

Question

我有下表CandidateInterview:

状态正在进行中

CandidateID	面试ID
100	12	尚未
100	13

您可以为此使用窗口函数

窗口函数通常比相关子查询更有效率

SELECT
CandidateID,
InterviewID,
Status
FROM (
SELECT *,
OtherStatus = COUNT(CASE WHEN Status > 1 THEN 1 END) OVER (PARTITION BY CandidateID)
FROM CandidateInterview ci
) ci
WHERE OtherStatus = 0;

SQL Fiddle

SQL Fiddle

MS SQL Server 2017架构设置:

CREATE TABLE CandidateInterview (CandidateID int, InterviewID int,
Status int)
INSERT INTO CandidateInterview VALUES 
(100     ,    12     ,     1),
(100      ,   13     ,     2),
(120     ,     9      ,    1)

查询1：

SELECT * FROM CandidateInterview AS A
WHERE NOT EXISTS 
(
SELECT * FROM CandidateInterview AS B
WHERE A.CandidateID=B.CandidateID
AND Status >1


)

结果：

| CandidateID | InterviewID | Status |
|-------------|-------------|--------|
|         120 |           9 |      1 |