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列的值延续上一行的值,改造一下查询的结果?
里面有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 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, 11GOSELECT 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,NumberAA 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