--'不好意思,发错了点--'原始数据
declare @table table(a1 int,a2 int,Data int)
insert into @table select 1,1,10
insert into @table select 2,1,11
insert into @table select 3,1,12
insert into @table select 4,1,13
insert into @table select 5,2,10
insert into @table select 6,2,11
insert into @table select 7,2,12
insert into @table select 8,2,13
--'结果数据
declare @Result table(a1 int,a2 int,Data int,Seq int)
insert into @Result select 1,1,10,1
insert into @Result select 2,1,11,2
insert into @Result select 3,1,12,3
insert into @Result select 4,1,13,4
insert into @Result select 5,2,10,1
insert into @Result select 6,2,11,2
insert into @Result select 7,2,12,3
insert into @Result select 8,2,13,4--'原始数据
select * from @table
--'想通过批处理得到如下所示结果结果:(将a1与a2中的数据进行排序)
select * from @Result
declare @table table(a1 int,a2 int,Data int)
insert into @table select 1,1,10
insert into @table select 2,1,11
insert into @table select 3,1,12
insert into @table select 4,1,13
insert into @table select 5,2,10
insert into @table select 6,2,11
insert into @table select 7,2,12
insert into @table select 8,2,13
--'结果数据
declare @Result table(a1 int,a2 int,Data int,Seq int)
insert into @Result select 1,1,10,1
insert into @Result select 2,1,11,2
insert into @Result select 3,1,12,3
insert into @Result select 4,1,13,4
insert into @Result select 5,2,10,1
insert into @Result select 6,2,11,2
insert into @Result select 7,2,12,3
insert into @Result select 8,2,13,4--'原始数据
select * from @table
--'想通过批处理得到如下所示结果结果:(将a1与a2中的数据进行排序)
select * from @Result
select *,identity(int,1,1) seq into #t from @table
select * from #t
drop table #t
select a.*,count(case when a.data >= b.data then 1 end) as Seq
from @table a join @table b on a.a2 = b.a2
group by a.a1,a.a2,a.data/*
a1 a2 Data Seq
----------- ----------- ----------- -----------
1 1 10 1
2 1 11 2
3 1 12 3
4 1 13 4
5 2 10 1
6 2 11 2
7 2 12 3
8 2 13 4(所影响的行数为 8 行)
*/
學習
select a1,a2,data,(select count(*) from @table a where [email protected] and a.a1<[email protected]) from @table