Question

一个SQL超难的语句！！！

table1
====================================
c_b c_c1 c_c2
--------------------------------
A a<>k1 a<>k3
B b<>k1 c<>k1
A a<>k1 e<>k2
B d<>k3 d<>k2
=====================================
table_2

c_A c_B c_C1 c_C2
----------------------------------
k1 A a
k1 B b c
k2 A e
k2 B d
k3 A a
k3 B d
如何由table1生成table2???

Answer 1

我当你是MSSQL，如果是Oracle则写法略为不同
select isnull(a.c_a,b.c_a) as c_a,
isnull(a.c_b,b.c_b) as c_b,
c_c1,c_c2 from
(select distinct substring(c_c1,charindex('<>',c_c1)+2,len(c_c1)) as c_a,c_b,
substring(c_c1,1,charindex('<>',c_c1)-1) as c_c1 from table1) a
full join
(select distinct substring(c_c2,charindex('<>',c_c2)+2,len(c_c2)) as c_a,c_b,
substring(c_c2,1,charindex('<>',c_c2)-1) as c_c2 from table1 ) b
on a.c_a=b.c_a and a.c_b=b.c_b
要生成table2直接select ... into table2 ...就OK
具体数据测试和结果如下：
declare @table1 table(c_b varchar(50),c_c1 varchar(50),c_c2 varchar(50))
insert into @table1 select 'A', 'a<>k1', 'a<>k3'
union all select 'B', 'b<>k1', 'c<>k1'
union all select 'A', 'a<>k1', 'e<>k2'
union all select 'B', 'd<>k3', 'd<>k2'
select isnull(a.c_a,b.c_a) as c_a,
isnull(a.c_b,b.c_b) as c_b,
c_c1,c_c2 from
(select distinct substring(c_c1,charindex('<>',c_c1)+2,len(c_c1)) as c_a,c_b,
substring(c_c1,1,charindex('<>',c_c1)-1) as c_c1 from @table1) a
full join
(select distinct substring(c_c2,charindex('<>',c_c2)+2,len(c_c2)) as c_a,c_b,
substring(c_c2,1,charindex('<>',c_c2)-1) as c_c2 from @table1 ) b
on a.c_a=b.c_a and a.c_b=b.c_b
结果：
c_a c_b c_c1 c_c2
---------- ---------- ---------- ----------
k1 A a NULL
k1 B b c
k2 A NULL e
k2 B NULL d
k3 A NULL a
k3 B d NULL

Answer 2

哈..老师没教的这么详细..

Answer 3

- -#