有一张表是这样的
id ino_id gl_no inid
1 100 200 1
1 100 200 2
1 100 200 3
1 100 200 4
2 101 201 1
2 101 201 2
2 101 201 3
2 101 201 4
2 101 201 5
2 101 201 6
3 102 202 1
3 102 202 2
3 102 202 3
3 102 202 4
3 102 202 5
3 102 202 6
3 102 202 7
3 102 202 8
4 103 203 1
4 103 203 2
4 103 203 3
4 103 203 4
4 103 203 5
4 103 203 6查询出来的数据大概是以上这样,
现在有个问题就是
如何根据表id 1 2 3 4 区分开inid的值
比如说表id为1 那么inid就是自动增长1 2 3 4
换到id为2时 那么inid的值又从1开始自动增长
换到id为3时 那么inid的值又是从1开始自动增长如何根据id的值来区分inid的值 还请各位帮帮忙啊
id ino_id gl_no inid
1 100 200 1
1 100 200 2
1 100 200 3
1 100 200 4
2 101 201 1
2 101 201 2
2 101 201 3
2 101 201 4
2 101 201 5
2 101 201 6
3 102 202 1
3 102 202 2
3 102 202 3
3 102 202 4
3 102 202 5
3 102 202 6
3 102 202 7
3 102 202 8
4 103 203 1
4 103 203 2
4 103 203 3
4 103 203 4
4 103 203 5
4 103 203 6查询出来的数据大概是以上这样,
现在有个问题就是
如何根据表id 1 2 3 4 区分开inid的值
比如说表id为1 那么inid就是自动增长1 2 3 4
换到id为2时 那么inid的值又从1开始自动增长
换到id为3时 那么inid的值又是从1开始自动增长如何根据id的值来区分inid的值 还请各位帮帮忙啊
id,
ino_id,
gl_no,
inid=row_number() over(partition by id order by getdate())
from
tb
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[ino_id] int,[gl_no] int,[inid] int)
insert [tb]
select 1,100,200,1 union all
select 1,100,200,2 union all
select 1,100,200,3 union all
select 1,100,200,4 union all
select 2,101,201,1 union all
select 2,101,201,2 union all
select 2,101,201,3 union all
select 2,101,201,4 union all
select 2,101,201,5 union all
select 2,101,201,6 union all
select 3,102,202,1 union all
select 3,102,202,2 union all
select 3,102,202,3 union all
select 3,102,202,4 union all
select 3,102,202,5 union all
select 3,102,202,6 union all
select 3,102,202,7 union all
select 3,102,202,8 union all
select 4,103,203,1 union all
select 4,103,203,2 union all
select 4,103,203,3 union all
select 4,103,203,4 union all
select 4,103,203,5 union all
select 4,103,203,6
---查询---
select
id,
ino_id,
gl_no,
inid=row_number() over(partition by id order by getdate())
from
tb---结果---
id ino_id gl_no inid
----------- ----------- ----------- --------------------
1 100 200 1
1 100 200 2
1 100 200 3
1 100 200 4
2 101 201 1
2 101 201 2
2 101 201 3
2 101 201 4
2 101 201 5
2 101 201 6
3 102 202 1
3 102 202 2
3 102 202 3
3 102 202 4
3 102 202 5
3 102 202 6
3 102 202 7
3 102 202 8
4 103 203 1
4 103 203 2
4 103 203 3
4 103 203 4
4 103 203 5
4 103 203 6(24 行受影响)
go
create table tb(id int,ino_id int,gl_no int,inid int)
go
create trigger tri_tb on tb instead of insert
as
begin
select *,rn1=identity(int,1,1) into #t1 from inserted
select *,rn2=(select count(1) from #t1 where id=t.id and rn1<=t.rn1) into #t2 from #t1 t
insert tb select i.id,i.ino_id,i.gl_no,isnull(max(t.inid),0)+i.rn2
from #t2 i left join tb t on t.id=i.id group by i.id,i.ino_id,i.gl_no,i.rn2
end
go
insert tb
select 1,100,200,null union all
select 1,100,200,null union all
select 2,101,201,null union all
select 3,102,202,null union all
select 4,103,203,null
go
insert tb
select 1,100,200,null union all
select 1,100,200,null union all
select 2,101,201,null union all
select 3,102,202,null union all
select 4,103,203,null
go
select * from tb order by id,inid/*
id ino_id gl_no inid
----------- ----------- ----------- -----------
1 100 200 1
1 100 200 2
1 100 200 3
1 100 200 4
2 101 201 1
2 101 201 2
3 102 202 1
3 102 202 2
4 103 203 1
4 103 203 2(10 行受影响)*/