要求根据SJPH这列生成日期加三位的领料单号，如“20151021001”。
首先查找领料单号那列最大的单号与今天日期相对比，如果小于今天的日期则以“20151021001”为第一个领料单号，如果存在今天的单号则需在原有最大单号的基础上加1
例子如下：
XH DB DH BJPH SJPH
0001 511 1509020187 206040000051 204010000488
0002 511 1509020187 204080000472 204010000488
0003 511 1509020187 204010000503 204010000488
0004 511 1509020187 204010000497 204010000488
0005 511 1509020187 204010000498 204010000488
0006 511 1509020187 204010000489 204010000488
0007 511 1509020187 204010000493 204010000488
0008 511 1509020187 204010000494 204010000488
0001 511 1509020189 204010000499 204010000490
0002 511 1509020189 204010000500 204010000490
0001 511 1509020194 204010000498 204010000495
最终实现结果：
XH DB DH BJPH SJPH LLDH
0001 511 1509020187 206040000051 204010000488 领料单号1
0002 511 1509020187 204080000472 204010000488 领料单号1
0003 511 1509020187 204010000503 204010000488 领料单号1
0004 511 1509020187 204010000497 204010000488 领料单号1
0005 511 1509020187 204010000498 204010000488 领料单号1
0006 511 1509020187 204010000489 204010000488 领料单号1
0007 511 1509020187 204010000493 204010000488 领料单号1
0008 511 1509020187 204010000494 204010000488 领料单号1
0001 511 1509020189 204010000499 204010000490 领料单号2
0002 511 1509020189 204010000500 204010000490 领料单号2
0001 511 1509020194 204010000498 204010000495 领料单号3
------解决思路----------------------
DECLARE @t TABLE(XH varchar(4), SJPH varchar(12), LLDH varchar(11))
DECLARE @batch int --测试批数
SET @batch = 1
WHILE @batch<=2
BEGIN
IF @batch = 1
INSERT INTO @t(XH,SJPH)
SELECT '0001','204010000488' UNION ALL
SELECT '0002','204010000488' UNION ALL
SELECT '0001','204010000490' UNION ALL
SELECT '0002','204010000490' UNION ALL
SELECT '0001','204010000491'
ELSE
INSERT INTO @t(XH,SJPH)
SELECT '0001','204010000492' UNION ALL
SELECT '0001','204010000493' UNION ALL
SELECT '0002','204010000493'
-- 处理过程
DECLARE @prefix varchar(6)
DECLARE @maxNum int
DECLARE @code varchar(11)
SET @prefix = CONVERT(varchar(6),GETDATE(),112)
SELECT @code = MAX(LLDH)
FROM @t
WHERE LLDH LIKE @prefix+'%'
IF @code IS NULL
SET @maxNum = 0
ELSE
SET @maxNum = CONVERT(int,RIGHT(@code,3))
;WITH a AS (
SELECT DISTINCT SJPH
FROM @t
WHERE LLDH IS NULL
)
,b AS (
SELECT SJPH,
@maxNum + ROW_NUMBER() OVER(ORDER BY SJPH) num
FROM a
)
UPDATE @t
SET t.LLDH = @prefix + RIGHT('000'+CONVERT(varchar(3),b.num),3)
FROM @t t, b
WHERE t.SJPH = b.SJPH
SELECT * FROM @t
SET @batch = @batch + 1
END
XH SJPH LLDH
---- ------------ -----------
0001 204010000488 201510001
0002 204010000488 201510001
0001 204010000490 201510002
0002 204010000490 201510002
0001 204010000491 201510003
XH SJPH LLDH
---- ------------ -----------
0001 204010000488 201510001
0002 204010000488 201510001
0001 204010000490 201510002
0002 204010000490 201510002
0001 204010000491 201510003
0001 204010000492 201510004
0001 204010000493 201510005
0002 204010000493 201510005
------解决思路----------------------
select * , '领料单' + cast(dense_rank() over(order by sjph) as varchar(10)) rnx
from test
go
(11 行受影响)
xh db dh bjph sjph rnx
-------------------- -------------------- -------------------- -------------------- -------------------- ----------------
0001 511 1509020187 206040000051 204010000488 领料单1
0002 511 1509020187 204080000472 204010000488 领料单1
0003 511 1509020187 204010000503 204010000488 领料单1
0004 511 1509020187 204010000497 204010000488 领料单1
0005 511 1509020187 204010000498 204010000488 领料单1
0006 511 1509020187 204010000489 204010000488 领料单1
0007 511 1509020187 204010000493 204010000488 领料单1
0008 511 1509020187 204010000494 204010000488 领料单1
0001 511 1509020189 204010000499 204010000490 领料单2
0002 511 1509020189 204010000500 204010000490 领料单2
0001 511 1509020194 204010000498 204010000495 领料单3
(11 行受影响)