表A:
A01
-----------
M00002987
...
现想插入一笔数据来源于表B,insert到表A里，想自动根据现有表A的A字段目前是M00002987 ，新插入进来的数据从M00002988开始，第一个编号是系统自动生成。
目标就是把B表的数据的count数目累加加到A01字段。
比如B表有10条，则A表A01字段从M00002988增加到M00002998. 自动编号是9位，M+8位递增数据。
------解决方案--------------------
CREATE TABLE #a (a01 CHAR(9))
INSERT #a
SELECT 'M00002987'
CREATE TABLE #b (b01 int)
INSERT #b
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
;WITH a1 AS
(
SELECT RIGHT(MAX(a01),8) a01 FROM #a
)
INSERT #a
SELECT 'M'+RIGHT('0000000'+RTRIM(ROW_NUMBER() OVER(ORDER BY @@servername)+a.a01),8)
FROM a1 a,#b
------解决方案--------------------
select 'M'+right('00000'+cast(right('M00002988',8)+rownum as varchar),8)
from
(
select *,row_number() over(order by getdate()) rownum
from b
)t
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-02-19 12:28:17
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([A01] varchar(9))
insert [A]
select 'M00002987'
--------------开始查询--------------------------
select A01,'M'+RIGHT('0000000'+CAST(CAST(SUBSTRING(a01,2,LEN(a01))AS INT)+1 as varchar),8)
from [A]
----------------结果----------------------------
/*
A01
--------- -----------------
M00002987 M00002988
*/
------解决方案--------------------
select A01,'M'+RIGHT('0000000'+CAST(CAST(SUBSTRING(a01,2,LEN(a01))AS INT)+1 as varchar),7)
from [A]