这种查询该如何写?

[lhb2000]

表
ID LB MC JE SJ
1 AA 001 1 2007-10-1
2 AA 002 2 2007-10-2
3 BB 001 1 2007-10-5
4 CC 005 5 2007-10-7
5 CC 007 8 2007-10-8
6 DD 007 8 2007-10-11
....
如何得到如下的查询结果
1 AA 001 1 2007-10-1 1
2 AA 002 2 2007-10-2 1
3 BB 001 1 2007-10-5 2
4 CC 005 5 2007-10-7 3
5 CC 007 8 2007-10-8 3
6 DD 007 8 2007-10-11 4

就是得到LB不重复的排序

[中国风]

select *,[排序]=(select count(distinct LB) from ta where LB!>t.LB)
from
ta t

[中国风]

declare @t table(ID int,      LB  nvarchar(5),     MC  nvarchar(3),   JE  int,   SJ datetime)
insert @t select 1,         'AA',       '001',     1,     '2007-10-1' 
insert @t select 2,         'AA',       '002',     2,     '2007-10-2' 
insert @t select 3,         'BB',       '001',     1,     '2007-10-5' 
insert @t select 4,         'CC',       '005',     5,     '2007-10-7' 
insert @t select 5,         'CC',       '007',     8,     '2007-10-8' 
insert @t select 6,         'DD',       '007',     8,     '2007-10-11' 

select
	*,
	[Order]=(select count(distinct LB) from @T where LB!>t.LB)
from 
	@t t


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）

ID          LB    MC   JE          SJ                                                     Order       
----------- ----- ---- ----------- ------------------------------------------------------ ----------- 
1           AA    001  1           2007-10-01 00:00:00.000                                1
2           AA    002  2           2007-10-02 00:00:00.000                                1
3           BB    001  1           2007-10-05 00:00:00.000                                2
4           CC    005  5           2007-10-07 00:00:00.000                                3
5           CC    007  8           2007-10-08 00:00:00.000                                3
6           DD    007  8           2007-10-11 00:00:00.000                                4

（所影响的行数为 6 行）

[中国风]

declare @t table(ID int,      LB  nvarchar(5),     MC  nvarchar(3),   JE  int,   SJ datetime)
insert @t select 1,         'AA',       '001',     1,     '2007-10-1' 
insert @t select 2,         'AA',       '002',     2,     '2007-10-2' 
insert @t select 3,         'BB',       '001',     1,     '2007-10-5' 
insert @t select 4,         'CC',       '005',     5,     '2007-10-7' 
insert @t select 5,         'CC',       '007',     8,     '2007-10-8' 
insert @t select 6,         'DD',       '007',     8,     '2007-10-11' 

select
	*,
	[Order]=(select count(distinct LB) from @T where SJ!>t.SJ)--换作时间做为条件
from 
	@t t


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）

ID          LB    MC   JE          SJ                                                     Order       
----------- ----- ---- ----------- ------------------------------------------------------ ----------- 
1           AA    001  1           2007-10-01 00:00:00.000                                1
2           AA    002  2           2007-10-02 00:00:00.000                                1
3           BB    001  1           2007-10-05 00:00:00.000                                2
4           CC    005  5           2007-10-07 00:00:00.000                                3
5           CC    007  8           2007-10-08 00:00:00.000                                3
6           DD    007  8           2007-10-11 00:00:00.000                                4

（所影响的行数为 6 行）

[-狙击手-]

declare @A table(id int,lb nvarchar(2),mc nvarchar(5),je int,sj varchar(10)) 
insert @a select 1,'AA','001',1,'2007-10-1' 
insert @a select 2,'AA','002',2,'2007-10-2' 
insert @a select 3,'BB','001',1,'2007-10-5' 
insert @a select 4,'CC','005',5,'2007-10-7' 
insert @a select 5,'CC','007',8,'2007-10-8' 
insert @a select 6,'DD','007',8,'2007-10-11' 


select 
    *,[order]=(select count(distinct lb) from @A where ID<=t.ID)
from 
    @A t
/*
id          lb   mc    je          sj         order       
----------- ---- ----- ----------- ---------- ----------- 
1           AA   001   1           2007-10-1  1
2           AA   002   2           2007-10-2  1
3           BB   001   1           2007-10-5  2
4           CC   005   5           2007-10-7  3
5           CC   007   8           2007-10-8  3
6           DD   007   8           2007-10-11 4
*/

[liangCK]

declare @t table(ID int,LB varchar(10),MC varchar(10),JE int,SJ datetime)
insert @t select 1,'AA','001',1,'2007-10-1'
union all select 2,'AA','002',2,'2007-10-2'
union all select 3,'BB','001',1,'2007-10-5'
union all select 4,'CC','005',5,'2007-10-7'
union all select 5,'CC','007',8,'2007-10-8'
union all select 6,'DD','007',8,'2007-10-11'

select a.*,(select count(distinct LB) from @t where ID!>a.ID) from @t a

同意roy_88

[lhb2000]

测试成功 结贴

[pt1314917]

declare @t table(id int,lb varchar(10),mc varchar(10),je int,sj datetime)
insert into @t select 1,'AA','001',1,'2007-10-1' 
insert into @t select 2,'AA','002',2,'2007-10-2' 
insert into @t select 3,'BB','001',1,'2007-10-5' 
insert into @t select 4,'CC','005',5,'2007-10-7' 
insert into @t select 5,'CC','007',8,'2007-10-8' 
insert into @t select 6,'DD','007',8,'2007-10-11' 

select bh=identity(int,1,1),lb into #temp from @t group by lb
select a.*,b.bh from @t a,#temp b where a.lb=b.lb

[lhb2000]

用时间作条件时 当时间相同的结果就不对了