现有表A及相关数据如下:
ID NAME STATUS
1 张三 0
2 李四 1
3 李四 0
4 张三 1
5 张三 1
6 王五 0
ID字段为自动增长列,按ID排序,需要得出连续出现两次 STATUS=1的NAME,如上表中那么应该显示的就是“张三”,请写出相关SQL语句 。
ID NAME STATUS
1 张三 0
2 李四 1
3 李四 0
4 张三 1
5 张三 1
6 王五 0
ID字段为自动增长列,按ID排序,需要得出连续出现两次 STATUS=1的NAME,如上表中那么应该显示的就是“张三”,请写出相关SQL语句 。
FROM tb AS A
WHERE EXISTS(
SELECT *
FROM tb
WHERE ID=A.ID+1 AND Status=1 AND A.Status=1
)
-- 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 行受影响)
*/
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 )
goselect 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 = 1drop table tb/*name
----------
张三(所影响的行数为 1 行)
*/
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 行)
*/
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+1id name status
----------- ---------- -----------
4 张三 1
5 张三 1(2 行受影响)