当前位置：首页 → 问答吧 → 足彩任九扩展一场算法程序

足彩任九扩展一场算法程序

时间：2011-11-24

来源：互联网

有一任九单式表，如下表
ID F1 F2，F3，F4，F5，F6，F7，F8，F9，F10，F11，F12，F13，F14
1 * 3 * * 3 3 0 0 * * 3 0 3 1
2 * 3 * * 3 3 0 0 * * 3 0 3 0

...

现在对选中的9比赛进行扩展（也就是有的足彩过滤软件里面的扩展一场，0就扩展为3和1；1就扩展为3和0，3就扩展为1和0，*号为不选中的场次，不进行扩展），比方第一注扩展后的号码为（包括被扩展的的号码在内，扩展后号码共有19注）：
*3**3300**3031
*3**3300**3033
*3**3300**3030
*3**3300**3011
*3**3300**3001
*3**3300**3331
*3**3300**3131
*3**3300**1031
*3**3300**0031
*3**3303**3031
*3**3301**3031
*3**3330**3031
*3**3310**3031
*3**3100**3031
*3**3000**3031
*3**1300**3031
*3**0300**3031
*1**3300**3031
*0**3300**3031
当表中的所有记录进行扩展后，有可能会用相同的号码，相同的号码要求保留（假设表中有20条记录，那么扩展后就有20*19=380条记录，而不是少于380条记录--像一些软件扩展后是去掉重复注的，号码就少于380注）
请教如何实现？

作者: doubagui 发布时间: 2011-11-24

什么？排序有点乱

作者: ssp2009 发布时间: 2011-11-24

有一任九单式表，如下表
ID F1 F2 F3 F4 F5 F6 F7 F8 F9 F10 F11 F12 F13 F14
1 * 3 * * 3 3 0 0 * * 3 0 3 1
2 * 3 * * 3 3 0 0 * * 3 0 3 0

......

作者: doubagui 发布时间: 2011-11-24

彩票啊，中了分我多少

作者: Beirut 发布时间: 2011-11-24

引用 3 楼 beirut 的回复:
彩票啊，中了分我多少

都有份，呵呵！

作者: doubagui 发布时间: 2011-11-24

写了一个，楼主看看可以不：
SQL code


--演示数据
create table lottery(ID int, F1 CHAR(1), F2 CHAR(1), F3 CHAR(1), F4 CHAR(1), F5 CHAR(1), F6 CHAR(1), F7 CHAR(1), 
    F8 CHAR(1), F9 CHAR(1), F10 CHAR(1), F11 CHAR(1), F12 CHAR(1), F13 CHAR(1), F14 CHAR(1))
go
insert lottery values('1','*','3','*','*','3','3','0','0','*','*','3','0','3','1')
insert lottery values('2','*','3','*','*','3','3','0','0','*','*','3','0','3','0')
go

--具体语句
WITH 
t310 AS(SELECT Val='3' UNION ALL SELECT Val='1' UNION ALL SELECT Val='0'),
t1 AS (SELECT ID,ISNULL(b.Val,'*') F1,CASE WHEN a.F1=b.Val OR a.F1='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F1 <> '*'),
t2 AS (SELECT ID,ISNULL(b.Val,'*') F2,CASE WHEN a.F2=b.Val OR a.F2='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F2 <> '*'),
t3 AS (SELECT ID,ISNULL(b.Val,'*') F3,CASE WHEN a.F3=b.Val OR a.F3='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F3 <> '*'),
t4 AS (SELECT ID,ISNULL(b.Val,'*') F4,CASE WHEN a.F4=b.Val OR a.F4='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F4 <> '*'),
t5 AS (SELECT ID,ISNULL(b.Val,'*') F5,CASE WHEN a.F5=b.Val OR a.F5='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F5 <> '*'),
t6 AS (SELECT ID,ISNULL(b.Val,'*') F6,CASE WHEN a.F6=b.Val OR a.F6='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F6 <> '*'),
t7 AS (SELECT ID,ISNULL(b.Val,'*') F7,CASE WHEN a.F7=b.Val OR a.F7='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F7 <> '*'),
t8 AS (SELECT ID,ISNULL(b.Val,'*') F8,CASE WHEN a.F8=b.Val OR a.F8='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F8 <> '*'),
t9 AS (SELECT ID,ISNULL(b.Val,'*') F9,CASE WHEN a.F9=b.Val OR a.F9='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F9 <> '*'),
t10 AS (SELECT ID,ISNULL(b.Val,'*') F10,CASE WHEN a.F10=b.Val OR a.F10='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F10 <> '*'),
t11 AS (SELECT ID,ISNULL(b.Val,'*') F11,CASE WHEN a.F11=b.Val OR a.F11='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F11 <> '*'),
t12 AS (SELECT ID,ISNULL(b.Val,'*') F12,CASE WHEN a.F12=b.Val OR a.F12='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F12 <> '*'),
t13 AS (SELECT ID,ISNULL(b.Val,'*') F13,CASE WHEN a.F13=b.Val OR a.F13='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F13 <> '*'),
t14 AS (SELECT ID,ISNULL(b.Val,'*') F14,CASE WHEN a.F14=b.Val OR a.F14='*' THEN 0 ELSE 1 END ID2 FROM lottery a LEFT JOIN t310 b on a.F14 <> '*')

SELECT t1.ID,t1.F1,t2.F2,t3.F3,t4.F4,t5.F5,t6.F6,t7.F7,t8.F8,t9.F9,t10.F10,t11.F11,t12.F12,t13.F13,t14.F14
FROM t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14 
WHERE t1.ID=t2.ID and t1.ID=t3.ID and t1.ID=t4.ID
    and t1.ID=t5.ID and t1.ID=t6.ID and t1.ID=t7.ID
    and t1.ID=t8.ID and t1.ID=t9.ID and t1.ID=t10.ID
    and t1.ID=t11.ID and t1.ID=t12.ID and t1.ID=t13.ID
    and t1.ID=t14.ID
    and t1.ID2+t2.ID2+t3.ID2+t4.ID2+t5.ID2+t6.ID2+t7.ID2+t8.ID2+t9.ID2+t10.ID2+t11.ID2+t12.ID2+t13.ID2+t14.ID2<=1 
--此处的1可以改成其他数字，表示任意扩展几场

ID          F1   F2   F3   F4   F5   F6   F7   F8   F9   F10  F11  F12  F13  F14
----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1           *    3    *    *    3    3    3    0    *    *    3    0    3    1
1           *    3    *    *    3    3    1    0    *    *    3    0    3    1
1           *    3    *    *    3    3    0    3    *    *    3    0    3    1
1           *    3    *    *    3    3    0    1    *    *    3    0    3    1
1           *    3    *    *    3    3    0    0    *    *    3    3    3    1
1           *    3    *    *    3    3    0    0    *    *    3    1    3    1
1           *    3    *    *    3    3    0    0    *    *    3    0    3    3
1           *    3    *    *    3    3    0    0    *    *    3    0    3    1
1           *    3    *    *    3    3    0    0    *    *    3    0    3    0
1           *    3    *    *    3    3    0    0    *    *    3    0    1    1
1           *    3    *    *    3    3    0    0    *    *    3    0    0    1
1           *    3    *    *    3    3    0    0    *    *    1    0    3    1
1           *    3    *    *    3    3    0    0    *    *    0    0    3    1
1           *    3    *    *    3    1    0    0    *    *    3    0    3    1
1           *    3    *    *    3    0    0    0    *    *    3    0    3    1
1           *    3    *    *    1    3    0    0    *    *    3    0    3    1
1           *    3    *    *    0    3    0    0    *    *    3    0    3    1
1           *    1    *    *    3    3    0    0    *    *    3    0    3    1
1           *    0    *    *    3    3    0    0    *    *    3    0    3    1
2           *    3    *    *    3    3    3    0    *    *    3    0    3    0
2           *    3    *    *    3    3    1    0    *    *    3    0    3    0
2           *    3    *    *    3    3    0    3    *    *    3    0    3    0
2           *    3    *    *    3    3    0    1    *    *    3    0    3    0
2           *    3    *    *    3    3    0    0    *    *    3    3    3    0
2           *    3    *    *    3    3    0    0    *    *    3    1    3    0
2           *    3    *    *    3    3    0    0    *    *    3    0    3    3
2           *    3    *    *    3    3    0    0    *    *    3    0    3    1
2           *    3    *    *    3    3    0    0    *    *    3    0    3    0
2           *    3    *    *    3    3    0    0    *    *    3    0    1    0
2           *    3    *    *    3    3    0    0    *    *    3    0    0    0
2           *    3    *    *    3    3    0    0    *    *    1    0    3    0
2           *    3    *    *    3    3    0    0    *    *    0    0    3    0
2           *    3    *    *    3    1    0    0    *    *    3    0    3    0
2           *    3    *    *    3    0    0    0    *    *    3    0    3    0
2           *    3    *    *    1    3    0    0    *    *    3    0    3    0
2           *    3    *    *    0    3    0    0    *    *    3    0    3    0
2           *    1    *    *    3    3    0    0    *    *    3    0    3    0
2           *    0    *    *    3    3    0    0    *    *    3    0    3    0

(38 行受影响)

作者: coleling 发布时间: 2011-11-24

SQL code


--借用五楼数据
create table lottery(ID int, F1 CHAR(1), F2 CHAR(1), F3 CHAR(1), F4 CHAR(1), F5 CHAR(1), F6 CHAR(1), F7 CHAR(1), 
    F8 CHAR(1), F9 CHAR(1), F10 CHAR(1), F11 CHAR(1), F12 CHAR(1), F13 CHAR(1), F14 CHAR(1))
go
insert lottery colues('1','*','3','*','*','3','3','0','0','*','*','3','0','3','1')
insert lottery colues('2','*','3','*','*','3','3','0','0','*','*','3','0','3','0')
go

with cte as
 (SELECT col='3' UNION ALL SELECT col='1' UNION ALL SELECT col='0') 
select id,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14=b.col
 from lottery a,cte b where isnumeric(F14)=1
 union 
select id,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13=b.col,F14
 from lottery a,cte b where isnumeric(F13)=1
 union 
 select id,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12=b.col,F13,F14
 from lottery a,cte b where isnumeric(F12)=1
 union 
 select id,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11=b.col,F12,F13,F14
 from lottery a,cte b where isnumeric(F11)=1
 union 
 select id,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10=b.col,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F10)=1
 union 
 select id,F1,F2,F3,F4,F5,F6,F7,F8,F9=b.col,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F9)=1
 union 
 select id,F1,F2,F3,F4,F5,F6,F7,F8=b.col,F9,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F8)=1
 union 
 select id,F1,F2,F3,F4,F5,F6,F7=b.col,F8,F9,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F7)=1
 union 
 select id,F1,F2,F3,F4,F5,F6=b.col,F7,F8,F9,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F6)=1
 union 
 select id,F1,F2,F3,F4,F5=b.col,F6,F7,F8,F9,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F5)=1
 union 
 select id,F1,F2,F3,F4=b.col,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F4)=1
 union 
 select id,F1,F2,F3=b.col,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F3)=1
 union 
 select id,F1,F2=b.col,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F2)=1
 union 
 select id,F1=b.col,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14
 from lottery a,cte b where isnumeric(F1)=1
 order by id
 
/*
id          F1   F2   F3   F4   F5   F6   F7   F8   F9   F10  F11  F12  F13  F14
----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1           *    0    *    *    3    3    0    0    *    *    3    0    3    1
1           *    1    *    *    3    3    0    0    *    *    3    0    3    1
1           *    3    *    *    0    3    0    0    *    *    3    0    3    1
1           *    3    *    *    1    3    0    0    *    *    3    0    3    1
1           *    3    *    *    3    0    0    0    *    *    3    0    3    1
1           *    3    *    *    3    1    0    0    *    *    3    0    3    1
1           *    3    *    *    3    3    0    0    *    *    0    0    3    1
1           *    3    *    *    3    3    0    0    *    *    1    0    3    1
1           *    3    *    *    3    3    0    0    *    *    3    0    0    1
1           *    3    *    *    3    3    0    0    *    *    3    0    1    1
1           *    3    *    *    3    3    0    0    *    *    3    0    3    0
1           *    3    *    *    3    3    0    0    *    *    3    0    3    1
1           *    3    *    *    3    3    0    0    *    *    3    0    3    3
1           *    3    *    *    3    3    0    0    *    *    3    1    3    1
1           *    3    *    *    3    3    0    0    *    *    3    3    3    1
1           *    3    *    *    3    3    0    1    *    *    3    0    3    1
1           *    3    *    *    3    3    0    3    *    *    3    0    3    1
1           *    3    *    *    3    3    1    0    *    *    3    0    3    1
1           *    3    *    *    3    3    3    0    *    *    3    0    3    1
2           *    0    *    *    3    3    0    0    *    *    3    0    3    0
2           *    1    *    *    3    3    0    0    *    *    3    0    3    0
2           *    3    *    *    0    3    0    0    *    *    3    0    3    0
2           *    3    *    *    1    3    0    0    *    *    3    0    3    0
2           *    3    *    *    3    0    0    0    *    *    3    0    3    0
2           *    3    *    *    3    1    0    0    *    *    3    0    3    0
2           *    3    *    *    3    3    0    0    *    *    0    0    3    0
2           *    3    *    *    3    3    0    0    *    *    1    0    3    0
2           *    3    *    *    3    3    0    0    *    *    3    0    0    0
2           *    3    *    *    3    3    0    0    *    *    3    0    1    0
2           *    3    *    *    3    3    0    0    *    *    3    0    3    0
2           *    3    *    *    3    3    0    0    *    *    3    0    3    1
2           *    3    *    *    3    3    0    0    *    *    3    0    3    3
2           *    3    *    *    3    3    0    0    *    *    3    1    3    0
2           *    3    *    *    3    3    0    0    *    *    3    3    3    0
2           *    3    *    *    3    3    0    1    *    *    3    0    3    0
2           *    3    *    *    3    3    0    3    *    *    3    0    3    0
2           *    3    *    *    3    3    1    0    *    *    3    0    3    0
2           *    3    *    *    3    3    3    0    *    *    3    0    3    0

(38 行受影响)