巧妙将求差改为取模。
2007-01-17 22:26 zhaosichao 阅读(200) 评论(0) 编辑 收藏 举报left(NO,2) -left(NO ,2)=50
declare @t table(NO varchar(12),Num int)
insert into @t select '01050232',7
insert into @t select '03039483',8
insert into @t select '51050232',2
insert into @t select '05939382',4
insert into @t select '06938347',8
insert into @t select '55939382',12
select
NO,sum(Num) as Num
from
(select right('00'+rtrim(left(NO,2)%50),2)+stuff(NO,1,2,'') as No1,stuff(NO,1,2,'**') as NO,Num from @t) t
group by
NO,NO1
select * from @t order by right(no,6)
select right('00'+rtrim(left(NO,2)%50),2)+stuff(NO,1,2,'') as No1,stuff(NO,1,2,'**') as NO,Num ,* from @t
