求一SQL,

求一SQL,在线等!

create table #test
(id int identity(1,1),ids nvarchar(3),status int )

insert into #test
select 'A0',1
union all
select 'A1',1
union all
select 'A2',0
union all
select 'A3',1
union all
select 'A4',0
union all
select 'A5',1
union all
select 'A6',1
union all
select 'A7',1
union all
select 'A8',1
union all
select 'A9',0


我想获取status=1的连续记录数大于某数(@n,int)的,比如我设置@n = 3
结果是:
'A5',1
'A6',1
'A7',1

[解决办法]
连续是指ids连续？
[解决办法]

SQL code

---------------------------------------------  Author : liangCK 小梁 & angellan 兰儿--  Comment: 小梁 爱 兰儿--  Date   : 2009-05-29 17:35:40------------------------------------------- --> 生成测试数据: @TDECLARE @T TABLE (id INT,ids VARCHAR(2),status INT)INSERT INTO @TSELECT 1,'A0',1 UNION ALLSELECT 2,'A1',1 UNION ALLSELECT 3,'A2',0 UNION ALLSELECT 4,'A3',1 UNION ALLSELECT 5,'A4',0 UNION ALLSELECT 6,管理器
[解决办法]
SQL code
---------------------------------------------  Author : liangCK 小梁 & angellan 兰儿--  Comment: 小梁 爱 兰儿--  Date   : 2009-05-29 17:35:40------------------------------------------- --> 生成测试数据: @TDECLARE @T TABLE (id INT,ids VARCHAR(2),status INT)INSERT INTO @TSELECT 1,'A0',1 UNION ALLSELECT 2,'A1',1 UNION ALLSELECT 3,'A2',0 UNION ALLSELECT 4,'A3',1 UNION ALLSELECT 5,'A4',0 UNION ALLSELECT 6,'A5',1 UNION ALLSELECT 7,'A6',1 UNION ALLSELECT 8,'A7',1 UNION ALLSELECT 9,'A8',1 UNION ALLSELECT 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 BON A.id >= B.minid    AND A.id < B.minid+@n/*id          ids  status----------- ---- -----------1           A0   12           A1   1(2 row(s) affected)*/
[解决办法]
SQL code
select top 3 ids,status from #testwhere status='1' and id not in (select top 3 id from #test where status='1')/*A5    1A6    1A7    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------------------------------------------- --> 生成测试数据: @TDECLARE @T TABLE (id INT,ids VARCHAR(3),status INT)INSERT INTO @TSELECT 1,'A0',1 UNION ALLSELECT 2,'A1',1 UNION ALLSELECT 3,'A2',0 UNION ALLSELECT 4,'A3',1 UNION ALLSELECT 5,'A4',0 UNION ALLSELECT 6,'A5',1 UNION ALLSELECT 7,'A6',1 UNION ALLSELECT 8,'A7',1 UNION ALLSELECT 9,'A8',1 UNION ALLSELECT 10,'A9',0 UNION ALLSELECT 11,'A10',0--SQL查询如下:DECLARE @n INT;SET @n = 6;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(*) >= CASE WHEN @n > 4 THEN 4 ELSE @n END        ) AS N    ) AS BON A.id >= B.minid    AND A.id < B.minid+CASE WHEN @n > 4 THEN 4 ELSE @n END 
 
[解决办法]
哦.这个交给兰儿解决.
[解决办法]
人都看晕了
[解决办法]
SQL code
---------------------------------------------  Author : liangCK 小梁 & angellan 兰儿--  Comment: 小梁 爱 兰儿--  Date   : 2009-05-29 17:35:40------------------------------------------- --> 生成测试数据: @TDECLARE @T TABLE (id INT,ids VARCHAR(3),status INT)INSERT INTO @TSELECT 1,'A0',1 UNION ALLSELECT 2,'A1',1 UNION ALLSELECT 3,'A2',0 UNION ALLSELECT 4,'A3',1 UNION ALLSELECT 5,'A4',0 UNION ALLSELECT 6,'A5',1 UNION ALLSELECT 7,'A6',1 UNION ALLSELECT 8,'A7',1 UNION ALLSELECT 9,'A8',1 UNION ALLSELECT 10,'A9',0 UNION ALLSELECT 11,'A10',0--SQL查询如下:DECLARE @n INT;SET @n = 6;WITH LiangAndLan AS(    SELECT MIN(id) AS minid,MAX(id) AS maxid,COUNT(*) AS cnt    FROM (         SELECT rowid=(                    SELECT COUNT(*)                    FROM @T                    WHERE id < A.id AND status<>A.status                ),*         FROM @T AS A    ) AS T    WHERE status=1    GROUP BY status,rowid)SELECT B.*FROM (    SELECT TOP(1) * FROM LiangAndLan WHERE cnt <= @n ORDER BY cnt DESC) AS A    JOIN @T AS BON B.id BETWEEN A.minid AND A.maxid/*id          ids  status----------- ---- -----------6           A5   17           A6   18           A7   19           A8   1(4 row(s) affected)*/
[解决办法]
SQL code
create table #test (id int identity(1,1),ids nvarchar(3),status int ) insert into #test select 'A0',1 union all select 'A1',1 union all select 'A2',0union all select 'A3',1 union all select 'A4',0 union all select 'A5',1 union all select 'A6',1 union all select 'A7',1 union all select 'A8',1 union all select 'A9',0declare @i intset @i = 3;with cteas(    select * ,rowid = row_number() over (order by getdate())    from #test a    where status = 1),cte1 as(    select  min(id) as minid ,min(id) + @i - 1 as maxid,count(1) as c    from cte    group by id -rowid)select *from cte twhere exists(select 1 from cte1 where t.id between minid and maxid and c >= @i) drop table #test/*id          ids  status      rowid----------- ---- ----------- --------------------6           A5   1           47           A6   1           58           A7   1           6*/
[解决办法]
SQL code
create table #test (id int identity(1,1),ids nvarchar(3),status int ) insert into #test select 'A0',1 union all select 'A1',1 union all select 'A2',0union all select 'A3',1 union all select 'A4',0 union all select 'A5',1 union all select 'A6',1 union all select 'A7',1 union all select 'A8',1 union all select 'A9',0union all select 'A10',0declare @i intset @i = 6;with cteas(    select * ,rowid = row_number() over (order by getdate())     from #test a    where status = 1),cte1 as(    select  min(id) as minid ,max(id) as maxid,count(1) as c    from cte    group by id -rowid),cte2as(select max(c) as maxcount from cte1)   select t.*from cte t,cte2 mwhere exists(select 1 from cte1 where t.id between minid and maxid and c >= case when @i > m.maxcount then m.maxcount else @i end) drop table #test/*id          ids  status      rowid----------- ---- ----------- --------------------6           A5   1           47           A6   1           58           A7   1           69           A8   1           7(4 行受影响)*/
[解决办法]
SQL code
;with cte as(select *,grp=id from #test where id=1union allselect a.*,case when a.status=b.status then grp else grp+1 end from #test as a join cte as b on a.id=b.id+1)select top 6 *from #test where status=1 and id>=(select top 1 minidfrom (select min(id) as minid,max(id) as maxid,status,count(*) as cntfrom ctegroup by status,grp) twhere status=1order by cnt desc)