22,209
社区成员
发帖
与我相关
我的任务
分享
group by a.FModel,LEFT(MakeNO,4),SUBSTRING(MakeNO,5,2),a.rn/10
select case when rn/10=0 then FModel else '' end FModel,
case when rn/10=0 then LEFT(MakeNO,4) else '' end as Years,
case when rn/10=0 then SUBSTRING(MakeNO,5,2) else '' end as Months,
right(max(case when rn%10=0 then MakeNO else '' end),5) as MakeNO01,
right(max(case when rn%10=1 then MakeNO else '' end),5) as MakeNO02,
right(max(case when rn%10=2 then MakeNO else '' end),5) as MakeNO03,
right(max(case when rn%10=3 then MakeNO else '' end),5) as MakeNO04,
right(max(case when rn%10=4 then MakeNO else '' end),5) as MakeNO05,
right(max(case when rn%10=5 then MakeNO else '' end),5) as MakeNO06,
right(max(case when rn%10=6 then MakeNO else '' end),5) as MakeNO07,
right(max(case when rn%10=7 then MakeNO else '' end),5) as MakeNO08,
right(max(case when rn%10=8 then MakeNO else '' end),5) as MakeNO09,
right(max(case when rn%10=9 then MakeNO else '' end),5) as MakeNO10
from(
select MakeNO,FModel,
rn=(select count(1) from tb where FModel=t.FModel and MakeNO<t.MakeNO)
from tb t) a
group by a.FModel,LEFT(MakeNO,4),SUBSTRING(MakeNO,5,2),a.rn/10
order by a.FModel,a.rn/10
---------------------------------------------------------------------------------
FModel Years Months MakeNO01 MakeNO02 MakeNO03 MakeNO04 MakeNO05 MakeNO06 MakeNO07 MakeNO08 MakeNO09 MakeNO10
AFF46/260 8*1.5 2010 05 00100 00101 00102 00103 00104 00105 00106 00107 00108 00109
00110 00111 00112
Cu50WZC--020--50+150 2010 06 00001 00002 00003 00004 00005 00006 00007 00008 00009 00010
00011 00012 00013 00014 00015 00016
L=150 φ27 2010 06 00050 00051 00052
select case when rn/10=0 then FModel else '' end FModel,
right(max(case when rn%10=0 then MakeNO else '' end),5) as MakeNO01,
right(max(case when rn%10=1 then MakeNO else '' end),5) as MakeNO02,
right(max(case when rn%10=2 then MakeNO else '' end),5) as MakeNO03,
right(max(case when rn%10=3 then MakeNO else '' end),5) as MakeNO04,
right(max(case when rn%10=4 then MakeNO else '' end),5) as MakeNO05,
right(max(case when rn%10=5 then MakeNO else '' end),5) as MakeNO06,
right(max(case when rn%10=6 then MakeNO else '' end),5) as MakeNO07,
right(max(case when rn%10=7 then MakeNO else '' end),5) as MakeNO08,
right(max(case when rn%10=8 then MakeNO else '' end),5) as MakeNO09,
right(max(case when rn%10=9 then MakeNO else '' end),5) as MakeNO10
from(
select MakeNO,FModel,
rn=(select count(1) from tb where FModel=t.FModel and MakeNO<t.MakeNO)
from tb t) a
group by a.FModel,a.rn/10
order by a.FModel,a.rn/10
/*
FModel MakeNO01MakeNO02MakeNO03MakeNO04MakeNO05MakeNO06MakeNO07MakeNO08MakeNO09MakeNO10
AFF46/260 8*1.5 00100 00101 00102 00103 00104 00105 00106 00107 00108 00109
00110 00111 00112
Cu50WZC--020--50+150 00001 00002 00003 00004 00005 00006 00007 00008 00009 00010
00011 00012 00013 00014 00015 00016
L=150 φ27 00050 00051 00052
*/