SQL开发实例和优化是怎样的

这篇文章将为大家详细讲解有关SQL开发实例和优化是怎样的，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

找出连续的数据 如1,2,3,48,50,51,52,53,67,68

找出连续的数字的起点和重点

1,3

48,48

51,53

67,68

create table test.range_problem(

a int not null,

    primary key (a));

insert into test.range_problem values(1);

insert into test.range_problem values(2);

insert into test.range_problem values(3);

insert into test.range_problem values(48);

insert into test.range_problem values(50);

insert into test.range_problem values(51);

insert into test.range_problem values(52);

insert into test.range_problem values(53);

insert into test.range_problem values(66);

insert into test.range_problem values(67);

   --思路1 找最后一个不连续的数

   SELECT T1.A

   FROM test.range_problem T1

WHERE NOT EXISTS

   (

     SELECT A

     FROM test.range_problem T2

     WHERE T2.A -1 = T1.A

   )

   --思路2  找所有行对应的结束值

SELECT ROW_NUMBER()OVER(ORDER BY TBASE.A) ID

         ,TBASE.A

         ,(SELECT Min(A)

           FROM test.range_problem T1

           WHERE NOT EXISTS

           (

             SELECT A

FROM test.range_problem T2

             WHERE T2.A -1 = T1.A

           )

           AND T1.A >= TBASE.A  --T1.A 是3,48,53,67 TBASE.A 是每行的值

) A_END

   FROM test.range_problem TBASE

   --思路3 分组后找出所有连续的起始值和结束值

   SELECT MIN(A) A_START, A_END

     FROM(

SELECT ROW_NUMBER()OVER(ORDER BY TBASE.A) ID

           ,TBASE.A

           ,(SELECT Min(A)

             FROM test.range_problem T1

             WHERE NOT EXISTS

             (

SELECT A

               FROM test.range_problem T2

               WHERE T2.A -1 = T1.A

             )

AND T1.A >= TBASE.A  --T1.A 是3,48,53,67 TBASE.A 是每行的值

            ) A_END

     FROM test.range_problem TBASE

   ) T

   GROUP BY T.A_END

   --对上面语句的优化

   WITH POT AS(

           SELECT A

FROM test.range_problem T

           WHERE NOT EXISTS

           ( SELECT A

             FROM test.range_problem TC

             WHERE TC.A -1 = T.A)

         )

SELECT TBASE.A A_START,(SELECT MIN(A)

                               FROM  POT T

                               WHERE TBASE.A <= T.A 

                               ) A_END

         FROM test.range_problem TBASE

关于SQL开发实例和优化是怎样的就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。