按行排除满足特定条件的记录

Question

我需要一些头脑风暴。我已经构建了一个查询，向我展示了我需要什么。但是，现在的要求是使用此记录列表并根据特定条件排除记录。

这是我构建的查询的当前输出：

Patient | Action | Date
james   | REG    | 2019/01/01
James   | CUR    | 2019/01/15
Jacon   | REG    | 2019/01/12
Jacob   | REG    | 2019/01/13
Main    | CUR    | 2019/01/01
Main    | REG    | 2019/01/05
Lucy    | REG    | 2019/01/08
Lucy    | CUR    | 2019/01/09
Lucy    | CUR    | 2019/01/10

根据上面的样本数据，我想删除第一个记录为"REG"且以下操作为"CUR"的任何患者。所以在这个例子中，我只想删除詹姆斯。

关于我应该做什么的任何想法？

感谢您的帮助！

Answer 1

请先使用dense_rank和row_number对数据进行分组，然后从临时表中受益，获取您正在寻找的数据。

CREATE TABLE #temp (Patient VARCHAR(50), Action VARCHAR(3))
Insert INTO #temp VALUES
('james','REG'),
('james','CUR'),
('Jacob','REG'),
('Jacob','REG'),
('Main','CUR'),
('Main','REG'),
('Lucy','REG'),
('Lucy','CUR'),
('Lucy','CUR')
SELECT *, DENSE_RANK() OVER (ORDER BY Patient ASC) GroupNo,
ROW_NUMBER() OVER (partition BY Patient ORDER BY Patient ASC) GroupOrder
INTO #PatientsWithGroup
FROM #temp
SELECT MIN(c1.GroupNo) GroupNo
INTO #PatsToEliminate
FROM #PatientsWithGroup c1
INNER JOIN #PatientsWithGroup c2 ON c1.GroupNo=c2.GroupNo
WHERE (c1.GroupOrder=1 AND c1.Action='REG') AND (c2.GroupOrder = 2 AND c2.Action='CUR')
HAVING COUNT(c1.Patient)<3
SELECT *
FROM #PatientsWithGroup p
WHERE p.GroupNo NOT IN (SELECT GroupNo FROM #PatsToEliminate)

Answer 2

您可以使用 LEAD 函数向前看。

CREATE TABLE #Patients (
ID int IDENTITY(1,1),
Patient varchar(50),
[Action] varchar(50)
);
INSERT INTO #Patients (Patient, [Action])
VALUES
('james', 'REG'),
('James', 'CUR'),
('Jacon', 'REG'),
('Jacob', 'REG'),
('Main',  'CUR'),
('Main',  'REG'),
('Lucy',  'REG'),
('Lucy',  'CUR'),
('Lucy',  'CUR');
SELECT * FROM #Patients;
WITH 
PatientWithNextAction AS (
SELECT 
Patient,
[Action],
LEAD([Action]) OVER(PARTITION BY Patient ORDER BY ID) NextAction
FROM 
#Patients 
)
DELETE 
FROM 
#Patients 
WHERE 
Patient IN (
SELECT
Patient
FROM
PatientWithNextAction
WHERE
[Action] = 'REG'
AND NextAction = 'CUR'
);
SELECT * FROM #Patients;

DROP TABLE #Patients;

Answer 3

试试这个：

select 1 as ordre, 'james' as Patient, 'REG' as Action into #tmp
union select 2,'James', 'CUR'
union select 3,'Jacon', 'REG'
union select 4,'Jacob', 'REG'
union select 5,'Main' , 'CUR'
union select 6,'Main' , 'REG'
union select 7,'Lucy' , 'REG'
union select 8,'Lucy' , 'CUR'
union select 9,'Lucy' , 'CUR'
;with cte as
(
select ordre, Patient, [Action], RANK () OVER ( 
PARTITION BY Patient
ORDER BY  ordre
) Patient_order  from #tmp a 
)
select * from cte a where not exists(select 1 from cte b where a.Patient = b.Patient and b.Patient_order = 1 and Action = 'REG'
and exists(select 1 from cte c where c.Patient = b.Patient and c.Patient_order = 2 and Action = 'CUR')
)