号码扩展
有表字段F1，F2,F3字段的值由1,2,3组成
例如
ID F1 F2 F3
1 3 1 1
...

现将记录进行扩展：某两个字段的值不变，另外一个字段的值由原来的值变成另外的两个值（例如原来为1，变化后为2和3），那么一条记录变化后，包括原来的记录在内，就得到7条记录。
结果为
ID F1 F2 F3
1 3 1 1
2 3 1 3
3 3 1 2
4 3 3 1
5 3 2 1
6 2 1 1
7 1 1 1
...
如何实现


[解决办法]

SQL code

insert tab(F1,F2,F3)select F1,F2,Ffrom tab a,(select 1 as F union all select 2 union all select 3) as twhere a.F3 <> t.Fand not exists (  select 1 from tab  where F1 = a.F1  and F2 = a.F2  and F3 = t.F  )insert tab(F1,F2,F3)select F1,F,F3from tab a,(select 1 as F union all select 2 union all select 3) as twhere a.F2 <> t.Fand not exists (  select 1 from tab  where F1 = a.F1  and F2 = t.F  and F3 = a.F3  )insert tab(F1,F2,F3)select F,F2,F3from tab a,(select 1 as F union all select 2 union all select 3) as twhere a.F1 <> t.Fand not exists (  select 1 from tab  where F1 = t.F  and F2 = a.F2  and F3 = a.F3  )
[解决办法]
SQL code
create table tb(ID int identity(1,1),F1 int,F2 int,F3 int)insert into tb select 3,1,1goinsert into tbselect f1+1,f2,f3 from tbunion allselect f1+2,f2,f3 from tbunion allselect f1,f2+1,f3 from tbunion allselect f1,f2+2,f3 from tbunion allselect f1,f2,f3+1 from tbunion allselect f1,f2,f3+2 from tbgoselect * from tb/*ID          F1          F2          F3----------- ----------- ----------- -----------1           3           1           12           4           1           13           5           1           14           3           2           15           3           3           16           3           1           27           3           1           3(7 行受影响)*/godrop table tb
[解决办法]
SQL code
create table tb(ID int identity(1,1),F1 int,F2 int,F3 int)insert into tb select 3,1,1goinsert into tbselect f1-1,f2,f3 from tbunion allselect f1-2,f2,f3 from tbunion allselect f1,f2+1,f3 from tbunion allselect f1,f2+2,f3 from tbunion allselect f1,f2,f3+1 from tbunion allselect f1,f2,f3+2 from tbgoselect * from tb/*ID          F1          F2          F3----------- ----------- ----------- -----------1           3           1           12           2           1           13           1           1           14           3           2           15           3           3           16           3           1           27           3           1           3(7 行受影响)*/godrop table tb
[解决办法]
SQL code
use Tempdbgo--> -->  declare @T table([ID] INT IDENTITY,[F1] int,[F2] int,[F3] int)Insert @Tselect 3,1,1INSERT @TSelect [F1],[F2],[F3]=b.IDfrom @T AS a,(SELECT 1 AS ID UNION ALL SELECT 2 UNION ALL SELECT 3)bWHERE a.f3<>b.IDUNION ALLSelect [F1],[F2]=b.ID,[F3] from @T AS a,(SELECT 1 AS ID UNION ALL SELECT 2 UNION ALL SELECT 3)bWHERE a.f2<>b.ID UNION ALL Select [F1]=b.ID,[F2],[F3]from @T AS a,(SELECT 1 AS ID UNION ALL SELECT 2 UNION ALL SELECT 3)bWHERE a.f1<>b.IDSELECT * FROM @T
[解决办法]
SQL code
declare @t table(id int,f1 int ,f2 int,f3 int);declare @tx table(id int);insert into @t select 1,3,1,1; --这里可以修改insert into @tx select 1 union all select 2 union all select 3;select x.* from (select a.id as f1,b.id as f2,c.id as f3 from @tx a cross join @tx b cross join @tx c ) x join @t y on x.f1=y.f1 and x.f2=y.f2 or x.f1=y.f1 and x.f3=y.f3 or x.f2=y.f2 and x.f3=y.f3;/*f1          f2          f3----------- ----------- -----------1           1           12           1           13           1           13           1           23           1           33           2           13           3           1*/ 
 
[解决办法]
SQL code
create table tb(ID int identity(1,1),F1 int,F2 int,F3 int)insert into tb select 3,1,1with t as (select t1.s1,t2.s2,t3.s3 from (select 1 s1 union select 2 union select 3) t1cross join(select 1 s2 union select 2 union select 3) t2cross join(select 1 s3 union select 2 union select 3) t3)insert into tbselect t.*from tleft join tb t12 on t.s1=t12.F1 and t.s2=t12.F2left join tb t13 on t.s1=t13.F1 and t.s3=t13.F3left join tb t23 on t.s2=t23.F2 and t.s3=t23.F3where ((t12.F1 is not null and t12.F2 is not null and t12.F3 is not null)or (t13.F1 is not null and t13.F2 is not null and t13.F3 is not null)or (t23.F1 is not null and t23.F2 is not null and t23.F3 is not null))and not exists(select 1 from tb where F1=t.s1 and F2=t.s2 and F3=t.s3)select * from tbID          F1          F2          F3----------- ----------- ----------- -----------1           3           1           12           1           1           13           2           1           14           3           1           25           3           1           36           3           2           17           3           3           1