34,590
社区成员
发帖
与我相关
我的任务
分享
写错了个
小地方
create table #TT
(
a0188 int,
id nchar(10),
jlh int
)
insert into #TT select 4343,null,157509
insert into #TT select 4344,null,5472
insert into #TT select 4344,null,5473
insert into #TT select 4344,null,5474
insert into #TT select 4344,null,5476
insert into #TT select 4344,null,10450
insert into #TT select 4344,null,18096
insert into #TT select 4344,null,131946
insert into #TT select 4345,null,2507
insert into #TT select 4345,null,2509
insert into #TT select 4345,null,123075
insert into #TT select 4345,null,125824
insert into #TT select 4345,null,147097
insert into #TT select 4345,null,157325
insert into #TT select 4345,null,157326
insert into #TT select 4346,null,2511
insert into #TT select 4346,null,18220
insert into #TT select 4346,null,148259
select a0188,
(select count(*)+1 from #TT where a0188=T.a0188 and jlh<T.jlh) ID,
jlh
from #TT T
order by a0188 asc,ID asc,jlh desc
a0188 ID jlh
----------- ----------- -----------
4343 1 157509
4344 1 5472
4344 2 5473
4344 3 5474
4344 4 5476
4344 5 10450
4344 6 18096
4344 7 131946
4345 1 2507
4345 2 2509
4345 3 123075
4345 4 125824
4345 5 147097
4345 6 157325
4345 7 157326
4346 1 2511
4346 2 18220
4346 3 148259
create table #TT
(
a0188 int,
id nchar(10),
jlh int
)
insert into #TT select 4343,null,157509
insert into #TT select 4344,null,5472
insert into #TT select 4344,null,5473
insert into #TT select 4344,null,5474
insert into #TT select 4344,null,5476
insert into #TT select 4344,null,10450
insert into #TT select 4344,null,18096
insert into #TT select 4344,null,131946
insert into #TT select 4345,null,2507
insert into #TT select 4345,null,2509
insert into #TT select 4345,null,123075
insert into #TT select 4345,null,125824
insert into #TT select 4345,null,147097
insert into #TT select 4345,null,157325
insert into #TT select 4345,null,157326
insert into #TT select 4346,null,2511
insert into #TT select 4346,null,18220
insert into #TT select 4346,null,148259
select a0188,
(select count(*)+1 from #TT where a0188=T.a0188 and jlh>T.jlh) ID,
jlh
from #TT T
order by a0188 asc,ID asc
a0188 ID jlh
----------- ----------- -----------
4343 1 157509
4344 1 131946
4344 2 18096
4344 3 10450
4344 4 5476
4344 5 5474
4344 6 5473
4344 7 5472
4345 1 157326
4345 2 157325
4345 3 147097
4345 4 125824
4345 5 123075
4345 6 2509
4345 7 2507
4346 1 148259
4346 2 18220
4346 3 2511
(18 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a0188] int,[id] sql_variant,[jlh] int)
insert [tb]
select 4343,null,157509 union all
select 4344,null,5472 union all
select 4344,null,5473 union all
select 4344,null,5474 union all
select 4344,null,5476 union all
select 4344,null,10450 union all
select 4344,null,18096 union all
select 4344,null,131946 union all
select 4345,null,2507 union all
select 4345,null,2509 union all
select 4345,null,123075 union all
select 4345,null,125824 union all
select 4345,null,147097 union all
select 4345,null,157325 union all
select 4345,null,157326 union all
select 4346,null,2511 union all
select 4346,null,18220 union all
select 4346,null,148259
select [a0188],
id=(select count(1)+1 from tb where [a0188]=t.[a0188] and JLH<t.JLH),
JLH
from tb t
order by 1,2,3
--测试结果:
/*
a0188 id JLH
----------- ----------- -----------
4343 1 157509
4344 1 5472
4344 2 5473
4344 3 5474
4344 4 5476
4344 5 10450
4344 6 18096
4344 7 131946
4345 1 2507
4345 2 2509
4345 3 123075
4345 4 125824
4345 5 147097
4345 6 157325
4345 7 157326
4346 1 2511
4346 2 18220
4346 3 148259
(所影响的行数为 18 行)
*/
select [a0188],
id=(select count(1)+1 from tb where [a0188]=t.[a0188] and JLH<t.JLH)
JLH
from tb t
order by 1,2,3
select * from tb order by a0188 asc,id desc,JLH desc