寻最优SQL查询算法
比如有个表表结构如下：
---------------------------------------------
序号(Int) 起号(int) 止号(int)
1 1 4
2 7 9
3 5 6
4 11 13
5 14 20
-------------------------------------------------
需要查询出结果（2行）
序号(Int) 起号(int) 止号(int)
1 1 9
4 11 20
请问有什么好办法，效率要高！ SQL??算法?
[解决办法]
;with t as
select fb,fe from tb
union all
select a.fb,b.fe from t a
left join tb b on a.fe=b.fa-1
select min(fa),fb from (
select fa,max(fb) fb
from t
group by fa
) a
group by fb
CREATE TABLE T
(
序号 int,
起号 int,
止号 int
)
INSERT INTO T
SELECT 1,1,4
UNION ALL
SELECT 2,7,9
UNION ALL
SELECT 3,5,6
UNION ALL
SELECT 4,11,13
UNION ALL
SELECT 5,14,20
--方案1
;WITH CTE
AS
(
SELECT A.*
FROM T A
WHERE NOT EXISTS(SELECT 1 FROM T WHERE 止号=A.起号-1)
UNION ALL
SELECT Y.序号,NULL,X.止号
FROM T X
JOIN CTE Y ON X.起号=Y.止号+1
)
SELECT 序号,起号=MIN(起号),止号=MAX(止号)
FROM CTE
GROUP BY 序号
/*
序号起号止号
1 1 9
4 11 20
*/
--方案2
;WITH CTE1
AS
(
SELECT A.序号,A.起号,连接序号=ROW_NUMBER()OVER(ORDER BY 起号)
FROM T A
WHERE NOT EXISTS(SELECT 1 FROM T WHERE 止号=A.起号-1)
),
CTE2
AS
(
SELECT B.止号,连接序号=ROW_NUMBER()OVER(ORDER BY 止号)
FROM T B
WHERE NOT EXISTS(SELECT 1 FROM T WHERE 起号=B.止号+1)
)
SELECT C.序号,C.起号,D.止号
FROM CTE1 C
JOIN CTE2 D ON C.连接序号=D.连接序号
/*
序号起号止号
1 1 9
4 11 20
*/
--方案3，这里我也拿不准是TOP(1)配合ORDER BY效率高还是MIN效率高
;WITH CTE1
AS
(
SELECT A.序号,A.起号
FROM T A
WHERE NOT EXISTS(SELECT 1 FROM T WHERE 止号=A.起号-1)
),
CTE2
AS
(
SELECT B.止号
FROM T B
WHERE NOT EXISTS(SELECT 1 FROM T WHERE 起号=B.止号+1)
)
SELECT *,止号=(SELECT MIN(止号) FROM CTE2 WHERE 止号>CTE1.起号)
FROM CTE1
