-------------------------------------------
--  Author : liangCK 小梁 & angellan 兰儿
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-05-29 17:35:40
-------------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,ids VARCHAR(2),status INT)
INSERT INTO @T
SELECT 1,'A0',1 UNION ALL
SELECT 2,'A1',1 UNION ALL
SELECT 3,'A2',0 UNION ALL
SELECT 4,'A3',1 UNION ALL
SELECT 5,'A4',0 UNION ALL
SELECT 6,管理器

------解决方案--------------------

SQL code
-------------------------------------------
--  Author : liangCK 小梁 & angellan 兰儿
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-05-29 17:35:40
-------------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,ids VARCHAR(2),status INT)
INSERT INTO @T
SELECT 1,'A0',1 UNION ALL
SELECT 2,'A1',1 UNION ALL
SELECT 3,'A2',0 UNION ALL
SELECT 4,'A3',1 UNION ALL
SELECT 5,'A4',0 UNION ALL
SELECT 6,'A5',1 UNION ALL
SELECT 7,'A6',1 UNION ALL
SELECT 8,'A7',1 UNION ALL
SELECT 9,'A8',1 UNION ALL
SELECT 10,'A9',0

--SQL查询如下:

DECLARE @n INT;
SET @n = 2;

SELECT A.*
FROM @T AS A
    JOIN (
        SELECT MIN(minid) AS minid
        FROM (
             SELECT MIN(id) AS minid
             FROM (
                  SELECT id,ids,status,
                       rowid=(
                           SELECT COUNT(*)
                           FROM @T
                           WHERE id<M.id AND status<>M.status
                       )
                  FROM @T AS M
             ) AS T
             WHERE status=1
             GROUP BY status,rowid
             HAVING COUNT(*) >= @n
        ) AS N
    ) AS B
ON A.id >= B.minid
    AND A.id < B.minid+@n

/*
id          ids  status
----------- ---- -----------
1           A0   1
2           A1   1

(2 row(s) affected)

*/

------解决方案--------------------
SQL code
select top 3 ids,status from #test
where status='1' and id not in (
select top 3 id from #test where status='1')
/*
A5    1
A6    1
A7    1
*/

------解决方案--------------------
SQL code
create table #test
(id int identity(1,1),ids nvarchar(3),status int ) 

;with Args as 
(
 select id,ids,status,id - num as nums 
 from 
 (
 select id,ids,status,row_number() over(partition by status order by status) num
 from #test
 )T
)
 select ids,max(status),nums from Args
 group by ids,nums
 having count(nums) = 3

------解决方案--------------------

SQL code
-------------------------------------------
--  Author : liangCK 小梁 & angellan 兰儿
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-05-29 17:35:40
-------------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,ids VARCHAR(3),status INT)
INSERT INTO @T
SELECT 1,'A0',1 UNION ALL
SELECT 2,'A1',1 UNION ALL
SELECT 3,'A2',0 UNION ALL
SELECT 4,'A3',1 UNION ALL
SELECT 5,'A4',0 UNION ALL
SELECT 6,'A5',1 UNION ALL
SELECT 7,'A6',1 UNION ALL
SELECT 8,'A7',1 UNION