Create table tb([ID] int IDENTITY(1,1),Num int,z int,f int)
create procedure GetNum
@i int
as
declare @m int
declare @c int
select @c=(select count(*) from tb group by Num having Num=@i)
if @c is null
set @c=1
select @m=Num from tb where id=(select max(id) from tb)
begin
if @m=@i
begin
insert into tb([Num],z,f) values(@i,@c,1)
update tb set f='1' where id=(select max(id) from tb)-1
select @c=(select count(*) from tb group by Num having Num=@i)
update tb set z=@c where id=(select max(id) from tb)
end
else
begin
insert into tb([Num],z,f) values(@i,@c,0)
select @c=(select count(*) from tb group by Num having Num=@i)
update tb set z=@c where id=(select max(id) from tb)
end
end
exec GetNum 3
go
exec GetNum 3
go
exec GetNum 3
go
exec GetNum 4
go
exec GetNum 4
go
exec GetNum 3
select Num ,count(f) from tb where f=1 group by Num having count(f) <20
Create table tb([ID] int IDENTITY(1,1),Num int,z int,f int)
create procedure GetNum
@i int
as
declare @m int
declare @c int
select @c=(select count(*) from tb group by Num having Num=@i)
if @c is null
set @c=1
select @m=Num from tb where id=(select max(id) from tb)
begin
if @m=@i
begin
insert into tb([Num],z,f) values(@i,@c,1)
update tb set f='1' where id=(select max(id) from tb)-1
select @c=(select count(*) from tb group by Num having Num=@i)
update tb set z=@c where id=(select max(id) from tb)
end
else
insert into tb([Num],z,f) values(@i,@c,0)
end
exec GetNum 3
go
exec GetNum 3
go
exec GetNum 3
go
exec GetNum 4
go
exec GetNum 4
go
exec GetNum 3
select Num ,count(f) from tb where f=1 group by Num having count(f)<20