CREATE TABLE DEMO
(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20),
CODE VARCHAR(20)
)希望效果如下:
NAME CODE
A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3就是每个name都有自己的流水编号,独立的 。我目前的方法就是INSERT的时候查询再插入
有点笨 ,先听听大家有没有更好 更灵活的解决方案。
(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20),
CODE VARCHAR(20)
)希望效果如下:
NAME CODE
A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3就是每个name都有自己的流水编号,独立的 。我目前的方法就是INSERT的时候查询再插入
有点笨 ,先听听大家有没有更好 更灵活的解决方案。
create view v_demo
as
select name,count(0) code from demo
group by nameinsert into demo(name,code)
select @name,(select code+1 from v_demo where name=@name)
select id,name,code=row_number() over (partition by name order by id)
from DEMO
create table tb(name varchar(10))
insert into tb select 'A'
insert into tb select 'A'
insert into tb select 'A'
insert into tb select 'B'
insert into tb select 'B'
insert into tb select 'C'
insert into tb select 'C'
insert into tb select 'C'select code=row_number()over(partition by name order by name),name from tb
/*
code name
-------------------- ----------
1 A
2 A
3 A
1 B
2 B
1 C
2 C
3 C(8 個資料列受到影響)
*/
insert into demo(name,code)
select 'A',max(code)+1
from demo
where name='A'
declare @code int,@id=id
select @id=id from inserted
select @code=row_number() over (partition by name order by id)
from DEMO where id=@id --这个@id是你传入的
print @code