这个分析函数如何改？

显示全部楼层 · 2008-2-13 12:43:03

大家好，问一个菜鸟问题：
create table A_TEST
(
F_CODEA CHAR(1),
F_CODEB CHAR(1),
F_SUM NUMBER(2)
)

f_codeaf_codeb
f_sum

1
2
45

2
2
23

1
3
45

1
2
23
我写了一个SQL
SELECT F_CODEA, COUNT(f_codeb) OVER(PARTITION BY F_CODEA ORDER BY F_SUM RANGE UNBOUNDED PRECEDING)
FROM A_TEST
得出的结果是
1
1
1
3
1
3
2
1
我想要的结果是
1
1
1
2
1
3
2
1
请问如何改？

千问 · 2008-2-13 12:43:03

你的数据有问题
f_codea f_codeb f_sum
1 1 45
2 2 23
1 3 45
1 2 23
ELECT F_CODEA, COUNT(f_codeb) OVER(PARTITION BY F_CODEA ORDER BY F_CODEb RANGE UNBOUNDED PRECEDING)
FROM A_TEST
maybe ok

千问 · 2008-2-13 12:43:03

你这样写试一试看，应该是你的order by 有问题
SELECT F_CODEA, COUNT(f_codeb) OVER(PARTITION BY F_CODEA ORDER BY F_CODEARANGE UNBOUNDED PRECEDING)
FROM A_TEST

千问 · 2008-2-13 12:43:03

sELECT F_CODEA,f_codeb
rank()
OVER(PARTITION BY F_CODEA ORDER BY F_CODEb )

千问 · 2008-2-13 12:43:03

SELECT TT.F_CODEA,ROW_NUMBER() OVER(PARTITION BY TT.F_CODEB ORDER BY TT.F_SUM)AFROM A_TEST TT
ORDER BY F_CODEA,A