to:leimin(黄山光明顶)
我问的是如何转化表达式,并将结果变成如下的表内容(如果有更好的表结构也可以)
结果:
A*B + A*C + D*F + D*G*H + D*G*I + E*F + E*G*H + E*G*I
表内容:
Type Letter
---------------
1 A
1 B
2 A
2 C
3 D
3 F
4 D
4 G
4 H
.....
我问的是如何转化表达式,并将结果变成如下的表内容(如果有更好的表结构也可以)
结果:
A*B + A*C + D*F + D*G*H + D*G*I + E*F + E*G*H + E*G*I
表内容:
Type Letter
---------------
1 A
1 B
2 A
2 C
3 D
3 F
4 D
4 G
4 H
.....
set nocount on
create table test(pid varchar(10),cid varchar(10))
insert test select '0','A'
union all select 'A','B'
union all select 'A','C'
union all select '0','D'
union all select '0','E'
union all select 'D','F'
union all select 'D','G'
union all select 'G','H'
union all select 'G','I'
union all select 'E','F'
union all select 'E','G'select cid into #t1 from test where pid='0' and cid in (select pid from test)
select identity(int,1,1) as id,cid into #t2 from test where pid='0' and cid not in (select pid from test)
declare @i int,@j int
set @i=1
set @j=1
while @i<>0 and @j<>0
begin
insert #t1 select a.cid+'*'+b.cid from #t1 a,test b where b.pid=right(a.cid,1) and a.cid+'*'+b.cid not in (select cid from #t1) and b.cid in (select pid from test)
set @i=@@rowcount
insert #t2 select a.cid+'*'+b.cid from #t1 a,test b where b.pid=right(a.cid,1) and a.cid+'*'+b.cid not in (select cid from #t2) and b.cid not in (select pid from test)
set @j=@@rowcount
enddeclare @string varchar(1000)
set @string=''
select @string=@string+cid+'+' from #t2
set @string=left(@string,len(@string)-1)
select @stringdrop table #t1
select top 100 identity(int,1,1) as N into #t3 from sysobjects
select id,substring('*'+cid+'*',N+1,charindex('*','*'+cid+'*',N+1)-N-1) as cid from #t2,#t3 where substring('*'+cid+'*',N,200) like '*_%' drop table #t2
drop table #t3A*B+A*C+D*F+D*G*H+E*G*H+D*G*I+E*G*I+E*Fid cid
----------- ------------
1 A
1 B
2 A
2 C
3 D
3 F
4 D
4 G
4 H
5 E
5 G
5 H
6 D
6 G
6 I
7 E
7 G
7 I
8 E
8 F
select * from testpid cid
---------- ----------
0 A
A B
A C
0 D
0 E
D F
D G
G H
G I
E F
E G
我的qq:10340482
mns:[email protected]