数据库环境：SQL SERVER 2005
以前用C/JAVA穷举双色球的所有排列，今天想着换成用SQL实现，只生成一柱双色球。
简单说下双色球的规则，双色球由红色球和蓝色球组成，每注投注号码由6个红色球号码和1个蓝色球号码组成。
红色球号码从1--33中选择；蓝色球号码从1--16中选择。同时，一柱号码中，蓝色球不能出现在红色球里。
规则讲完了，我们来看实现，直接上代码，代码里有注释，应该都能看懂
/*生成1-33个号码的球*/
WITH x0
AS ( SELECT number 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
ball
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
FOR
XML PATH('')
), ',', ' ') AS redball
) t
这样就实现了吗？？？
没有！！！
这里挖了一个坑，我们先看下意外的结果
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAALUAAAAuCAIAAADhkM9+AAADoklEQVR4nO2az2rbQBDG551iG9p3EUaH+EEMhpxKHyGHEhDdngJ5Bh8MuhZ6btElYPtqQ3qQLa129p/tHWukzMdgxK60kmZ++2WlCD6GJqXUv5Cen388PCxwBA9MIqXUdiyCvst9sZRSf3lL+OhTSnRHDY8P0T114uPnr1cJCRwtH0dKVbsD6fhDFM+cVLuD3T+oz0o6/hDFMyfCBxfxzAkvPgAg5vDI3Yz9Lz3qzorPifVGiHLClA/oytgHy9prHTbxnaQTzon1ToN3nTYnjPiImSiRUwcjdfEl3l1X+wdpTrjwYcX5ukQcNfP0zCpu8qw/PHOdOieM+MDXak1H0GzxgZyxaOTxVJ0PfMukOeHCR60YPvzbRnb0SZPiwgkV5MPlIqQ5YcSHnwbc4trGXUZqeCrGP4z2+G3cFZkTLnxYXdGal0u9dBBwHG94/0GaEy58WBXjKP4Drf7MUy7/CNaeNCe8+IjxD9eYLv88otTwlP9vrlHOSD5uzwkXPrAfHq/iI3IchqLj45accOFDV9BOg5aLR0t8AwRK/v40SU448vE5xTMnwgcX8cyJk49qd5CQqFx8kH8JDSC/xi9DfXTV4YPOtbbbbfW+l9ADgN3H4X3yAQC9l4RV4GL0LvEPRiH+0eFD/MOI0fjH6yM83vj4G/IPlUH+Emjxdb3MYbIqzT2L/PTOaK4qtH+trDBb8M7r1azuwafAR+GRXRHyj7cFLN4CLYl1KR+/v32pbzYBHw7/KJfT+hRNyXGLZ+d99b6vNk8TS/FUdi6SSU+Rw/RpraO2eZqcWsrldLbcGIPXp1MZdLrWq9kZi3I5haywjXyNf/z5/rW+zYYG3EKiUfpHuZzOsvnM5KMtlSfONBS5bgCdqd+ig7q6HtO9AMTZGP0jJR/e9ccNfBQ5zBUqjz653XH2Bv1wd6U9l3T2D4vrXOEftT4ZHzT+cdq28jGZ5xPHkqI6ryrqonr5OP39AnD6wXo1071KH1n8I5YPCv9oamn3j3ZJ4arWqcvHh2dpYoPD6SjiH34+CPyjWa6C5fmiyH2WoNvMqvSsP/zW4oAjcFLxDwsfVOsPZzE6GBklN587PCbRrnORJRS58SRlGVn8I5IPAv8ITdaI9x/NUZ73H66utl0bCo88ev9IIHl/imM070/T8NF7PbiF/P+lw4f4hxHiHx0+eq8Htxief9B9sib+gWN4/kEtYPBJH7dfVsLEtHyQgikaqIQPkU/Ch8in/5URsT/w3m+ZAAAAAElFTkSuQmCC" alt="" />
蓝色球和红色球出现重号
问题就出现在我用cte生成随机的6个红色球，每次调用x1的时候，这6个数都会随机生成，所以，才会出现这样的结果。
问题已经查明了，我们把6个红色球存到临时表里头，就不会每次调用的时候会发生改变。
更改后的SQL如下：
/*生成1-33号码的球*/
SELECT number AS ball
INTO #t0
FROM master.dbo.spt_values
WHERE type = 'P'
AND number <= 33
AND number >= 1
/*生成6个红色球*/
SELECT TOP 6
ball
INTO #t1
FROM #t0
ORDER BY NEWID() SELECT redball AS 红色球 ,
( /*生成蓝色球*/ SELECT TOP 1
ball
FROM #t0
WHERE ball <= 16
AND ball NOT IN ( SELECT ball
FROM #t1 )
ORDER BY NEWID()
) AS 蓝色球
FROM ( /*将红色球排到一行*/ SELECT REPLACE(( SELECT CAST(ball AS VARCHAR)
+ ','
FROM #t1
FOR
XML PATH('')
), ',', ' ') AS redball
) t
经网友Yuanet在评论中提醒，双色球的规则，蓝色球是可以出现在红色球中的。
因此，我对原来的代码做一些改动，使计算结果符合双色球的规则。
/*生成1-33个号码的球*/
WITH x0
AS ( SELECT number 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 红色球 ,
CAST(RAND()*16+1 AS INT) AS 蓝色球
FROM ( /*将红色球排到一行*/ SELECT REPLACE(( SELECT CAST(ball AS VARCHAR)
+ ','
FROM x1
FOR
XML PATH('')
), ',', ' ') AS redball
) t