如何插入递增的数据？

Question

--我有一张表test，表结构如下： 
--Bdepart--varchar(50)，存储大部门信息 
--Sdepart--varchar(50)，存储小部门信息 
--leibie---varchar(12)，存储员工类别信息，如 在岗职工，劳务工，聘用工等 
--name-----varchar(12),存储姓名信息 
--zhid-----char(5)，员工唯一ID 
--BdepartPId--char(2)，存储大部门排序信息 
--SdepartPId--char(4)，存储小部门排序信息 --现在假设test表name不存在重复，需要对test表的zhid分配唯一号，格式要求如下： select * from test where leibie='劳务工' order by bdepartpid,sdepartpid; 
--需将zhid字段从L0001置起，假若数据有99条，则最后的记录中zhid字段值为L0099 select * from test where leibie='在岗职工' order by bdepartpid,sdepartpid; 
--需将zhid字段从Z0001置起，假若数据有99条，则最后的记录中zhid字段值为Z0099 select * from test where leibie='聘用工' order by bdepartpid,sdepartpid; 
--需将zhid字段从P0001置起，假若数据有99条，则最后的记录中zhid字段值为P0099 
请问以上要求如何用一条SQL语句实现？

Answer 1

忘了一个重要的事情，在日后的新增员工时（可能是在岗职工、聘用工或劳务工），如何快速查询出最大值的ZHID？
因为上面说的只是name不重复的情况，但事实以上数据是类似工资数据库一样，每个月都会先以上个月的数据copy到本月存储，再经过增删改才正式是本月的数据。当然删除后该zhid不能被后新增的员工所使用。

Answer 2

    SELECT xx,xx,xx
         zhid=CASE leibie 
                 WHEN '劳务工' THEN 'L'
                 WHEN '在岗职工' THEN 'Z'
                 WHEN '聘用工' THEN 'P'
              END
              +RIGHT('000'+RTRIM(ROW_NUMBER() OVER(PARTITION BY leibie ORDER BY bdepartpid,sdepartpid)),4)
    FROM test

Answer 3

updte test set zhid = 'L' + right('0000'+cast((select count(*) from test where leibie='劳务工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='劳务工'
updte test set zhid = 'Z' + right('0000'+cast((select count(*) from test where leibie='在岗职工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='在岗职工'
updte test set zhid = 'P' + right('0000'+cast((select count(*) from test where leibie='聘用工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='聘用工'

Answer 4

??
insert test (zhid) select right(10000+right(max(zhid),4)+1,4)  where leibie=@leibie

Answer 5

update test set zhid = 'L' + right('0000'+cast((select count(*) from test where leibie='劳务工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='劳务工'
update test set zhid = 'Z' + right('0000'+cast((select count(*) from test where leibie='在岗职工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='在岗职工'
update test set zhid = 'P' + right('0000'+cast((select count(*) from test where leibie='聘用工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='聘用工'

Answer 6

declare @table table (id int,mchar varchar(1))
insert into @table
select 1,'a' union all
select 2,'b' union all
select 4,'v' union all
select 5,'c' union all
select 7,'d' union all
select 9,'c' union all
select 8,'d' union all
select 10,'i' union all
select 21,'m' union all
select 24,'p'union all
select 90,'k'
select substring('L0000',1,5-len(cast (row as nvarchar(10))))+cast (row as nvarchar(10)),mchar from 
(select row_number() over(order by id ) as row,mchar from @table)aa/*
                mchar
--------------- -----
L0001           a
L0002           b
L0003           v
L0004           c
L0005           d
L0006           d
L0007           c
L0008           i
L0009           m
L0010           p
L0011           k
*/

Answer 7

??
insert test (zhid) select left(zhid,1)+right(10000+right(max(zhid),4)+1,4)  where leibie=@leibie

Answer 8

alter table test add tid int identity(1,1)update a set zhid=case leibie 
when '劳务工' then 'L'+right('0000'+rtrim((select count(1) from test where leibie='劳务工' and tid<a.tid)+1),4) 
when '在岗职工' then 'Z'+right('0000'+rtrim((select count(1) from test where leibie='在岗职工' and tid<a.tid)+1),4) 
when '聘用工' then 'P'+right('0000'+rtrim((select count(1) from test where leibie='聘用工' and tid<a.tid)+1),4) end
from test aalter table test drop column tid

Answer 9

select leibie, max(zhid)  from test where leibie in ('劳务工','在岗职工','聘用工')

Answer 10

--modify
select leibie, max(zhid)  from test where leibie in ('劳务工','在岗职工','聘用工') group by leibie

Answer 11

update a set zhid=case leibie 
when '劳务工' then 'L'+right('0000'+rtrim((select count(1) from test where leibie='劳务工' and BdepartPId=a.BdepartPId and  SdepartPId<a.SdepartPId)+1),4) 
when '在岗职工' then 'Z'+right('0000'+rtrim((select count(1) from test where leibie='在岗职工' and BdepartPId=a.BdepartPId and  SdepartPId<a.SdepartPId)+1),4) 
when '聘用工' then 'P'+right('0000'+rtrim((select count(1) from test where leibie='聘用工' and BdepartPId=a.BdepartPId and  SdepartPId<a.SdepartPId)+1),4) end
from test a

Answer 12

tryupdate test set zhid = 'L' + right('0000'+cast((select count(*) from test where leibie='劳务工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='劳务工'
update test set zhid = 'Z' + right('0000'+cast((select count(*) from test where leibie='在岗职工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='在岗职工'
update test set zhid = 'P' + right('0000'+cast((select count(*) from test where leibie='聘用工' and bdepartpid < t.bdepartpid or (bdepartpid = t.bdepartpid and sdepartpid<t.sdepartpid ) + 1 as varchar),4) from tb t where leibie='聘用工'