一组数字,列名为kc,由很多最大为5个序列组成。如:
KC
1
2
3
4
5
1
2
3
4
5
1
2
3
1
2
3
4
1
1
2
3
4
5
…
请问怎么生成如下序列数据,XH列
KC XH
1 1
2 1
3 1
4 1
5 1
1 2
2 2
3 2
4 2
5 2
1 3
2 3
3 3
1 4
2 4
3 4
4 4
1 5
1 6
2 6
3 6
4 6
5 6
… …
小弟菜鸟,求各位高手帮忙解答,谢谢! 万分感谢!!!
KC
1
2
3
4
5
1
2
3
4
5
1
2
3
1
2
3
4
1
1
2
3
4
5
…
请问怎么生成如下序列数据,XH列
KC XH
1 1
2 1
3 1
4 1
5 1
1 2
2 2
3 2
4 2
5 2
1 3
2 3
3 3
1 4
2 4
3 4
4 4
1 5
1 6
2 6
3 6
4 6
5 6
… …
小弟菜鸟,求各位高手帮忙解答,谢谢! 万分感谢!!!
from a
from a
from
(
select a.kc,row_number over (partition by kc order by kc) xh
)
order by xh,kc
WITH a AS
(SELECT 1 kc
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 3
FROM DUAL
UNION ALL
SELECT 4
FROM DUAL
UNION ALL
SELECT 5
FROM DUAL
UNION ALL
SELECT 1
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 3
FROM DUAL
UNION ALL
SELECT 4
FROM DUAL
UNION ALL
SELECT 5
FROM DUAL
UNION ALL
SELECT 1
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 3
FROM DUAL
UNION ALL
SELECT 1
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 3
FROM DUAL
UNION ALL
SELECT 4
FROM DUAL
UNION ALL
SELECT 1
FROM DUAL
UNION ALL
SELECT 1
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 3
FROM DUAL
UNION ALL
SELECT 4
FROM DUAL
UNION ALL
SELECT 5
FROM DUAL)
上面的是生成测试语句,按你提供的数据
SELECT *
FROM (SELECT a.kc, ROW_NUMBER () OVER (PARTITION BY kc ORDER BY kc) xh
FROM a)
ORDER BY xh, kc结果
Row# KC XH1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
6 1 2
7 2 2
8 3 2
9 4 2
10 5 2
11 1 3
12 2 3
13 3 3
14 4 3
15 5 3
16 1 4
17 2 4
18 3 4
19 4 4
20 1 5
21 2 5
22 3 5
23 1 6
SELECT xh,kc
FROM (SELECT a.kc, ROW_NUMBER () OVER (PARTITION BY kc ORDER BY rowid) xh
FROM a)
ORDER BY xh, kc
这个你去试试看,可能会好点
我这生成的测试数据没有rowid的,所以没法测试出正确否
你是要更新还是要显示?
要更新用存储过程简单
FROM (SELECT kc,
DECODE (ROWNUM,
1, 1,
CASE
WHEN kc <= LAG (kc, 1, 0) OVER (ORDER BY ROWNUM)
THEN 1
ELSE 0
END
) temp_xh
FROM a)我测试下来的结果,和你要求的一样
Row# KC XH1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
6 1 2
7 2 2
8 3 2
9 4 2
10 5 2
11 1 3
12 2 3
13 3 3
14 1 4
15 2 4
16 3 4
17 4 4
18 1 5
19 1 6
20 2 6
21 3 6
22 4 6
23 5 6
SELECT kc, SUM (temp_xh) OVER (ORDER BY ROWNUM) xh
FROM (SELECT kc,
CASE
WHEN kc <= LAG (kc, 1, kc) OVER (ORDER BY ROWNUM)
THEN 1
ELSE 0
END
temp_xh
FROM a)
hebo2005的思路很好啊,不过要达到这个目的不需要那么复杂....SELECT kc, SUM(decode(kc, 1, 1, 0)) over(ORDER BY rownum) xh FROM a