如何选择出现在特定值之前的行

Question

我有一个包含以下信息的表：

CREATE TABLE A (
ID int,
start_date varchar(255),
effdate varchar(255),
enddate varchar(255),
lead_effdate varchar(255),
value varchar(255),
source varchar(255),
lead_source varchar(255)
);
INSERT INTO A
VALUES (1,"12/01/2013","12/01/2013","30/07/2014","31/07/2014",4,"REM","REM"),
(1,"31/07/2014","31/07/2014","04/09/2014","12/09/2014",4,"REM","REM"),
(1,"12/09/2014","12/09/2014","12/04/2015","13/04/2015",4,"REM","REM"),
(1,"13/04/2015","13/04/2015","01/01/2016","02/01/2016",4,"REM","REM"),
(1,"02/01/2016","02/01/2016","11/03/2016","13/03/2016",4,"REM","REM"),
(1,"13/03/2016","13/03/2016","22/04/2017","10/07/2016",4,"REM","GS"),
(1,"10/07/2016","10/07/2016",null,"23/04/2017",4,"GS","REM"),
(1,"23/04/2017","23/04/2017","05/05/2018","06/05/2018",4,"REM","REM"),
(1,"06/05/2018","06/05/2018","09/11/2019","10/11/2019",4,"REM","REM"),
(1,"10/11/2019","10/11/2019",null,null,4,"REM",null);
select * from A
order by start_date

我想删除从source=GS到表中最低行的所有行。正如您所看到的，订单是由start_date给出的。

我试着在(partitionby…(上使用row_number((来创建GS周围的负值和正值，但我无法计算出这个索引。你将如何解决它？

Answer 1

如果通过"到底部"；你的意思是"；较大的id"；则可以在delete:中使用join

delete a
from a join
(select max(id) as gs_id
from a
where source = 'GS'
) aa
on a.id > aa.gs_id;

Answer 2

您希望选择日期等于或晚于source='GS'的行的所有行。由于source = 'GS'可能有多行，因此您必须决定一行。可能是第一个或最后一个。

delete from a where start_date >= (select min(start_date) from a where source = 'GS');