一个比较难的问题,数据排序问题!

[lizhenlz]

--'原始数据
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,1,11
insert into @table select 7,1,12
insert into @table select 8,1,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,1,11,2
insert into @Result select 7,1,12,3
insert into @Result select 8,1,13,4

--'原始数据
select * from @table
--'想通过批处理得到如下所示结果结果:(将a1与a2中的数据进行排序)
select * from @Result

--类似于这种方法,我没成功,请大家帮忙(不用游标)
set nocount on
SELECT a.a1,count(b.Data)+1 as nCount
FROM @table a
left outer join @table b
on a.a1=b.a1 and a.Data<b.Data
group by a.a1 order by a.a1,count(b.Data)+1

[pbsql]

没整明白

[lizhenlz]

--'不好意思,发错了点

--'原始数据
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

[pbsql]

如下所示结果在哪里呢？

[lizhenlz]

结果为:select * from @Result

[pbsql]

是不是这意思：（加个行号）
select *,identity(int,1,1) seq into #t from @table
select * from #t
drop table #t

[shuiniu]

--try:
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語句應該不行吧
學習

[pbsql]

我明白了：
select a1,a2,data,(select count(*) from @table a where a.a2=@table.a2 and a.a1<=@table.a1) from @table

[大健]

慢了一步
原來已經做出來了....

[lizhenlz]

谢谢各位!