求帮帮，一SQL 的实现指令

[microd]

原表数据
-----------------------------
M1 M2 M3
-------------- ------ ---
20120419000001 KH0001 NULL
20120425000001 KH0002 NULL
20120503000001 KH0002 NULL
20120503000002 KH0004 NULL

想得的结果
-----------------------------
M1 M2 M3
-------------- ------ ---
20120419000001 KH0001 008
20120425000001 KH0002 009
20120503000001 KH0002 009
20120503000002 KH0004 010

---说明：
M3 的值不是从001 开始 ，是从前面的号增加1 ，但M2的值相同，就一定是同一个值。

[fcuandy]

这样?

create table tb(M1 VARCHAR(50),M2 VARCHAR(20),M3 VARCHAR(3))
GO
INSERT TB SELECT '20120419000001', 'KH0001', NULL
UNION ALL SELECT '20120425000001' ,'KH0002' ,NULL
UNION ALL SELECT '20120503000001' ,'KH0002' ,NULL
UNION ALL SELECT '20120503000002', 'KH0004' ,NULL
go
DECLARE @n INT,@x VARCHAR(20)
SET @n=7
UPDATE TB SET @n=CASE WHEN @x=m2 THEN @n ELSE @n+1 END,@x=M2,m3=RIGHT('000'+ RTRIM(@n),3)

SELECT * FROM Tb
/*
20120419000001	KH0001	008
20120425000001	KH0002	009
20120503000001	KH0002	009
20120503000002	KH0004	010
*/
drop table tb
go

[zhhcool123]

二楼高人，我到现在还没看明白楼主什么意思

[microd]

,

[唐诗三百首]

create table mic
(M1 varchar(20), M2 varchar(10), M3 varchar(10))

insert into mic
select '20120419000001', 'KH0001', null union all
select '20120425000001', 'KH0002', null union all
select '20120503000001', 'KH0002', null union all
select '20120503000002', 'KH0004', null


select M1,M2,
       replicate('0',3-len(8+(dense_rank() over(order by M2)-1)))
       +cast(8+dense_rank() over(order by M2)-1 as varchar) 'M3'
from mic

/*
M1                   M2         M3
-------------------- ---------- -------
20120419000001       KH0001     008
20120425000001       KH0002     009
20120503000001       KH0002     009
20120503000002       KH0004     010

(4 row(s) affected)
*/

[黄_瓜]

--> 测试数据：#tb IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([M1] BIGINT,[M2] VARCHAR(6),[M3] SQL_VARIANT) INSERT #tb SELECT 20120419000001,'KH0001',NULL UNION ALL SELECT 20120425000001,'KH0002',NULL UNION ALL SELECT 20120503000001,'KH0002',NULL UNION ALL SELECT 20120503000002,'KH0004',NULL --------------开始查询-------------------------- SELECT *,RIGHT(1000+DENSE_RANK() OVER(ORDER BY [M2])+7,3) FROM #tb ----------------结果---------------------------- /* M1 M2 M3 (无列名) 20120419000001 KH0001 NULL 008 20120425000001 KH0002 NULL 009 20120503000001 KH0002 NULL 009 20120503000002 KH0004 NULL 010 */

[黄_瓜]

引用 4 楼 ap0405140 的回复:

SQL code?12345678910111213141516171819202122232425create table mic(M1 varchar(20), M2 varchar(10), M3 varchar(10)) insert into micselect '20120419000001', 'KH0001', null union allselect '……

唐诗mm很艺术

[microd]

原始数据一定要按M2 排序，否则。。。