菜鸟跪求一简单SQL语句?
有如下表:
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 NULL
T1列显示的是第一个字母不为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 ', 13
select 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)
