表信息如下
a0188 id jlh
4343 NULL 157509
4344 NULL 5472
4344 NULL 5473
4344 NULL 5474
4344 NULL 5476
4344 NULL 10450
4344 NULL 18096
4344 NULL 131946
4345 NULL 2507
4345 NULL 2509
4345 NULL 123075
4345 NULL 125824
4345 NULL 147097
4345 NULL 157325
4345 NULL 157326
4346 NULL 2511
4346 NULL 18220
4346 NULL 148259
想给ID排序按照 a0188组且按照JLH大小顺序排
想得到的结果如下
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
a0188 id jlh
4343 NULL 157509
4344 NULL 5472
4344 NULL 5473
4344 NULL 5474
4344 NULL 5476
4344 NULL 10450
4344 NULL 18096
4344 NULL 131946
4345 NULL 2507
4345 NULL 2509
4345 NULL 123075
4345 NULL 125824
4345 NULL 147097
4345 NULL 157325
4345 NULL 157326
4346 NULL 2511
4346 NULL 18220
4346 NULL 148259
想给ID排序按照 a0188组且按照JLH大小顺序排
想得到的结果如下
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
解决方案 »
- 数据库一个表两个查询结果连起来
- sql查询语句
- sql server 2005 错误:10060
- 求一条组合字符串的SQL语句
- 处理过sql server大数据量的高手请指教! 单表数据量300W以上。等牛头人!
- 安全性问题!
- 如何处理数据量大的表, 设计上的几个大数据量的优化方法请大家看看,请帮我提出点建议
- 请问:日志信息的数据格式是什么?象timestamp等
- 如何判断当前的数据库是sql server7.0还是sql server2000?
- 高分求救高手:[Microsoft][ODBC SQL Server Driver]没有执行可选特性
- SQL备注字段问题
- 请教一菜问题,分析器如何调试有返回值的存储过程
id=(select count(1)+1 from tb where [a0188]=t.[a0188] and JLH<t.JLH)
JLH
from tb t
order by 1,2,3
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,148259select [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 行)*/
(
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,148259select 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 行受影响)
小地方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,148259select 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 desca0188 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