其他日常SQL题
# 1、先分组后排序，按组排序
# 题目描述
有表查询如下：
seq | str
----|----|
1 | a
2 | b
3 | b
4 | c
5 | c
6 | c
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
现要求查询结果如下：
seq | str | sc |
----|----|---------|
1 | a | 1
2 | b | 1
3 | b | 2
4 | c | 1
5 | c | 2
6 | c | 3
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 答案
with m as (
select 1 seq,'a' str from dual
union all
select 2 seq,'b' str from dual
union all
select 3 seq,'b' str from dual
union all
select 4 seq,'c' str from dual
union all
select 5 seq,'c' str from dual
union all
select 6 seq,'c' str from dual
)
/*select * from m*/
select a.seq,a.str,count(*) as sc
from m a
left join m b on a.str=b.str and a.seq>=b.seq
group by a.str ,a.seq
order by a.seq
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
编辑 (opens new window)
上次更新: 2023/10/13, 17:39:25