sql2005中,有表aa,只有一列id(INT类型)。表内数据如下:
1
2
3
5
6
8
9求教怎么能返回以下结果集:
1 3
5 6
8 9困扰了我很久,用游标的话,实在不行,因为数据量和id的跨度都非常大。哪位高手帮忙出个sql语句谢谢了!!
1
2
3
5
6
8
9求教怎么能返回以下结果集:
1 3
5 6
8 9困扰了我很久,用游标的话,实在不行,因为数据量和id的跨度都非常大。哪位高手帮忙出个sql语句谢谢了!!
drop table [aa]
Go
Create table [aa]([ID] int)
Insert [aa]
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 6 union all
select 8 union all
select 9
Go
select min(ID),max(ID)
from(
select ID,
cnt=cast(ID as int)-(select count(*)from [aa] n where m.ID>n.ID)
from [aa] m
)t
group by cnt
/*
----------- -----------
1 3
5 6
8 9
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([num] [int])
INSERT INTO [tb]
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '8' UNION ALL
SELECT '9'--SELECT * FROM [tb]-->SQL查询如下:
SELECT MIN(num) AS a, MAX(num) AS b
FROM (
SELECT px = ROW_NUMBER() OVER(ORDER BY num), *
FROM tb
) T
GROUP BY CAST(num AS INT)-px
/*
a b
----------- -----------
1 3
5 6
8 9(3 行受影响)
*/
insert #tt select 1
insert #tt select 2
insert #tt select 3
insert #tt select 5
insert #tt select 6
insert #tt select 8
insert #tt select 9
select MIN(id) as MIN_id,MAX(id) as MAX_id
from (
select id,id-ROW_NUMBER() over(order by id) as grp from #tt
) a
group by grp
MIN_id MAX_id
----------- -----------
1 3
5 6
8 9(3 行受影响)
FROM (
SELECT px = ROW_NUMBER() OVER(ORDER BY num), *
FROM tb
) T
GROUP BY num-pxnum不需要再Cast