查询数据排名情况SQL

最新推荐文章于 2022-05-23 16:05:03 发布

原创最新推荐文章于 2022-05-23 16:05:03 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #integer #c #border #table #测试

INFORMIX 同时被 2 个专栏收录

32 篇文章

订阅专栏

数据库开发技术

11 篇文章

订阅专栏

1/准备测试数据

---------------------------------------------------------------------------------
create table t1(
c1 integer,
c2 integer,
c3 integer
);

insert into t1 values(1,2,3)

insert into t1 values(1,8,4)
insert into t1 values(1,4,4)

insert into t1 values(1,4,5)

insert into t1 values(1,5,5)

insert into t1 values(2,2,3)

insert into t1 values(2,8,4)
insert into t1 values(2,4,4)

insert into t1 values(2,4,5)

insert into t1 values(2,5,5)

2/查看排名

---------------------------------------------------------------------------------

A/单记录排名

select c1,c3,
(select count( c3)+1 from t1 a where a.c3>b.c3
and a.c1=b.c1 and a.c1 =1
) order_num
from t1 b
where c1 =1
order by c1,c3

c1          c3          order_num
----------- ----------- ----------------------
1           3           5
1           4           3
1           4           3
1           5           1
1           5           1
B/多记录排名

select c1,c2,c3,
(select count( c3)+1 from t1 a where a.c3>b.c3
and a.c1=b.c1
) order_num
from t1 b
order by c1,c3

c1          c2          c3          order_num
----------- ----------- ----------- ----------------------
1           2           3           5
1           8           4           3
1           4           4           3
1           4           5           1
1           5           5           1
2           2           3           5
2           8           4           3
2           4           4           3
2           4           5           1
2           5           5           1