分享一个有趣的双色球生成SQL供学习

最新推荐文章于 2024-06-27 18:08:19 发布

熊胖子丶

最新推荐文章于 2024-06-27 18:08:19 发布

阅读量115

点赞数 1

分类专栏：数据库分享文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/qq_57014285/article/details/134263893

版权

数据库分享专栏收录该内容

3 篇文章 1 订阅

订阅专栏

WITH    x0
          AS ( SELECT     CASE WHEN   number <10 THEN '0'+CAST(number AS VARCHAR) ELSE CAST(number AS VARCHAR) END  AS ball
               FROM     master.dbo.spt_values
               WHERE    type = 'P'
                        AND number <= 33
                        AND number >= 1
             ),/*随机生成6个红色球*/
        x1
          AS ( SELECT TOP 6
                        ball
               FROM     x0 
               ORDER BY NEWID()
             )

---第一组
SELECT  redball   AS 红色球 ,
            ( /*生成蓝色球*/ SELECT TOP 1
                            CASE WHEN   ball <10 THEN CAST(ball AS VARCHAR) ELSE CAST(ball AS VARCHAR) END
                        FROM    x0
                        WHERE   ball <= 16
                                AND ball NOT IN ( SELECT    ball
                                                  FROM      x1 ) 
                        ORDER BY NEWID()
            ) AS 蓝色球
    FROM    ( /*将红色球排到一行*/ SELECT   REPLACE(( SELECT    CAST(ball AS VARCHAR)
                                                        + ','
                                              FROM      x1  ORDER BY x1.ball 
                                            FOR
                                              XML PATH('')
                                            ), ',', ' ') AS redball
            ) t
---第二组
UNION ALL
    SELECT  redball   AS 红色球 ,
            ( /*生成蓝色球*/ SELECT TOP 1
                            CASE WHEN   ball <10 THEN CAST(ball AS VARCHAR) ELSE CAST(ball AS VARCHAR) END
                        FROM    x0
                        WHERE   ball <= 16
                                AND ball NOT IN ( SELECT    ball
                                                  FROM      x1 ) 
                        ORDER BY NEWID()
            ) AS 蓝色球
    FROM    ( /*将红色球排到一行*/ SELECT   REPLACE(( SELECT    CAST(ball AS VARCHAR)
                                                        + ','
                                              FROM      x1  ORDER BY x1.ball 
                                            FOR
                                              XML PATH('')
                                            ), ',', ' ') AS redball
            ) t
---第三组
UNION ALL
    SELECT  redball   AS 红色球 ,
            ( /*生成蓝色球*/ SELECT TOP 1
                            CASE WHEN   ball <10 THEN CAST(ball AS VARCHAR) ELSE CAST(ball AS VARCHAR) END
                        FROM    x0
                        WHERE   ball <= 16
                                AND ball NOT IN ( SELECT    ball
                                                  FROM      x1 ) 
                        ORDER BY NEWID()
            ) AS 蓝色球
    FROM    ( /*将红色球排到一行*/ SELECT   REPLACE(( SELECT    CAST(ball AS VARCHAR)
                                                        + ','
                                              FROM      x1  ORDER BY x1.ball 
                                            FOR
                                              XML PATH('')
                                            ), ',', ' ') AS redball
            ) t
---第四组
UNION ALL
    SELECT  redball   AS 红色球 ,
            ( /*生成蓝色球*/ SELECT TOP 1
                            CASE WHEN   ball <10 THEN CAST(ball AS VARCHAR) ELSE CAST(ball AS VARCHAR) END
                        FROM    x0
                        WHERE   ball <= 16
                                AND ball NOT IN ( SELECT    ball
                                                  FROM      x1 ) 
                        ORDER BY NEWID()
            ) AS 蓝色球
    FROM    ( /*将红色球排到一行*/ SELECT   REPLACE(( SELECT    CAST(ball AS VARCHAR)
                                                        + ','
                                              FROM      x1  ORDER BY x1.ball 
                                            FOR
                                              XML PATH('')
                                            ), ',', ' ') AS redball
            ) t
---第五组
UNION ALL
    SELECT  redball   AS 红色球 ,
            ( /*生成蓝色球*/ SELECT TOP 1
                            CASE WHEN   ball <10 THEN CAST(ball AS VARCHAR) ELSE CAST(ball AS VARCHAR) END
                        FROM    x0
                        WHERE   ball <= 16
                                AND ball NOT IN ( SELECT    ball
                                                  FROM      x1 ) 
                        ORDER BY NEWID()
            ) AS 蓝色球
    FROM    ( /*将红色球排到一行*/ SELECT   REPLACE(( SELECT    CAST(ball AS VARCHAR)
                                                        + ','
                                              FROM      x1  ORDER BY x1.ball 
                                            FOR
                                              XML PATH('')
                                            ), ',', ' ') AS redball
            ) t

查询结果