;with cte as (select *,rn=row_number() over(order by getdate()) from tb)select a.name,a.id from cte a join cte b on b.rn=a.rn+1 and b.id=0 where a.id=1
ALTER TABLE TB ADD NEW_ID INT INDENTITY(1,1)SELECT name,id FROM TB WHERE id='1' and NEW_ID=(SELECT NEW_ID FROM TB WHERE id='0')+1
--SQL SERVER 2005以上版本使用row_number;with cte as (select *,rn=row_number() over(order by getdate()) from tb)SELECT a.name,a.id FROM cte a JOIN tb b ON a.rn=b.rn-1 and b.id=0
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([name] VARCHAR(1),[id] INT) INSERT [tb] SELECT 'A',1 UNION ALL SELECT 'B',1 UNION ALL SELECT 'C',1 UNION ALL SELECT 'D',0 UNION ALL SELECT 'E',1 UNION ALL SELECT 'F',1 UNION ALL SELECT 'G',0 UNION ALL SELECT 'H',1 UNION ALL SELECT 'I',1 --------------开始查询-------------------------- SELECT * FROM tb a WHERE [id] = 1 AND EXISTS ( SELECT 1 FROM tb b WHERE [name] > a.[name] AND [id] = 0 AND NOT EXISTS ( SELECT 1 FROM tb WHERE [name] > a.[name] AND [name] < b.[name] AND [id] = 1 ) ) /* name id ---- ----------- C 1 F 1(2 行受影响) */
use db;declare @t table(name varchar(10),id int); insert into @t values ('A', 1) ,('B', 1) ,('C', 1) ,('D', 0) ,('E', 1) ,('F', 1) ,('G', 0) ,('H', 1) ,('I', 1);--select * from @t;;with cte as ( select *,rn=row_number() over(order by getdate()) from @t ) select * from cte where rn in (select rn-1 from cte where id=0);/* name id rn ---------- ----------- -------------------- C 1 3 F 1 6*/
CREATE TABLE t1 ( col VARCHAR(2), id INT ) INSERT INTO t1 SELECT 'A', 1 UNION ALL SELECT 'B', 1 UNION ALL SELECT 'C', 1 UNION ALL SELECT 'D', 0 UNION ALL SELECT 'E', 1 UNION ALL SELECT 'F', 1 UNION ALL SELECT 'G', 0 UNION ALL SELECT 'H', 1 UNION ALL SELECT 'I', 1 SELECT * FROM t1;WITH aaa AS ( SELECT ROW_NUMBER() OVER(ORDER BY col) AS rowindex,* FROM t1 ) SELECT * FROM aaa AS a WHERE EXISTS (SELECT * FROM aaa AS b WHERE b.id=0 AND a.rowindex=b.rowindex-1)------------------------- rowindex col id 3 C 1 6 F 1
(select *,rn=row_number() over(order by getdate()) from tb)select a.name,a.id
from cte a
join cte b on b.rn=a.rn+1 and b.id=0
where a.id=1
ALTER TABLE TB ADD NEW_ID INT INDENTITY(1,1)SELECT name,id
FROM TB WHERE id='1' and NEW_ID=(SELECT NEW_ID FROM TB WHERE id='0')+1
(select *,rn=row_number() over(order by getdate()) from tb)SELECT a.name,a.id
FROM cte a
JOIN tb b ON a.rn=b.rn-1 and b.id=0
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] VARCHAR(1),[id] INT)
INSERT [tb]
SELECT 'A',1 UNION ALL
SELECT 'B',1 UNION ALL
SELECT 'C',1 UNION ALL
SELECT 'D',0 UNION ALL
SELECT 'E',1 UNION ALL
SELECT 'F',1 UNION ALL
SELECT 'G',0 UNION ALL
SELECT 'H',1 UNION ALL
SELECT 'I',1
--------------开始查询--------------------------
SELECT *
FROM tb a
WHERE [id] = 1
AND EXISTS ( SELECT 1
FROM tb b
WHERE [name] > a.[name]
AND [id] = 0
AND NOT EXISTS ( SELECT 1
FROM tb
WHERE [name] > a.[name]
AND [name] < b.[name]
AND [id] = 1 ) )
/*
name id
---- -----------
C 1
F 1(2 行受影响)
*/
跟这个童鞋问的一样
http://topic.csdn.net/u/20120802/14/6869b16e-c301-4022-af3d-cfdd63a235bc.html?96260
use db;declare @t table(name varchar(10),id int);
insert into @t values
('A', 1)
,('B', 1)
,('C', 1)
,('D', 0)
,('E', 1)
,('F', 1)
,('G', 0)
,('H', 1)
,('I', 1);--select * from @t;;with cte as (
select *,rn=row_number() over(order by getdate()) from @t
)
select * from cte where rn in (select rn-1 from cte where id=0);/*
name id rn
---------- ----------- --------------------
C 1 3
F 1 6*/
CREATE TABLE t1
(
col VARCHAR(2),
id INT
)
INSERT INTO t1
SELECT 'A', 1 UNION ALL
SELECT 'B', 1 UNION ALL
SELECT 'C', 1 UNION ALL
SELECT 'D', 0 UNION ALL
SELECT 'E', 1 UNION ALL
SELECT 'F', 1 UNION ALL
SELECT 'G', 0 UNION ALL
SELECT 'H', 1 UNION ALL
SELECT 'I', 1
SELECT * FROM t1;WITH aaa AS
(
SELECT ROW_NUMBER() OVER(ORDER BY col) AS rowindex,* FROM t1
)
SELECT * FROM aaa AS a WHERE EXISTS (SELECT * FROM aaa AS b WHERE b.id=0 AND a.rowindex=b.rowindex-1)-------------------------
rowindex col id
3 C 1
6 F 1