有表字段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*/