例如有个table有个字段为PROJECT,根据PROJECT的升序,取出的数据为:
PROJECT
000A
000A
000A
000F
000F
000K
000P
000P由于要写个Insert into...select语句,插入对象table有两个字段是GROUPID,DETAILNO
这两个字段的取法为,根据PROJECT的不同,GROUPID从00001开始连续,在相同的PROJECT中,DETAILNO从1连续
结果如下:
PROJECT GROUPID DETAILNO
000A 00001 1
000A 00001 2
000A 00001 3
000F 00002 1
000F 00002 2
000K 00003 1
000P 00004 1
000P 00004 2想请问一下,这个能不能实现呢?
PROJECT
000A
000A
000A
000F
000F
000K
000P
000P由于要写个Insert into...select语句,插入对象table有两个字段是GROUPID,DETAILNO
这两个字段的取法为,根据PROJECT的不同,GROUPID从00001开始连续,在相同的PROJECT中,DETAILNO从1连续
结果如下:
PROJECT GROUPID DETAILNO
000A 00001 1
000A 00001 2
000A 00001 3
000F 00002 1
000F 00002 2
000K 00003 1
000P 00004 1
000P 00004 2想请问一下,这个能不能实现呢?
解决方案 »
- Oracle 安装问题:Error in invoking target 'install' of makefile ins_ctx.mk ,在线等待中。。
- oralce 查询物理表主键
- Oracle 10g 和 Oracle Developer 2000
- 存储框架为什么只能用固定值的SQL语句呢?
- 急!!!排序问题
- PL/SQL结果有错
- 一个关于分布式数据库连接问题!
- 咨询一下
- 在sql中如何查询表空间?
- 求救 抱错
- 请教在solaris10下安装oracle10.1.0.3时,ins_net_server.mk安装出错.
- 为什么oracle运行一段时间后,会自动断开连接报12560错误,重启电脑后又可以连接了
--------------------
000A
000A
000A
000F
000F
000K
000P
000P8 rows selected.
SQL> select pro,
2 decode(pro,'000A','00001','000F','00002','000K','00003','000P','00004') gro
upid,
3 row_number() over(partition by pro order by pro) detailno
4 from t1;PRO GROUP DETAILNO
-------------------- ----- ----------
000A 00001 1
000A 00001 2
000A 00001 3
000F 00002 1
000F 00002 2
000K 00003 1
000P 00004 1
000P 00004 28 rows selected.
如果你PROJECT字段下像上面那样只有少数几个值的话倒还可行 如果多的话在写DECODE函数的时候就有点麻烦了同学催着出去打球了 一会回来了再看看等其他朋友写出更好的来
----------
000A
000A
000A
000F
000F
000K
000P
000P已选择8行。已用时间: 00: 00: 00.00
13:53:50 scott@TUNGKONG> select a.project,to_char(rn,'0000') GROUPID,row_number() over(partition by a.project order by a.project) DETAILNO from tb a,
13:53:58 2 (select rownum rn,project from (select distinct project from tb order by project)) b
13:53:58 3 where a.project = b.project;PROJECT GROUP DETAILNO
---------- ----- ----------
000A 0001 1
000A 0001 2
000A 0001 3
000F 0002 1
000F 0002 2
000K 0003 1
000P 0004 1
000P 0004 2已选择8行。已用时间: 00: 00: 00.03
(select rownum rn,project from (select distinct project from tb order by project)) b
where a.project = b.project order by project;
to_char(dense_rank()over(order by project))groupid,
row_number()over(partition by project order by rownum)detailno
from table1
insert into proj values('000A');
insert into proj values('000A');
insert into proj values('000F');
insert into proj values('000F');
insert into proj values('000K');
insert into proj values('000P');
insert into proj values('000P');SELECT
project,
LPAD(
TO_CHAR(
dense_rank() over(order by project)
), 5, '0'
) groupid ,
ROW_NUMBER() over(partition by project order by project) detailno
FROM proj;
补充下select project,
to_char(dense_rank()over(order by project),'0000')groupid,
row_number()over(partition by project order by rownum)detailno
from table1
14:02:23 scott@TUNGKONG> select * from tb;PROJECT
----------
000A
000A
000A
000F
000F
000K
000P
000P已选择8行。已用时间: 00: 00: 00.03
14:02:28 scott@TUNGKONG> select project,to_char(dense_rank()over(order by project),'0000') GROUPID,row_number() over(partition by project order by project) DETAILNO
14:02:34 2 from tb order by project;PROJECT GROUP DETAILNO
---------- ----- ----------
000A 0001 1
000A 0001 2
000A 0001 3
000F 0002 1
000F 0002 2
000K 0003 1
000P 0004 1
000P 0004 2已选择8行。已用时间: 00: 00: 00.03
as
(
select '000A' PROJECT from dual
union all
select '000A' from dual
union all
select '000A' from dual
union all
select '000F' from dual
union all
select '000F' from dual
union all
select '000K' from dual
union all
select '000P' from dual
union all
select '000P' from dual
)
select a.project,b,rn from
(
select project,substr(project,1,3)||rownum b from
(select distinct project from temp order by project ))A ,
( select project,
row_number()over(partition by substr(project,length(project),1) order by project) rn from temp) b
where A.project=B.project(+)--result:
000A 0001 1
000A 0001 2
000A 0001 3
000F 0002 1
000F 0002 2
000K 0003 1
000P 0004 1
000P 0004 2
SQL> select pro,
2 lpad(to_char(dense_rank() over(order by pro)),5,'0') groupid,
3 row_number() over(partition by pro order by pro) detailno
4 from t1;PRO GROUPID DETAILNO
-------------------- --------------- ----------
000A 00001 1
000A 00001 2
000A 00001 3
000F 00002 1
000F 00002 2
000K 00003 1
000P 00004 1
000P 00004 28 rows selected.
我觉得都要经过to_char这一步,何必多此一举使用lpad函数?
本来只是想借用一个他的LPAD的
结果给写成一样的了
as
(
select '000A' PROJECT from dual
union all
select '000A' from dual
union all
select '000A' from dual
union all
select '000F' from dual
union all
select '000F' from dual
union all
select '000K' from dual
union all
select '000P' from dual
union all
select '000P' from dual
)select PROJECT,
lpad(to_char(dense_rank() over(order by PROJECT)),5,'0') groupid,
row_number() over(partition by PROJECT order by PROJECT) detailno
from temp;