--drop table tbcreate table tb(id varchar(20),name varchar(20)) Insert into tb select '001','afafa' union all select '001001','afava' union all select '001001002','afafa' union all select '001001005','adfafa' union all select '001003','afda' union all select '001003001','afafa' union all select '003','adfa' union all select '003002','adfa' union all select '003005','afafa'--查詢 select id,name ,r1=case when len(id)>0 then case when left(id,3)='001' then '1' when left(id,3)>'001' then '2' else '0' end else '0' end ,r2=case when len(id)>3 then case when substring(id,4,3)='001' then '1' when substring(id,4,3)>'001' then '2' else '0' end else '0' end ,r3=case when len(id)>6 then case when substring(id,7,3)='001' then '1' when substring(id,7,3)>'001' then '2' else '0' end else '0' end from tb --結果 id name r1 r2 r3 ---------------------------------------------- 001 afafa 1 0 0 001001 afava 1 1 0 001001002 afafa 1 1 2 001001005 adfafa 1 1 2 001003 afda 1 2 0 001003001 afafa 1 2 1 003 adfa 2 0 0 003002 adfa 2 2 0 003005 afafa 2 2 0
--示例数据create table tb(id varchar(10),name varchar(10)) insert tb select '001' ,'afafa' union all select '001001' ,'afava' union all select '001001002','afafa' union all select '001001005','adfafa' union all select '001003' ,'afda' union all select '001003001','afafa' union all select '003' ,'adfa' union all select '003002' ,'adfa' union all select '003005' ,'afafa' go--处理 select *,r1=0,r2=0,r3=0 into #t from tbdeclare @id varchar(10),@r1 int,@r2 int,@r3 int select @r1=0,@r3=0 update #t set @r1=case when substring(@id,1,3)=substring(id,1,3) then @r1 else @r1+1 end, @r2=case when substring(@id,1,3)=substring(id,1,3) then case when substring(@id,1,6)=substring(id,1,6) then @r2 else @r2+1 end else 0 end, @r3=case when substring(@id,1,6)=substring(id,1,6) then case when substring(@id,1,9)=substring(id,1,9) then @r3 else @r3+1 end else 0 end, r1=@r1,r2=@r2,r3=@r3,@id=id --显示处理结果 select * from #t drop table #t go--删除测试 drop table tb/*--测试结果id name r1 r2 r3 ---------- ---------- ----------- ----------- ----------- 001 afafa 1 0 0 001001 afava 1 1 0 001001002 afafa 1 1 1 001001005 adfafa 1 1 2 001003 afda 1 2 0 001003001 afafa 1 2 1 003 adfa 2 0 0 003002 adfa 2 1 0 003005 afafa 2 2 0(所影响的行数为 9 行) --*/
Insert into tb
select '001','afafa'
union all select '001001','afava'
union all select '001001002','afafa'
union all select '001001005','adfafa'
union all select '001003','afda'
union all select '001003001','afafa'
union all select '003','adfa'
union all select '003002','adfa'
union all select '003005','afafa'--查詢
select id,name
,r1=case when len(id)>0 then case when left(id,3)='001' then '1' when left(id,3)>'001' then '2' else '0' end else '0' end
,r2=case when len(id)>3 then case when substring(id,4,3)='001' then '1' when substring(id,4,3)>'001' then '2' else '0' end else '0' end
,r3=case when len(id)>6 then case when substring(id,7,3)='001' then '1' when substring(id,7,3)>'001' then '2' else '0' end else '0' end
from tb --結果
id name r1 r2 r3
----------------------------------------------
001 afafa 1 0 0
001001 afava 1 1 0
001001002 afafa 1 1 2
001001005 adfafa 1 1 2
001003 afda 1 2 0
001003001 afafa 1 2 1
003 adfa 2 0 0
003002 adfa 2 2 0
003005 afafa 2 2 0
是这个样子的
根据id的前三位的排序得到r1列
前三位相同的r1列值相同 再根据中间三位排序得到r2的值
前六位相同的r1 r2值相同 在根据最后三位排序得到r3的值
如此下去 ..该怎么实现呢?
是这个样子的
根据id的前三位的排序得到r1列
前三位相同的r1列值相同 再根据中间三位排序得到r2的值
前六位相同的r1 r2值相同 在根据最后三位排序得到r3的值
如此下去 ..该怎么实现呢
004002005 3 1 2
为什么 r3 是 2 还是不清楚
insert tb select '001' ,'afafa'
union all select '001001' ,'afava'
union all select '001001002','afafa'
union all select '001001005','adfafa'
union all select '001003' ,'afda'
union all select '001003001','afafa'
union all select '003' ,'adfa'
union all select '003002' ,'adfa'
union all select '003005' ,'afafa'
go--处理
select *,r1=0,r2=0,r3=0
into #t from tbdeclare @id varchar(10),@r1 int,@r2 int,@r3 int
select @r1=0,@r3=0
update #t set
@r1=case
when substring(@id,1,3)=substring(id,1,3)
then @r1 else @r1+1 end,
@r2=case
when substring(@id,1,3)=substring(id,1,3)
then case
when substring(@id,1,6)=substring(id,1,6)
then @r2 else @r2+1 end
else 0 end,
@r3=case
when substring(@id,1,6)=substring(id,1,6)
then case
when substring(@id,1,9)=substring(id,1,9)
then @r3 else @r3+1 end
else 0 end,
r1=@r1,r2=@r2,r3=@r3,@id=id
--显示处理结果
select * from #t
drop table #t
go--删除测试
drop table tb/*--测试结果id name r1 r2 r3
---------- ---------- ----------- ----------- -----------
001 afafa 1 0 0
001001 afava 1 1 0
001001002 afafa 1 1 1
001001005 adfafa 1 1 2
001003 afda 1 2 0
001003001 afafa 1 2 1
003 adfa 2 0 0
003002 adfa 2 1 0
003005 afafa 2 2 0(所影响的行数为 9 行)
--*/