足彩任九扩展一场算法程序
有一任九单式表，如下表
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注）
请教如何实现？
[解决办法]
写了一个，楼主看看可以不：
--演示数据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))goinsert 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.F14FROM 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 11 * 3 * * 3 3 1 0 * * 3 0 3 11 * 3 * * 3 3 0 3 * * 3 0 3 11 * 3 * * 3 3 0 1 * * 3 0 3 11 * 3 * * 3 3 0 0 * * 3 3 3 11 * 3 * * 3 3 0 0 * * 3 1 3 11 * 3 * * 3 3 0 0 * * 3 0 3 31 * 3 * * 3 3 0 0 * * 3 0 3 11 * 3 * * 3 3 0 0 * * 3 0 3 01 * 3 * * 3 3 0 0 * * 3 0 1 11 * 3 * * 3 3 0 0 * * 3 0 0 11 * 3 * * 3 3 0 0 * * 1 0 3 11 * 3 * * 3 3 0 0 * * 0 0 3 11 * 3 * * 3 1 0 0 * * 3 0 3 11 * 3 * * 3 0 0 0 * * 3 0 3 11 * 3 * * 1 3 0 0 * * 3 0 3 11 * 3 * * 0 3 0 0 * * 3 0 3 11 * 1 * * 3 3 0 0 * * 3 0 3 11 * 0 * * 3 3 0 0 * * 3 0 3 12 * 3 * * 3 3 3 0 * * 3 0 3 02 * 3 * * 3 3 1 0 * * 3 0 3 02 * 3 * * 3 3 0 3 * * 3 0 3 02 * 3 * * 3 3 0 1 * * 3 0 3 02 * 3 * * 3 3 0 0 * * 3 3 3 02 * 3 * * 3 3 0 0 * * 3 1 3 02 * 3 * * 3 3 0 0 * * 3 0 3 32 * 3 * * 3 3 0 0 * * 3 0 3 12 * 3 * * 3 3 0 0 * * 3 0 3 02 * 3 * * 3 3 0 0 * * 3 0 1 02 * 3 * * 3 3 0 0 * * 3 0 0 02 * 3 * * 3 3 0 0 * * 1 0 3 02 * 3 * * 3 3 0 0 * * 0 0 3 02 * 3 * * 3 1 0 0 * * 3 0 3 02 * 3 * * 3 0 0 0 * * 3 0 3 02 * 3 * * 1 3 0 0 * * 3 0 3 02 * 3 * * 0 3 0 0 * * 3 0 3 02 * 1 * * 3 3 0 0 * * 3 0 3 02 * 0 * * 3 3 0 0 * * 3 0 3 0(38 行受影响)
[解决办法]
--借用五楼数据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))goinsert 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')gowith 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 11 * 1 * * 3 3 0 0 * * 3 0 3 11 * 3 * * 0 3 0 0 * * 3 0 3 11 * 3 * * 1 3 0 0 * * 3 0 3 11 * 3 * * 3 0 0 0 * * 3 0 3 11 * 3 * * 3 1 0 0 * * 3 0 3 11 * 3 * * 3 3 0 0 * * 0 0 3 11 * 3 * * 3 3 0 0 * * 1 0 3 11 * 3 * * 3 3 0 0 * * 3 0 0 11 * 3 * * 3 3 0 0 * * 3 0 1 11 * 3 * * 3 3 0 0 * * 3 0 3 01 * 3 * * 3 3 0 0 * * 3 0 3 11 * 3 * * 3 3 0 0 * * 3 0 3 31 * 3 * * 3 3 0 0 * * 3 1 3 11 * 3 * * 3 3 0 0 * * 3 3 3 11 * 3 * * 3 3 0 1 * * 3 0 3 11 * 3 * * 3 3 0 3 * * 3 0 3 11 * 3 * * 3 3 1 0 * * 3 0 3 11 * 3 * * 3 3 3 0 * * 3 0 3 12 * 0 * * 3 3 0 0 * * 3 0 3 02 * 1 * * 3 3 0 0 * * 3 0 3 02 * 3 * * 0 3 0 0 * * 3 0 3 02 * 3 * * 1 3 0 0 * * 3 0 3 02 * 3 * * 3 0 0 0 * * 3 0 3 02 * 3 * * 3 1 0 0 * * 3 0 3 02 * 3 * * 3 3 0 0 * * 0 0 3 02 * 3 * * 3 3 0 0 * * 1 0 3 02 * 3 * * 3 3 0 0 * * 3 0 0 02 * 3 * * 3 3 0 0 * * 3 0 1 02 * 3 * * 3 3 0 0 * * 3 0 3 02 * 3 * * 3 3 0 0 * * 3 0 3 12 * 3 * * 3 3 0 0 * * 3 0 3 32 * 3 * * 3 3 0 0 * * 3 1 3 02 * 3 * * 3 3 0 0 * * 3 3 3 02 * 3 * * 3 3 0 1 * * 3 0 3 02 * 3 * * 3 3 0 3 * * 3 0 3 02 * 3 * * 3 3 1 0 * * 3 0 3 02 * 3 * * 3 3 3 0 * * 3 0 3 0(38 行受影响)
[解决办法]
create table doubagui(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))insert into doubaguiselect 1, '*', '3', '*', '*', '3', '3', '0', '0', '*', '*', '3', '0', '3', '1' union allselect 2, '*', '3', '*', '*', '3', '3', '0', '0', '*', '*', '3', '0', '3', '0'with t0 as(select ID,'0' q from doubagui union allselect ID,'1' q from doubagui union allselect ID,'3' q from doubagui),t1 as(select ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14 from doubagui union select a.ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,b.q from doubagui a inner join t0 b on a.ID=b.ID and a.F14<>b.q and a.F14<>'*' union select a.ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,b.q,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F13<>b.q and a.F13<>'*' union select a.ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,b.q,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F12<>b.q and a.F12<>'*' union select a.ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,b.q,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F11<>b.q and a.F11<>'*' union select a.ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,b.q,F11,F12,F12,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F10<>b.q and a.F10<>'*' union select a.ID,F1,F2,F3,F4,F5,F6,F7,F8,b.q,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F9<>b.q and a.F9<>'*' union select a.ID,F1,F2,F3,F4,F5,F6,F7,b.q,F9,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F8<>b.q and a.F8<>'*' union select a.ID,F1,F2,F3,F4,F5,F6,b.q,F8,F9,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F7<>b.q and a.F7<>'*' union select a.ID,F1,F2,F3,F4,F5,b.q,F7,F8,F9,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F6<>b.q and a.F6<>'*' union select a.ID,F1,F2,F3,F4,b.q,F6,F7,F8,F9,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F5<>b.q and a.F5<>'*' union select a.ID,F1,F2,F3,b.q,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F4<>b.q and a.F4<>'*' union select a.ID,F1,F2,b.q,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F3<>b.q and a.F3<>'*' union select a.ID,F1,b.q,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F2<>b.q and a.F2<>'*' union select a.ID,b.q,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14 from doubagui a inner join t0 b on a.ID=b.ID and a.F1<>b.q and a.F1<>'*' )select * from t1ID F1 F2 F3 F4 F5 F6 F7 F8 F9 F10 F11 F12 F13 F14----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----1 * 0 * * 3 3 0 0 * * 3 0 3 11 * 1 * * 3 3 0 0 * * 3 0 3 11 * 3 * * 0 3 0 0 * * 3 0 3 11 * 3 * * 1 3 0 0 * * 3 0 3 11 * 3 * * 3 0 0 0 * * 3 0 3 11 * 3 * * 3 1 0 0 * * 3 0 3 11 * 3 * * 3 3 0 0 * * 0 0 3 11 * 3 * * 3 3 0 0 * * 1 0 3 11 * 3 * * 3 3 0 0 * * 3 0 0 11 * 3 * * 3 3 0 0 * * 3 0 1 11 * 3 * * 3 3 0 0 * * 3 0 3 01 * 3 * * 3 3 0 0 * * 3 0 3 11 * 3 * * 3 3 0 0 * * 3 0 3 31 * 3 * * 3 3 0 0 * * 3 1 3 11 * 3 * * 3 3 0 0 * * 3 3 3 11 * 3 * * 3 3 0 1 * * 3 0 3 11 * 3 * * 3 3 0 3 * * 3 0 3 11 * 3 * * 3 3 1 0 * * 3 0 3 11 * 3 * * 3 3 3 0 * * 3 0 3 12 * 0 * * 3 3 0 0 * * 3 0 3 02 * 1 * * 3 3 0 0 * * 3 0 3 02 * 3 * * 0 3 0 0 * * 3 0 3 02 * 3 * * 1 3 0 0 * * 3 0 3 02 * 3 * * 3 0 0 0 * * 3 0 3 02 * 3 * * 3 1 0 0 * * 3 0 3 02 * 3 * * 3 3 0 0 * * 0 0 3 02 * 3 * * 3 3 0 0 * * 1 0 3 02 * 3 * * 3 3 0 0 * * 3 0 0 02 * 3 * * 3 3 0 0 * * 3 0 1 02 * 3 * * 3 3 0 0 * * 3 0 3 02 * 3 * * 3 3 0 0 * * 3 0 3 12 * 3 * * 3 3 0 0 * * 3 0 3 32 * 3 * * 3 3 0 0 * * 3 1 3 02 * 3 * * 3 3 0 0 * * 3 3 3 02 * 3 * * 3 3 0 1 * * 3 0 3 02 * 3 * * 3 3 0 3 * * 3 0 3 02 * 3 * * 3 3 1 0 * * 3 0 3 02 * 3 * * 3 3 3 0 * * 3 0 3 0(38 row(s) affected)