求一SQL,在线等!

Question

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

Answer 1

ids不一定连续,不过要status为1的连续记录.

Answer 2

-------------------------------------------
--  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 = 3;SELECT A.*
FROM @T AS A
    JOIN (
         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 B
ON A.id >= B.minid
    AND A.id < B.minid+@n/*
id          ids  status
----------- ---- -----------
6           A5   1
7           A6   1
8           A7   1(3 row(s) affected)*/

Answer 3

小梁牛人的还差一点点..当我设置@n = 2的时候,id          ids  status
----------- ---- -----------
1           A0   1
2           A1   1
6           A5   1
7           A6   1(4 row(s) affected)

Answer 4

-------------------------------------------
--  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)*/

Answer 5

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
*/

Answer 6

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

Answer 7

多谢小梁,还有一个小问题,就是当我设置@n>4的时候,我要取那段连续区间最大的,就是"A6-A9",

Answer 8

modify
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(*) = 3

Answer 9

modify 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(*) = 3

Answer 10

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 min(ids) + '-' + max(ids)
from Args 
group by nums
having count(*) = 4

Answer 11

-------------------------------------------
--  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;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 B
ON A.id >= B.minid
    AND A.id < B.minid+CASE WHEN @n > 4 THEN 4 ELSE @n END

Answer 12

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 B
ON A.id >= B.minid
    AND A.id < B.minid+CASE WHEN @n > 4 THEN 4 ELSE @n END这个4不是确定的数值,因为在这个例子中最长的记录区间是4,所以取最大的区间为4

Answer 13

-------------------------------------------
--  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 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 B
ON B.id BETWEEN A.minid AND A.maxid/*
id          ids  status
----------- ---- -----------
6           A5   1
7           A6   1
8           A7   1
9           A8   1(4 row(s) affected)
*/

Answer 14

declare @rc int
set @rc=3set rowcount @rcselect *
from #test
where id>=(
select top 1 id
from #test as ta
where exists(select 1 from #test where id between ta.id and ta.id+@rc-1 and status=1 having count(*)=@rc)
order by id
)
order by idset rowcount 0

Answer 15

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',0declare @i int
set @i = 3;with cte
as
(
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 t
where 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           4
7           A6   1           5
8           A7   1           6
*/

Answer 16

@n> 4 后就是取4个，直接判断一下@N 不要搞那复杂了

Answer 17

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
union all 
select 'A10',0declare @i int
set @i = 6;with cte
as
(
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
),
cte2
as
(select max(c) as maxcount from cte1) 
  
select t.*
from cte t,cte2 m
where 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           4
7           A6   1           5
8           A7   1           6
9           A8   1           7(4 行受影响)
*/

Answer 18

;with cte as
(select *,grp=id from #test where id=1
union all
select 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 minid
from (
select min(id) as minid,max(id) as maxid,status,count(*) as cnt
from cte
group by status,grp
) t
where status=1
order by cnt desc
)

Answer 19

http://www.madio.cn/tool/?fromuid=166987

Answer 20

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

Answer 21

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
union all 
select 'A10',0declare @i int
set @i = 6;with cte
as
(
    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
),
cte2
as
(select max(c) as maxcount from cte1) 
  
select t.*
from cte t,cte2 m
where 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           4
7           A6   1           5
8           A7   1           6
9           A8   1           7(4 行受影响)
*/

Answer 22

休假回来，闲着无聊，特地来看看小梁和石头的。顺便写了个sql2000的，没想太多，所以用了一堆临时表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.pxselect * 
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 = 6     --可以根据需要修改
select 
  id,ids,status 
from #4 t 
where 
  bid-aid>=case when @n>maxid then maxid else @n end 
and 
  (select count(1) from #4 where bid-aid>=@n and id<=t.id)<=@ndrop table #test,#1,#2,#3,#4/**
id          ids  status      
----------- ---- ----------- 
6           A5   1
7           A6   1
8           A7   1
9           A8   1（所影响的行数为 4 行）
**/

Answer 23

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;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 B
ON A.id >= B.minid
    AND A.id < B.minid+CASE WHEN @n > 4 THEN 4 ELSE @n END

Answer 24

ids不一定连续,不过要status为1的连续记录

Answer 25

也写一个，用的临时表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=1update 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 sselect 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