有如下表:
Type FNum
A1 12
A2 34
A4 33
M1 21
M21 13要显示如下:
T1 FNum1 T2 FNum2
A1 12 M1 21
A2 34 M21 13
A4 33 NULL NULLT1列显示的是第一个字母不为M的,第二列显示第一字母为M的(要考虑到M的数目比非M的数目多的问题)?
Type FNum
A1 12
A2 34
A4 33
M1 21
M21 13要显示如下:
T1 FNum1 T2 FNum2
A1 12 M1 21
A2 34 M21 13
A4 33 NULL NULLT1列显示的是第一个字母不为M的,第二列显示第一字母为M的(要考虑到M的数目比非M的数目多的问题)?
create table T(Type varchar(10), FNum int)
insert T select 'A1', 12
union all select 'A2', 34
union all select 'A4', 33
union all select 'M1', 21
union all select 'M21', 13select ID=identity(int, 1, 1), * into #T1 from T
where left(Type, 1)<>'M'select ID=identity(int, 1, 1), * into #T2 from T
where left(Type, 1)='M'select T1=#T1.Type, FNum1=#T1.FNum,
T2=#T2.Type, FNum2=#T2.FNum
from #T1
full join #T2 on #T2.ID=#T1.ID--result
T1 FNum1 T2 FNum2
---------- ----------- ---------- -----------
A1 12 M1 21
A2 34 M21 13
A4 33 NULL NULL(3 row(s) affected)
select B.B1 as T1,B.B2 as FNum1 ,A.A1 as T2,A.A2 as FNum2 from
(select Type as A1,FNum as A2 from T where Type like 'M%') as A
cross join
(select Type as B1,FNum as B2 from T where Type like 'A%') as B 唉,不使用临时表达不到效果,看看楼下能不能吧