有表字段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
...
如何实现
例如
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
...
如何实现
select F1,F2,F
from tab a,(
select 1 as F union all select 2 union all select 3
) as t
where a.F3 <> t.F
and 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,F3
from tab a,(
select 1 as F union all select 2 union all select 3
) as t
where a.F2 <> t.F
and 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,F3
from tab a,(
select 1 as F union all select 2 union all select 3
) as t
where a.F1 <> t.F
and not exists (
select 1 from tab
where F1 = t.F
and F2 = a.F2
and F3 = a.F3
)
insert into tb select 3,1,1
go
insert into tb
select f1+1,f2,f3 from tb
union all
select f1+2,f2,f3 from tb
union all
select f1,f2+1,f3 from tb
union all
select f1,f2+2,f3 from tb
union all
select f1,f2,f3+1 from tb
union all
select f1,f2,f3+2 from tb
go
select * from tb
/*
ID F1 F2 F3
----------- ----------- ----------- -----------
1 3 1 1
2 4 1 1
3 5 1 1
4 3 2 1
5 3 3 1
6 3 1 2
7 3 1 3(7 行受影响)*/
go
drop table tb
insert into tb select 3,1,1
go
insert into tb
select f1-1,f2,f3 from tb
union all
select f1-2,f2,f3 from tb
union all
select f1,f2+1,f3 from tb
union all
select f1,f2+2,f3 from tb
union all
select f1,f2,f3+1 from tb
union all
select f1,f2,f3+2 from tb
go
select * from tb
/*
ID F1 F2 F3
----------- ----------- ----------- -----------
1 3 1 1
2 2 1 1
3 1 1 1
4 3 2 1
5 3 3 1
6 3 1 2
7 3 1 3(7 行受影响)*/
go
drop table tb
go
--> -->
declare @T table([ID] INT IDENTITY,[F1] int,[F2] int,[F3] int)
Insert @T
select 3,1,1INSERT @T
Select [F1],[F2],[F3]=b.ID
from @T AS a,(SELECT 1 AS ID UNION ALL SELECT 2 UNION ALL SELECT 3)b
WHERE a.f3<>b.ID
UNION ALL
Select [F1],[F2]=b.ID,[F3]
from @T AS a,(SELECT 1 AS ID UNION ALL SELECT 2 UNION ALL SELECT 3)b
WHERE 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)b
WHERE a.f1<>b.ID
SELECT * FROM @T
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 1
2 1 1
3 1 1
3 1 2
3 1 3
3 2 1
3 3 1
*/
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) t1
cross join
(select 1 s2 union select 2 union select 3) t2
cross join
(select 1 s3 union select 2 union select 3) t3
)
insert into tb
select t.*
from t
left join tb t12 on t.s1=t12.F1 and t.s2=t12.F2
left join tb t13 on t.s1=t13.F1 and t.s3=t13.F3
left join tb t23 on t.s2=t23.F2 and t.s3=t23.F3
where ((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 1
2 1 1 1
3 2 1 1
4 3 1 2
5 3 1 3
6 3 2 1
7 3 3 1