SQL Server:提取包含特定文本的行之后的行

Is there a way to extract ONLY the first row that come directly after the rows containing type = 'Email'?

是否有一种方法只提取包含type = 'Email'的行之后的第一行?

Sample table:

示例表:

id     type      details
1   Email   admin@sqlfiddle.com
2   1234    1234
3   Email   orange@hotmail.com
4   12345   12345
5   123456  123456
6   Email   cake@hotmail.com
7   1234567     1234567
8   12345678    12345678
9   123456789   123456789
10  Email   lala@gmail.com
11  01      01
12  Email   apple@yahoo.ca
13  012     012
14  Email   red@gmail.com
15  0123    0123

With Python and Pandas, I would write something like this...

用Python和熊猫，我会写这样的东西……

indexes = table[table['type']=='Email'].index + 1
table = table.ix[indexes]

Where the output would be...

输出是。

2   1234    1234
4   12345   12345
7   1234567     1234567
11  01  01
13  012     012
15  0123    0123

4 个解决方案

#1

select  *

from   (select  *
               ,lag (type) over (order by id) as prev_type

        from    t
        ) t

where   prev_type = 'Email'

#2

If you are specifically wanting just the first record after the email row you could be a bit more selective like this:

如果你特别想要的只是第一个记录后的电子邮件行，你可以有点选择性如下:

SELECT * FROM Table WHERE ID IN (SELECT ID+1 FROM Table where type='Email')

#3

For SQL Server Query like this

对于这样的SQL Server查询

select * from table where id in (Select id+1 from Table where type='Email')

#4

Here's a solution that should work even if id has gaps. It uses window functions.

这里有一个解决方案，即使id有差距也应该工作。它使用窗口功能。

---Sample data
WITH data([id], [type] , [details]) AS
(
    SELECT 1,'Email','admin@sqlfiddle.com' UNION ALL
    SELECT 2,'1234', '1234' UNION ALL
    SELECT 3,'Email','orange@hotmail.com' UNION ALL
    SELECT 4,'12345','12345' UNION ALL
    SELECT 5,'123456',  '123456' UNION ALL
    SELECT 6,'Email','cake@hotmail.com' UNION ALL
    SELECT 7,'1234567',  '1234567' UNION ALL
    SELECT 8,'12345678', '12345678' UNION ALL
    SELECT 9,'123456789','123456789' UNION ALL
    SELECT 10,  'Email','lala@gmail.com' UNION ALL
    SELECT 11,  '01','01' UNION ALL
    SELECT 12,  'Email','apple@yahoo.ca' UNION ALL
    SELECT 13 , '012',  '012' UNION ALL
    SELECT 14  ,'Email','red@gmail.com' 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')

#1

select  *

from   (select  *
               ,lag (type) over (order by id) as prev_type

        from    t
        ) t

where   prev_type = 'Email'

#2