目前表内容如下:
id(主键)
AAAA0001
AAAA0002
AAAA0005
BBBB0001
BBBB0003
BBBB0004请问怎样才能得出如下结果:
AAAA0001
AAAA0002
AAAA0003
BBBB0001
BBBB0002
BBBB0003
请各位赐教,谢谢!
id(主键)
AAAA0001
AAAA0002
AAAA0005
BBBB0001
BBBB0003
BBBB0004请问怎样才能得出如下结果:
AAAA0001
AAAA0002
AAAA0003
BBBB0001
BBBB0002
BBBB0003
请各位赐教,谢谢!
CREATE TABLE tb(
ID1 char(2) NOT NULL,
ID2 char(4) NOT NULL,
col int,
PRIMARY KEY(ID1,ID2))
INSERT tb SELECT 'aa','0001',1
UNION ALL SELECT 'aa','0003',2
UNION ALL SELECT 'aa','0004',3
UNION ALL SELECT 'bb','0005',4
UNION ALL SELECT 'bb','0006',5
UNION ALL SELECT 'cc','0007',6
UNION ALL SELECT 'cc','0009',7
GO--重排编号处理
DECLARE @ID1 char(2),@ID2 int
UPDATE tb SET
@ID2=CASE WHEN @ID1=ID1 THEN @ID2+1 ELSE 10001 END,
@ID1=ID1,ID2=RIGHT(@ID2,4)
SELECT * FROM tb
/*--结果
ID1 ID2 col
---- ---- -----------
aa 0001 1
aa 0002 2
aa 0003 3
bb 0001 4
bb 0002 5
cc 0001 6
cc 0002 7
--*/
AAAA0001
--
declare @s varchar(10),@i int
set @I = 1
update ta
set id = left(id,4)+right('0000'+ltrim(@i),4),@i = case when @s = left(id,4) then @I + 1 else 1 end, @s = left(id,4)
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] varchar(8))
insert [TB]
select 'AAAA0001' union all
select 'AAAA0002' union all
select 'AAAA0005' union all
select 'BBBB0001' union all
select 'BBBB0003' union all
select 'BBBB0004'select left(id,7)+cast((select count(id)+1 from TB where left(t.id,4)=left(id,4) and right(t.id,1)>right(id,1))as varchar(10)) from TB t/*------------------
AAAA0001
AAAA0002
AAAA0003
BBBB0001
BBBB0002
BBBB0003(6 行受影响)*/
drop table TB
-- Author: happyflystone
-- Version:V1.001
-- Date:2009-08-19 22:30:16
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id nvarchar(8))
Go
Insert into ta
select 'AAAA0001' union all
select 'AAAA0002' union all
select 'AAAA0005' union all
select 'BBBB0001' union all
select 'BBBB0003' union all
select 'BBBB0004'
Go
--Start
declare @s varchar(10),@i int
set @I = 1
update ta
set id = left(id,4)+right('0000'+ltrim(@i),4),@i = case when @s = left(id,4) then @I + 1 else 1 end, @s = left(id,4)Select * from ta
--Result:
/*
id
--------
AAAA0001
AAAA0002
AAAA0003
BBBB0001
BBBB0002
BBBB0003(所影响的行数为 6 行)*/
--End
declare @a table(id char(8))
insert @a select
'AAAA0001'union all select
'AAAA0002'union all select
'AAAA0005'union all select
'BBBB0001'union all select
'BBBB0003'union all select
'BBBB0004'
;with szy as
(
select left(id,4)as id ,px=row_number()over(partition by left(id,4) order by right(id,4))
from @a
)
select id=id+right('0000'+ltrim(px),4) from szyid
----------------
AAAA0001
AAAA0002
AAAA0003
BBBB0001
BBBB0002
BBBB0003(6 行受影响)
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id varchar(10))
go
insert tb SELECT
'AAAA0001' UNION ALL SELECT
'AAAA0002' UNION ALL SELECT
'AAAA0005' UNION ALL SELECT
'BBBB0001' UNION ALL SELECT
'BBBB0003' UNION ALL SELECT
'BBBB0004'
go
declare @s varchar(10),@i int
set @I = 1
update tb
set
@i = case when @s = left(id,4) then @I + 1 else 1 end,
@s = left(id,4),
id = left(id,4)+right('10000'+ltrim(@i),4)
select * from tb
/*
id
----------
AAAA0001
AAAA0002
AAAA0003
BBBB0001
BBBB0002
BBBB0003(6 行受影响)
*/
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] varchar(8))
insert [TB]
select 'AAAA0001' union all
select 'AAAA0002' union all
select 'AAAA0005' union all
select 'BBBB0001' union all
select 'BBBB0003' union all
select 'BBBB0004'select left(id,4)+right('0000'+
(select cast (count(1)+1 as varchar(100)) as cnt from tb where left(id,4) = left(A.id,4) and
right(id,4) < right(A.id,4) ),4)
from tb A id
--------
AAAA0001
AAAA0002
AAAA0003
BBBB0001
BBBB0002
BBBB0003
因为表中有几千笔数据,不是只有几笔,我想按主键的前四位分组,每个分组都由‘0001’开始编主键的后四位。
happyflystone(无枪狙击手)的答案 应该可以解决问题,但是能不能更加详细一些?