求一SQL,在线等----------------------续

yygyogfny 2009-06-01 09:57:49

原贴见:
http://topic.csdn.net/u/20090529/17/d4faa862-d0ed-4abb-a3f2-479da1fad7c4.html?seed=1332804210

首先感谢各位老大的回复,和管理员的推荐.

问题还没有得到完美的解决,

小梁第8楼 ,
兰儿第27楼,
无枪狙击手第36楼,
李晗第54楼
树人第73楼

等各位的回复,多少都还存在一些问题,请指教~~



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

当@n = 1,取第一个status为1的(A0)
当@n = 2,取第一,二个status为1的(A0,A1)
当@n = 3,(A5,A6,A7)
当@n = 4,(A5,A6,A7,A8)
当@n>4时,(A5,A6,A7,A8)

...全文

170 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

fwqkey 2009-06-25

打赏
举报

头晕

让你望见影子的墙 2009-06-24

打赏
举报

declare @t table (id int ,ids nvarchar(3),status int ,cnt int,grp int) 

insert into @t(id,ids,status)

select * from test

declare @cnt int,@grp int

set @cnt=0

set @grp=1



update s

set @cnt=case when status=(select status from @t where id=s.id+1) then @cnt+1 

         else case when status=1 then @cnt+1 else 0 end   end,

    cnt=@cnt,

    @grp=case when status=(select status from @t where id=s.id+1) or status=(select status from @t where id=s.id-1)

         then @grp else  @grp+1 end ,

    grp=@grp

from @t s



select distinct t.id,t.ids,t.status from @t s,@t t where s.grp=t.grp and s.cnt>6 and t.status=1

No_Data_Found 2009-06-24

打赏
举报

up 帮顶

claro 2009-06-01

打赏
举报

[Quote=引用 12 楼 zhaoyh0530 的回复:]
各位高手能把做的思想给解释，就太好了，代码有的太长根本别不过来个~学习中~~~
[/Quote]。。。。

--小F-- 2009-06-01

打赏
举报

继续关注

zhaoyh0530 2009-06-01

打赏
举报

各位高手能把做的思想给解释，就太好了，代码有的太长根本别不过来个~学习中~~~

ai_li7758521 2009-06-01

打赏
举报

学习

等不到来世 2009-06-01

打赏
举报

declare @n int

set @n=1            --这里指定@n值

--set @n=2

--set @n=3

--set @n=4

--set @n=5



--找出最大连续数

declare @n1 int

select @n1=max(n)

from 

(

	select n=count(1)

	from

	(

		select maxid=(select max(id) from #test b where not exists(select 1 from #test where id between a.id and b.id and status<>1))

		from #test a

		where status=1

	) t

	group by maxid

) tt

--设定实际连续数

set @n=case when @n>@n1 then @n1 else @n end

--选择结果

select top(@n) ids,status

from #test

where status=1

and id>=(select min(id) from #test t where not exists(select 1 from #test where id between t.id and t.id+@n-1 and status<>1))

order by id



--@n=1

/*

ids  status

---- -----------

A0   1



(1 行受影响)

*/

--@n=2

/*

ids  status

---- -----------

A0   1

A1   1



(2 行受影响)

*/

--@n=3

/*

ids  status

---- -----------

A5   1

A6   1

A7   1



(3 行受影响)

*/

--@n=4

/*

ids  status

---- -----------

A5   1

A6   1

A7   1

A8   1



(4 行受影响)

*/

--@n=5

/*

ids  status

---- -----------

A5   1

A6   1

A7   1

A8   1



(4 行受影响)

*/

liangCK 2009-06-01

打赏
举报

-------------------------------------------

--  Author : liangCK 小梁 & angellan 兰儿

--  Comment: 小梁 爱 兰儿

--  Date   : 2009-05-29 17:35:40

-------------------------------------------

 

--> 生成测试数据: T

CREATE TABLE T(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 ALL

SELECT 9,'A8',1 UNION ALL

SELECT 10,'A9',0 UNION ALL

SELECT 11,'A10',0



--SQL查询如下:

GO



CREATE PROC p @n INT

AS

	;WITH LiangAndLan AS

	(

		 SELECT TOP(1)

			 id,ids,status,class=1,rows=1

		 FROM T

		 WHERE status = 1

		 ORDER BY id

		 UNION ALL

		 SELECT 

			 B.id,B.ids,B.status,

			 CASE WHEN A.status=B.status THEN A.class ELSE A.class+1 END,

			 CASE WHEN A.status=B.status THEN A.rows+1 ELSE 1 END

		 FROM LiangAndLan AS A

			 JOIN T AS B

		 ON A.id=B.id-1

		 --WHERE CASE WHEN A.status=B.status THEN A.rows+1 ELSE 1 END > @n

	)

	SELECT A.id,A.ids,A.status

	FROM LiangAndLan AS A

		JOIN (

			SELECT TOP(1)  * FROM LiangAndLan 

			WHERE rows <= @n AND status = 1 ORDER BY rows DESC,class

		) AS B

	ON A.class=B.class AND A.rows < = B.rows;

GO



EXEC p 1;



EXEC p 2;



EXEC p 3;



EXEC p 4;



EXEC p 6;



GO



DROP PROC p

DROP TABLE T





/*

id          ids  status

----------- ---- -----------

1           A0   1



(1 row(s) affected)



id          ids  status

----------- ---- -----------

1           A0   1

2           A1   1



(2 row(s) affected)



id          ids  status

----------- ---- -----------

6           A5   1

7           A6   1

8           A7   1



(3 row(s) affected)



id          ids  status

----------- ---- -----------

6           A5   1

7           A6   1

8           A7   1

9           A8   1



(4 row(s) affected)



id          ids  status

----------- ---- -----------

6           A5   1

7           A6   1

8           A7   1

9           A8   1



(4 row(s) affected)

*/

liangCK 2009-06-01

打赏
举报

-------------------------------------------

--  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 ALL

SELECT 9,'A8',1 UNION ALL

SELECT 10,'A9',0 UNION ALL

SELECT 11,'A10',0



--SQL查询如下:





DECLARE @n INT;

SET @n = 6;



WITH LiangAndLan AS

(

     SELECT TOP(1)

         id,ids,status,class=1,rows=1

     FROM @T

     WHERE status = 1

     ORDER BY id

     UNION ALL

     SELECT 

         B.id,B.ids,B.status,

         CASE WHEN A.status=B.status THEN A.class ELSE A.class+1 END,

         CASE WHEN A.status=B.status THEN A.rows+1 ELSE 1 END

     FROM LiangAndLan AS A

         JOIN @T AS B

     ON A.id=B.id-1

     --WHERE CASE WHEN A.status=B.status THEN A.rows+1 ELSE 1 END > @n

)

SELECT A.id,A.ids,A.status

FROM LiangAndLan AS A

    JOIN (

        SELECT TOP(1)  * FROM LiangAndLan 

        WHERE rows <= @n AND status = 1 ORDER BY rows DESC,class

    ) AS B

ON A.class=B.class AND A.rows < = B.rows;

yygyogfny 2009-06-01

打赏
举报

[Quote=引用 6 楼 josy 的回复:]
这样？
[/Quote]

结果正确,谢谢树人.

百年树人 2009-06-01

打赏
举报

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 





-->Start

select * into #1 from #test t where status=1 and not exists(select 1 from #test where status=1 and id=t.id-1)

select * into #2 from #test t where status=1 and not exists(select 1 from #test where status=1 and id=t.id+1)



select a.id as aid,b.id as bid 

into #3

from

  (select *,px=(select count(1)+1 from #1 where id<t.id) from #1 t) a,

  (select *,px=(select count(1)+1 from #2 where id<t.id) from #2 t) b

where

  a.px=b.px



select * 

into #4

from #test a,#3 b,(select max(bid-aid) as maxid from #3) c

where 

  a.id between b.aid and b.bid



-->查询

declare @n int

set @n = 2     --可以根据需要修改

select 

  id,ids,status 

from #4 t 

where 

  bid-aid>=case when @n>=maxid then maxid else case when @n between aid and bid then bid-aid end end  

and 

  (select count(1) from #4 where bid-aid>=@n and id<=t.id)<=@n



drop table #test,#1,#2,#3,#4





/**

id          ids  status      

----------- ---- ----------- 

1           A0   1

2           A1   1



（所影响的行数为 2 行）

**/

这样？

liangCK 2009-06-01

打赏
举报

哦,,明白了.

yygyogfny 2009-06-01

打赏
举报

[Quote=引用 2 楼 liangCK 的回复:]
还没解决 ?
[/Quote]

各位的回复多少都还存在一定问题,我已经在原贴里都一一说明了.

ws_hgo 2009-06-01

打赏
举报

先看下

liangCK 2009-06-01

打赏
举报

还没解决 ?

yygyogfny 2009-06-01

打赏
举报

[Quote=引用楼主 yygyogfny 的帖子:]
原贴见:
http://topic.csdn.net/u/20090529/17/d4faa862-d0ed-4abb-a3f2-479da1fad7c4.html?seed=1332804210

当@n = 1,取第一个status为1的(A0)
[/Quote]

也可以取中有一个status为1的记录,比如:A3

lg3605119 2009-06-01

打赏
举报





--当@n = 1,取第一个status为1的(A0) 

--当@n = 2,取第一,二个status为1的(A0,A1) 

--当@n = 3,(A5,A6,A7) 

--当@n = 4,(A5,A6,A7,A8) 

--当@n>4时,(A5,A6,A7,A8) 

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 



declare @n int

set @n = 5



;with cte as

(

	select type = 1,* from #test where id = (select min(id) from #test)

    union all

    select type = case when a.status = b.status then b.type else b.type+1 end,a.* from #test a ,cte b where a.id = b.id+1

)

,cte1 as

(

	select *,px = row_number() over(partition by type order by id) from cte

)

select top(@n) id,ids,status from cte1 a 

where exists(select 1 from cte1 where a.type = type and px >=case when @n > (select max(px) from cte1) then (select max(px) from cte1) else @n end)

and px <=@n and status = 1

order by id 



drop table #test



/*

1	A0	1

2	A1	1

6	A5	1

7	A6	1

*/