if object_id('tb') is not null drop table tb go create table tb(id INT IDENTITY,时间 varchar(10),货物名单 varchar(10)) insert tb(时间,货物名单) select '8:00','西瓜' union all select '8:01','西瓜' union all select '8:02','西瓜' union all select '8:03','西瓜' union all select '8:05','西瓜' union all select '8:55','西瓜' union all select '8:56','香蕉' union all select '8:57','香蕉' union all select '8:57','西瓜' union all select '8:58','苹果' union all select '8:59','苹果' union all select '9:58','西瓜' union all select '9:59','西瓜' select * from tb godeclare @id int declare cur cursor for select id from tb open cur fetch next from cur into @id while @@fetch_status=0 begin declare @lev int,@count int set @lev=1 declare @t table(id INT,时间 varchar(10),货物名单 varchar(10),lev_i int) select @count=count(1) from @t print @count if(@count=0) begin insert into @t select *,@lev from tb where id=@id end else if(@count<>0) begin declare @levlast int,@货物名单L varchar(10),@levnow int,@货物名单N varchar(10) set @levnow=0 set @levlast=1 select @levlast=lev_i,@货物名单L=货物名单 from @t where id=(@id-1) select @货物名单N=货物名单 from tb where id=@id if(@货物名单N<>@货物名单L) begin set @levnow=@levlast+1 end else if (@货物名单N=@货物名单L) begin set @levnow=@levlast end insert into @t select *,@levnow from tb where id=@id end fetch next from cur into @id end close cur deallocate curselect A.地方,A.次数 from ( select 货物名单 as 地方,lev_i,count(1) as 次数 from @t group by lev_i ,货物名单 ) A order by A.lev_i楼主:第一个功能要是慢的话试一下这个
drop table tb
go
create table tb(id INT IDENTITY,时间 varchar(10),货物名单 varchar(10))
insert tb(时间,货物名单)
select '8:00','西瓜' union all
select '8:01','西瓜' union all
select '8:02','西瓜' union all
select '8:03','西瓜' union all
select '8:05','西瓜' union all
select '8:55','西瓜' union all
select '8:56','香蕉' union all
select '8:57','香蕉' union all
select '8:57','西瓜' union all
select '8:58','苹果' union all
select '8:59','苹果' union all
select '9:58','西瓜' union all
select '9:59','西瓜'
select * from tb
godeclare @id int
declare cur cursor for select id from tb
open cur
fetch next from cur into @id
while @@fetch_status=0
begin
declare @lev int,@count int
set @lev=1
declare @t table(id INT,时间 varchar(10),货物名单 varchar(10),lev_i int)
select @count=count(1) from @t
print @count
if(@count=0)
begin
insert into @t
select *,@lev from tb where id=@id
end
else if(@count<>0)
begin
declare @levlast int,@货物名单L varchar(10),@levnow int,@货物名单N varchar(10)
set @levnow=0
set @levlast=1
select @levlast=lev_i,@货物名单L=货物名单 from @t where id=(@id-1)
select @货物名单N=货物名单 from tb where id=@id
if(@货物名单N<>@货物名单L)
begin
set @levnow=@levlast+1
end
else if (@货物名单N=@货物名单L)
begin
set @levnow=@levlast
end
insert into @t
select *,@levnow from tb where id=@id
end
fetch next from cur into @id
end
close cur
deallocate curselect A.地方,A.次数 from
(
select 货物名单 as 地方,lev_i,count(1) as 次数 from @t
group by lev_i ,货物名单
) A
order by A.lev_i楼主:第一个功能要是慢的话试一下这个