本帖最后由 claylee 于 2010-11-15 00:59 编辑
现在有数据表 pm
user_no ,type ,p_no, m_no
1001 a
1001 a
1001 a
1002 b
1002 d
1003 a
1004 a
1004 a
1004 b
1004 b
1004 c
1004 c
1004 c
处理后结果需要为
user_no ,type , p_no, m_no
1001 a 1 1
1001 a 1 2
1001 a 1 3
1002 b 1 1
1002 d 2 1
1003 a 1 1
1004 a 1 1
1004 a 1 2
1004 b 1 1
1004 b 1 2
1004 c 2 1
1004 c 2 2
1004 c 2 3
也就是需要p_no需要根据type扩展,m_no根据p_no扩展
谢谢各位~
现在有数据表 pm
user_no ,type ,p_no, m_no
1001 a
1001 a
1001 a
1002 b
1002 d
1003 a
1004 a
1004 a
1004 b
1004 b
1004 c
1004 c
1004 c
处理后结果需要为
user_no ,type , p_no, m_no
1001 a 1 1
1001 a 1 2
1001 a 1 3
1002 b 1 1
1002 d 2 1
1003 a 1 1
1004 a 1 1
1004 a 1 2
1004 b 1 1
1004 b 1 2
1004 c 2 1
1004 c 2 2
1004 c 2 3
也就是需要p_no需要根据type扩展,m_no根据p_no扩展
谢谢各位~
解决方案 »
- Oracle如何创建数据库?
- sql navigator 中怎么运行调试oracle存储过程?
- Oracle批量更新及聚合函数Group By的问题,在线等,急——————————————————————
- 動態的sql該如何寫?簡單的
- vc调用ORACLE的存储过程
- 求SQL(表A, 字段1为d1,字段2为d2.求SQL语句,功能要求:当d2为空时,用常量5跟d1比大,当d2不为空时5跟d2比大的所有记录)
- 为什么我的oracle设置的作业不能定时执行
- oracle如何存储doc文档
- 在delphi的query中使用oracle数据库时,怎样赋同一个变量的不同值
- 如何优化sql语句
- 存储过程中值累加
- isqlplus启动问题
USER_NO TYPE P_NO M_NO
---------- ---- ---------- ----------
1001 a
1001 a
1001 a
1002 b
1002 d
1003 a
1004 a
1004 a
1004 b
1004 b
1004 c
1004 c
1004 c
13 rows selected
SQL> update pm a
2 set a.p_no = (select count(distinct b.type)
3 from pm b
4 where b.type <= a.type
5 and a.user_no = b.user_no),
6 a.m_no = (select count(*)
7 from pm b
8 where a.user_no = b.user_no
9 and a.type = b.type
10 and b.rowid <= a.rowid)
11 ;
13 rows updated
SQL> select * from pm;
USER_NO TYPE P_NO M_NO
---------- ---- ---------- ----------
1001 a 1 1
1001 a 1 2
1001 a 1 3
1002 b 1 1
1002 d 2 1
1003 a 1 1
1004 a 1 1
1004 a 1 2
1004 b 2 1
1004 b 2 2
1004 c 3 1
1004 c 3 2
1004 c 3 3
13 rows selected
SQL>
with tab as(
select 1001 user_no, 'a' type from dual union all
select 1001 user_no, 'a' type from dual union all
select 1001 user_no, 'a' type from dual union all
select 1002 user_no, 'b' type from dual union all
select 1002 user_no, 'd' type from dual union all
select 1003 user_no, 'a' type from dual union all
select 1004 user_no, 'a' type from dual union all
select 1004 user_no, 'a' type from dual union all
select 1004 user_no, 'b' type from dual union all
select 1004 user_no, 'b' type from dual union all
select 1004 user_no, 'c' type from dual union all
select 1004 user_no, 'c' type from dual union all
select 1004 user_no, 'c' type from dual
)
select user_no,
type,
p_no,
row_number() over(partition by user_no, type, p_no order by user_no, type, p_no) m_no
from (select user_no,
type,
dense_rank() over(partition by user_no, type order by type) p_no
from tab)
SQL>
SQL> with tab as(
2 select 1001 user_no, 'a' type from dual union all
3 select 1001 user_no, 'a' type from dual union all
4 select 1001 user_no, 'a' type from dual union all
5 select 1002 user_no, 'b' type from dual union all
6 select 1002 user_no, 'd' type from dual union all
7 select 1003 user_no, 'a' type from dual union all
8 select 1004 user_no, 'a' type from dual union all
9 select 1004 user_no, 'a' type from dual union all
10 select 1004 user_no, 'b' type from dual union all
11 select 1004 user_no, 'b' type from dual union all
12 select 1004 user_no, 'c' type from dual union all
13 select 1004 user_no, 'c' type from dual union all
14 select 1004 user_no, 'c' type from dual
15 )
16 select user_no,
17 type,
18 p_no,
19 row_number() over(partition by user_no, type, p_no order by user_no, type, p_no) m_no
20 from (select user_no,
21 type,
22 dense_rank() over(partition by user_no, type order by type) p_no
23 from tab)
24 ; USER_NO TYPE P_NO M_NO
---------- ---- ---------- ----------
1001 a 1 1
1001 a 1 2
1001 a 1 3
1002 b 1 1
1002 d 1 1
1003 a 1 1
1004 a 1 1
1004 a 1 2
1004 b 1 1
1004 b 1 2
1004 c 1 1
1004 c 1 2
1004 c 1 313 rows selectedSQL>