SQL Server:提取包含特定文本的行之后的行
有没有办法仅提取包含type
='Email'的行之后的第一行?SQL Server:提取包含特定文本的行之后的行
样品表:
id type details
1 Email [email protected]
2 1234 1234
3 Email [email protected]
4 12345 12345
5 123456 123456
6 Email [email protected]
7 1234567 1234567
8 12345678 12345678
9 123456789 123456789
10 Email [email protected]
11 01 01
12 Email [email protected]
13 012 012
14 Email [email protected]
15
Python和熊猫,我会写这样的事情...
indexes = table[table['type']=='Email'].index + 1
table = table.ix[indexes]
对输出会...
2 1234 1234
4 12345 12345
7 1234567 1234567
11 01 01
13 012 012
15
select *
from (select *
,lag (type) over (order by id) as prev_type
from t
) t
where prev_type = 'Email'
对于像这样的SQL Server查询
select * from table where id in (Select id+1 from Table where type='Email')
如果是专门的电子邮件一行之后想只是第一个记录,你可能会多一点选择的是这样的:
SELECT * FROM Table WHERE ID IN (SELECT ID+1 FROM Table where type='Email')
只是注意到你的领域被称为ID,抱歉,编辑! – Conrad
谢谢康拉德!如果给出了一个ID,这很好,但如果没有给出,会如何。有没有一种广义的方式来做到这一点? –
你假设ID没有差距 –
这里是一个要工作,即使ID有差距的解决方案。它使用窗口函数。
---Sample data
WITH data([id], [type] , [details]) AS
(
SELECT 1,'Email','[email protected]' UNION ALL
SELECT 2,'1234', '1234' UNION ALL
SELECT 3,'Email','[email protected]' UNION ALL
SELECT 4,'12345','12345' UNION ALL
SELECT 5,'123456', '123456' UNION ALL
SELECT 6,'Email','[email protected]' UNION ALL
SELECT 7,'1234567', '1234567' UNION ALL
SELECT 8,'12345678', '12345678' UNION ALL
SELECT 9,'123456789','123456789' UNION ALL
SELECT 10, 'Email','[email protected]' UNION ALL
SELECT 11, '01','01' UNION ALL
SELECT 12, 'Email','[email protected]' UNION ALL
SELECT 13 , '012', '012' UNION ALL
SELECT 14 ,'Email','[email protected]' UNION ALL
SELECT 15 ,'0123', '0123'
),
---temporary table to hold row numbers
tbl([Row_Num], [id], [type]) AS
(
SELECT (ROW_NUMBER() OVER (ORDER BY [id])) AS [Row_Num] ,[id],[type] FROM data
)
---actual query using both tables
SELECT
d.[id],
d.[type],
d.[details]
FROM [data] d
INNER JOIN
[tbl] t
ON d.[id] = t.[id]
WHERE t.[Row_Num] IN (SELECT Row_Num + 1 FROM tbl WHERE [type] = 'Email')
太棒了!感谢Dudu - 这很好。 –
你是什么意思的“滞后”......是否有关键字或任何东西? – Darshak
lag是一个函数,你可以用它来获得下一个值 –