请教个ＳＱＬ面试题目？？？？

[500公里之外]

现有表A及相关数据如下：
ID 　　NAME STATUS
1 张三 0
2 李四 1
3 李四 0
4 张三 1
5 张三 1
6 王五 0
ID字段为自动增长列，按ID排序，需要得出连续出现两次 STATUS=1的NAME，如上表中那么应该显示的就是“张三”，请写出相关SQL语句 。

[liangCK]

SELECT *
FROM tb AS A
WHERE EXISTS(
          SELECT *
          FROM tb
          WHERE ID=A.ID+1 AND Status=1 AND A.Status=1
      )

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,NAME VARCHAR(4),STATUS INT)
INSERT INTO @T
SELECT 1,'张三',0 UNION ALL
SELECT 2,'李四',1 UNION ALL
SELECT 3,'李四',0 UNION ALL
SELECT 4,'张三',1 UNION ALL
SELECT 5,'张三',1 UNION ALL
SELECT 6,'王五',0

--SQL查询如下:

SELECT *
FROM @T AS A
WHERE EXISTS(
          SELECT *
          FROM @T
          WHERE ID=A.ID+1 AND Status=1 AND A.Status=1
      )

/*
ID          NAME STATUS
----------- ---- -----------
4           张三   1

(1 行受影响)


*/

[dawugui]

select distinct m.name from a m , b n where a.name = b.name and a.id = b.id - 1 and m.status = 1 and n.status = 1

[dawugui]

create table tb(ID int,NAME varchar(10), STATUS int)
insert into tb values(1 , '张三' , 0 )
insert into tb values(2 , '李四' , 1 )
insert into tb values(3 , '李四' , 0 )
insert into tb values(4 , '张三' , 1 )
insert into tb values(5 , '张三' , 1 )
insert into tb values(6 , '王五' , 0 )
go

select distinct m.name from tb m , tb n where m.name = n.name and m.id = n.id - 1 and m.status = 1 and n.status = 1

drop table tb

/*

name       
---------- 
张三

（所影响的行数为 1 行）
*/

[dawugui]

create table tb(ID int,NAME varchar(10), STATUS int)
insert into tb values(1 , '张三' , 0 )
insert into tb values(2 , '李四' , 1 )
insert into tb values(3 , '李四' , 0 )
insert into tb values(4 , '张三' , 1 )
insert into tb values(5 , '张三' , 1 )
insert into tb values(6 , '王五' , 0 )
go
--1
select distinct m.name from tb m , tb n where m.name = n.name and m.id = n.id - 1 and m.status = 1 and n.status = 1
/*
name       
---------- 
张三

（所影响的行数为 1 行）

*/

--2如果ID存在断号，先生成连续的号
select distinct m.name from 
(select id = (select count(1) from tb where id < t.id) + 1 , NAME , STATUS from tb t) m ,
(select id = (select count(1) from tb where id < t.id) + 1 , NAME , STATUS from tb t) n
where m.name = n.name and m.id = n.id - 1 and m.status = 1 and n.status = 1
/*
name       
---------- 
张三

（所影响的行数为 1 行）

*/

drop table tb

/*

name       
---------- 
张三

（所影响的行数为 1 行）
*/

[anwsp]

mark

[Roc_Lee]

[Quote=引用 4 楼 dawugui 的回复:]
SQL codecreate table tb(ID int,NAME varchar(10), STATUS int)
insert into tb values(1 , '张三' , 0 )
insert into tb values(2 , '李四' , 1 )
insert into tb values(3 , '李四' , 0 )
insert into tb values(4 , '张三' , 1 )
insert into tb values(5 , '张三' , 1 )
insert into tb values(6 , '王五' , 0 )
go

select distinct m.name from tb m , tb n where m.name = n.name and m.id = n.id - 1 and m.st…
[/Quote]
见wugui的

[ChinaJiaBing]

declare @A table (id int,name nvarchar(10),[status] int)
insert into @A select 1,'张三',0
     union all select 2,'李四',1
     union all select 3,'李四',0
     union all select 4,'张三',1
     union all select 5,'张三',1
     union all select 6,'王五',0
 select a.* from 
(select * from @A  where status= 1 ) a ,     
(select * from @A  where status= 1) b
where a.id=b.id-1
union all 
 select a.* from 
(select * from @A  where status= 1 ) a ,     
(select * from @A  where status= 1) b
where a.id=b.id+1

id          name       status
----------- ---------- -----------
4           张三         1
5           张三         1

(2 行受影响)