请教SQL语句怎么写？

[xga359]

表：AA
查询st=1 并且时间小于它的第一条数据。

[RINK_1]

SELECT *
FROM
(SELECT B.*
FROM TABLE A
CROSS APPLY (SELECT TOP 1 * FROM TABLE WHERE TIME<A.TIME AND ST<>1 ORDER BY TIME DESC) AS B
WHERE A.ST=1
UNION ALL
SELECT * FROM TABLE WHERE ST=1) AS A
ORDER BY TIME

[Dear SQL(燊)]

with list as(
	select id,[name],[time],st
		,rid=ROW_NUMBER()over(order by [time])
	from aa
)
select id,[name],[time],st
from list
where st=1
union all
select b.id,b.[name],b.[time],b.st
from list a
inner join list b on a.rid=b.rid-1
where a.st=1

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('AA') IS NOT NULL DROP TABLE AA
GO
CREATE TABLE AA(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	[Name] NVARCHAR(10),
	[Time] DATETIME,
	ST INT	
)
GO
SET NOCOUNT ON
INSERT INTO AA VALUES ('AA','2018-12-01 11:30:00',1);
INSERT INTO AA VALUES ('BB','2018-12-01 11:29:00',0);
INSERT INTO AA VALUES ('CC','2018-12-01 11:33:00',0);
INSERT INTO AA VALUES ('DD','2018-12-01 11:32:00',1);
INSERT INTO AA VALUES ('EE','2018-12-01 11:30:00',0);
INSERT INTO AA VALUES ('FF','2018-12-01 11:30:00',0);
INSERT INTO AA VALUES ('GG','2018-12-01 11:28:00',1);
INSERT INTO AA VALUES ('HH','2018-12-01 11:23:00',0);
INSERT INTO AA VALUES ('JJ','2018-12-01 11:00:00',0);

;WITH cte AS(
SELECT * FROM AA WHERE st=1
)
SELECT * FROM cte AS c
UNION
SELECT distinct a.* 
FROM cte AS b CROSS APPLY
(
	select TOP 1 a.*
	from AA AS a
	WHERE a.[Time]<b.[Time]
	AND a.id NOT IN ( SELECT id FROM cte) 
	ORDER BY a.[Time] DESC
)
AS a
ORDER BY [Id]  

/*
Id          Name       Time                    ST
----------- ---------- ----------------------- -----------
1           AA         2018-12-01 11:30:00.000 1
2           BB         2018-12-01 11:29:00.000 0
4           DD         2018-12-01 11:32:00.000 1
5           EE         2018-12-01 11:30:00.000 0
7           GG         2018-12-01 11:28:00.000 1
8           HH         2018-12-01 11:23:00.000 0
*/