查询记录中 追加行记录 使数据连贯 改造查询结果

[李世东]

sql server2008 里面有一个表(ATable)没有主键
里面有2个字段(AA,BB),数据形式如下
AA BB
11 1
11 2
11 3 <---断开连续的数据,从查询结果查询出来能不能自动连贯上去?
11 5
11 6
11 7
22 1
22 2
22 3
22 4 <---断开连续的数据,从查询结果查询出来能不能自动连贯上去?
22 6
22 7

查询记录中 如何追加行记录 使BB列的数据断开连续的地方,再追加一行 查询结果出来能不能自动连贯上去,同时AA列的值延续上一行的值,改造一下查询的结果?

[百年树人]

if object_id('[atable]') is not null drop table [atable]
go
create table [atable]([AA] int,[BB] int)
insert [atable]
select 11,1 union all
select 11,2 union all
select 11,3 union all
select 11,5 union all
select 11,6 union all
select 11,7 union all
select 22,1 union all
select 22,2 union all
select 22,3 union all
select 22,4 union all
select 22,6 union all
select 22,7
go

select aa,b.number
from (select aa,min(bb) as minbb,max(bb) as maxbb from atable group by aa) a
join master..spt_values b on b.type='P'
and b.number between a.minbb and a.maxbb

/**
aa          number
----------- -----------
11          1
11          2
11          3
11          4
11          5
11          6
11          7
22          1
22          2
22          3
22          4
22          5
22          6
22          7

(14 行受影响)
**/

[孤独加百列]

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
	DROP TABLE tba
END
GO
CREATE TABLE tba
(
	AA INT,
	BB INT
) 
GO
INSERT INTO tba
SELECT 11, 1 UNION
SELECT 11, 2 UNION
SELECT 11, 3 UNION
SELECT 11, 5 UNION
SELECT 11, 6 UNION
SELECT 11, 7 UNION
SELECT 22, 1 UNION
SELECT 22, 2 UNION
SELECT 22, 3 UNION
SELECT 22, 4 UNION 
SELECT 22, 6 UNION
SELECT 22, 7 UNION
SELECT 33, 1 UNION
SELECT 33, 2 UNION
SELECT 33, 3 UNION
SELECT 33, 4 UNION 
SELECT 33, 8 UNION
SELECT 33, 11 UNION
SELECT 44, 2 UNION
SELECT 44, 3 UNION
SELECT 44, 4 UNION 
SELECT 44, 8 UNION
SELECT 44, 11

GO

SELECT DISTINCT AA,number
FROM Tba AS A FULL OUTER JOIN (SELECT number FROM master..spt_values WHERE type = 'P' AND number > 0)AS B ON number <= (SELECT MAX(BB) FROM tba WHERE AA = A.AA) AND number >= (SELECT MIN(BB) FROM tba WHERE AA = A.AA)
WHERE AA IS NOT NULL
ORDER BY AA,Number

AA	number
11	1
11	2
11	3
11	4
11	5
11	6
11	7
22	1
22	2
22	3
22	4
22	5
22	6
22	7
33	1
33	2
33	3
33	4
33	5
33	6
33	7
33	8
33	9
33	10
33	11
44	2
44	3
44	4
44	5
44	6
44	7
44	8
44	9
44	10
44	11

[潇洒王子]

借助系统表来实现，还是第一次见到，学习了

[百年树人]

[Quote=引用 4 楼 的回复:]
编程实现
[/Quote]

此回复得100分，经查实，属于倒分行为，发帖及得分者封杀1年

[lsd1020lsd]

编程实现