表:人员
我存盘时update 编号( like 'A001')
UPDATE 人员
SET 编号='A'+CAST(ISNULL((SELECT CAST(MAX(RIGHT(编号,3)) AS NUMERIC)
FROM 人员
WHERE 编号 LIKE 'A'+'%'),CAST(RIGHT(编号,3)+'000' AS NUMERIC))+1 AS NVARCHAR(50))
WHERE ISNULL(编号,'')='' 得到的结果是A1 为何不是A001呢?
我存盘时update 编号( like 'A001')
UPDATE 人员
SET 编号='A'+CAST(ISNULL((SELECT CAST(MAX(RIGHT(编号,3)) AS NUMERIC)
FROM 人员
WHERE 编号 LIKE 'A'+'%'),CAST(RIGHT(编号,3)+'000' AS NUMERIC))+1 AS NVARCHAR(50))
WHERE ISNULL(编号,'')='' 得到的结果是A1 为何不是A001呢?
SET 编号='A'+RIGHT('000' + CAST(ISNULL((SELECT CAST(MAX(RIGHT(编号,3)) AS NUMERIC) FROM 人员 WHERE 编号 LIKE 'A'+'%'),CAST(RIGHT(编号,3)+'000' AS NUMERIC))+1 AS NVARCHAR(50)),3)
WHERE ISNULL(编号,'')=''
insert 人员
--select 'A009' union all
select '' union all
select '' union all
select null union all
select null
select * from 人员begin tranUPDATE 人员
SET 编号='A'+RIGHT(1000 +
ISNULL
(
(SELECT CAST(MAX(RIGHT(编号,3)) AS NUMERIC) FROM 人员 WHERE 编号 LIKE 'A'+'%'),0
)+1
,3)WHERE ISNULL(编号,'')='' select * from 人员
rollback trandrop table 人员