老MIS系统的部门编码如下:部门编码 部门名称 上级部门
GZZB 公司总部 TOP
XSB 销售部 GZZB
RSB 人事部 GZZB
XSB01 销售一部 XSB
XSB02 销售二部 XSB以此类推现在新的MIS系统要求要如下编码:
部门编码 部门名称 上级部门
01 公司总部 TOP
0101 销售部 01
0102 人事部 01
010101 销售一部 0101
010102 销售二部 0101该如何转换了,老编码和新编码都没有层级LEVEL的标记
GZZB 公司总部 TOP
XSB 销售部 GZZB
RSB 人事部 GZZB
XSB01 销售一部 XSB
XSB02 销售二部 XSB以此类推现在新的MIS系统要求要如下编码:
部门编码 部门名称 上级部门
01 公司总部 TOP
0101 销售部 01
0102 人事部 01
010101 销售一部 0101
010102 销售二部 0101该如何转换了,老编码和新编码都没有层级LEVEL的标记
解决方案 »
- 求一相互修改的 sql 语句 ,请高手指点 在线等,急急急急急急急急急急急急急急急急急
- 用什么语句可以直接得到指定的在数据库中已经有的表名的创建语句?
- SQL2008表中数据的单元格是只读属性,不能更改数据,请教怎么设置属性为非只读
- update可否解决?? 问题!!
- 执行"select name from Table"把第行name字段的值用“,”隔开变成一个字符串,不用存储过程能不能做到?
- 限定小数位数
- 请教关于优化排序的方法
- 表中记录很多时建立索引是不是很慢啊
- 只有sql6.5数据库备份文件怎样转成sql7.0
- 不同服务器动态插入的问题!
- 数据库中是乱码,可显示到页面上却是正常的?!!
- 如何手动安装IIS。
SQL05以上用row_number排序函數處理
SQL2000需要用臨時表或循環
go
--> -->
declare @T table([部门编码] nvarchar(20),[部门名称] nvarchar(20),[上级部门] nvarchar(20))
Insert @T
select N'GZZB',N'公司总部',N'TOP' union all
select N'XSB',N'销售部',N'GZZB' union all
select N'RSB',N'人事部',N'GZZB' union all
select N'XSB01',N'销售一部',N'XSB' union all
select N'XSB02',N'销售二部',N'XSB'
;with Cte
as
(select *,cast(right(100+row_number()over(order by (select 1)),2) as nvarchar(20)) as Code from @T t where not exists(select 1 from @T where [部门编码]=t.[上级部门])
union all
select t.*,cast(t2.Code+right(100+row_number()over(partition by t2.[部门编码] order by (select 1)),2) as nvarchar(20)) as Code
from @T t,Cte t2
where t.[上级部门]=t2.[部门编码]
)
update b
set [部门编码]=a.Code,[上级部门]=isnull(c.Code,b.[上级部门])
from Cte a
inner join @T b on a.[部门编码]=b.[部门编码]
left join Cte c on c.[部门编码]=b.[上级部门]
select * from @T部门编码 部门名称 上级部门
-------------------- -------------------- --------------------
01 公司总部 TOP
0101 销售部 01
0102 人事部 01
010101 销售一部 0101
010102 销售二部 0101(5 個資料列受到影響)
if object_id('tb')is not null drop table tb
go
create table tb(部门编码 varchar(5),部门名称 varchar(8),上级部门 varchar(4))
insert into tb
select 'GZZB','公司总部','TOP' union all
select 'XSB','销售部','GZZB' union all
select 'RSB','人事部','GZZB' union all
select 'XSB01','销售一部','XSB' union all
select 'XSB02','销售二部','XSB'
if object_id('tc')is not null drop table tc
go
create table tc(px int identity(1,1),id varchar(20),部门编码 varchar(20),部门名称 varchar(20),上级部门 varchar(20),leve int)
go declare @level int
set @level=1insert tc select 部门编码,'01',部门名称,上级部门,@level from tb where 上级部门='TOP'
while exists(select 1 from tb,tc where tb.上级部门=tc.id and leve=@level)
begin
set @level=@level+1
insert tc select tb.部门编码,'',tb.部门名称,tc.部门编码,@level from tb,tc where tb.上级部门=tc.id and leve=@level-1
update tc
set 部门编码=上级部门+right('00'+ltrim((select count(1) from tc where 上级部门=t.上级部门 and px<=t.px)),2)
from tc t
where 部门编码=''
end
go select 部门编码,部门名称,上级部门 from tc部门编码 部门名称 上级部门
-------------------- -------------------- --------------------
01 公司总部 TOP
0101 销售部 01
0102 人事部 01
010101 销售一部 0101
010102 销售二部 0101(5 行受影响)
--> 测试数据:@table
declare @table table([部门编码] varchar(10),[部门名称] varchar(20),[上级部门] varchar(10))
insert @table
select 'GZZB','公司总部','TOP' union all
select 'XSB','销售部','GZZB' union all
select 'RSB','人事部','GZZB' union all
select 'XSB01','销售一部','XSB' union all
select 'XSB02','销售二部','XSB'
--生成部门数
select distinct identity(int,1,1) as id, 部门编码
into #temp from @table where 部门名称 <> '公司总部' and charindex('0',部门编码) = 0
--修改
update @table
set 部门编码 = case when charindex('B',t.部门编码)>0 and t.部门编码 <> 'GZZB'then
case len(r.id) when 1 then '010'+convert(varchar(10),r.id)+replace(t.部门编码,r.部门编码,'')
else '01'+convert(varchar(10),r.id)+replace(t.部门编码,r.部门编码,'') end
when charindex('ZB',t.部门编码)>0 then '01'
else t.部门编码 end ,
上级部门 = case when charindex('B',t.上级部门)>0 and t.上级部门 <> 'GZZB' then
case len(r.id) when 1 then '010'+convert(varchar(10),r.id)+replace(t.部门编码,r.部门编码,'')
else '01'+convert(varchar(10),r.id)+replace(t.部门编码,r.部门编码,'') end
when charindex('ZB',t.上级部门)>0 then '01'
else t.上级部门 end
from @table t left join #temp r
on r.部门编码 = case when charindex('0',t.部门编码) >0 then substring(t.部门编码,1,charindex('0',t.部门编码)-1)
else t.部门编码 endselect * from @tabledrop table #temp
--结果
----------------------------------------
01 公司总部 TOP
0101 销售部 01
0102 人事部 01
010101 销售一部 010101
010102 销售二部 010102
--> 测试数据:@table
declare @table table([部门编码] varchar(10),[部门名称] varchar(20),[上级部门] varchar(10))
insert @table
select 'GZZB','公司总部','TOP' union all
select 'XSB','销售部','GZZB' union all
select 'RSB','人事部','GZZB' union all
select 'XSB01','销售一部','XSB' union all
select 'XSB02','销售二部','XSB'select distinct identity(int,1,1) as id, 部门编码
into #temp from @table where 部门名称 <> '公司总部' and charindex('0',部门编码) = 0update @table
set 部门编码 = case when charindex('B',t.部门编码)>0 and t.部门编码 <> 'GZZB'then
case len(r.id) when 1 then '010'+convert(varchar(10),r.id)+replace(t.部门编码,r.部门编码,'')
else '01'+convert(varchar(10),r.id)+replace(t.部门编码,r.部门编码,'') end
when charindex('ZB',t.部门编码)>0 then '01'
else t.部门编码 end ,
上级部门 = case when charindex('B',t.上级部门)>0 and t.上级部门 <> 'GZZB' then
case len(r.id) when 1 then '010'+convert(varchar(10),r.id)+replace(t.上级部门,r.部门编码,'')
else '01'+convert(varchar(10),r.id)+replace(t.上级部门,r.部门编码,'') end
when charindex('ZB',t.上级部门)>0 then '01'
else t.上级部门 end
from @table t left join #temp r
on r.部门编码 = case when charindex('0',t.部门编码) >0 then substring(t.部门编码,1,charindex('0',t.部门编码)-1)
else t.部门编码 endselect * from @tabledrop table #temp
--结果
-----------------------------------
01 公司总部 TOP
0101 销售部 01
0102 人事部 01
010101 销售一部 0101
010102 销售二部 0101