with t as
(select aliushuihao,ascore+bscore score from teacher t1
(select liushuihao,sum(score) score from student group by liushuihao) t2
where aliushuihao=bliushuihao)
select brn,a from t a,(select liushuihao,row_number() over (order by liushuihao) rn from t ) b
where aliushuihao=bliushuihao
能看懂吧?
先按照不重复把流水号排出序号,然后去关联主表
全部选中执行一下,什么都不用改,你把换行和空格弄好了就行
比如你要按name 进行group by ,然后按name排序
select row_number() over (order by name) rn,name,sum(计算值) from 表名 group by name创建表
create table ccc(a int,
b int);
insert into ccc values (10,null);
insert into ccc values (20,10);
insert into ccc values (30,10);
insert into ccc values (40,20);
insert into ccc values (50,20);
insert into ccc values (60,30);
insert into ccc values (70,50);
运行
SELECT a, b, 1 || SUBSTR (SYS_CONNECT_BY_PATH (lv, '-'), 3)FROM (SELECT a, b, ROW_NUMBER () OVER (PARTITION BY b ORDER BY lv) lv
FROM (SELECT a, b, LEVEL lv
FROM ccc
START WITH b IS NULL
CONNECT BY PRIOR a = b) t) s
START WITH b IS NULL
CONNECT BY PRIOR a = b
结果
update biao t set 字段= 1 where rowid= (select 字段abc,min(rowid) from biao tt where trowid = ttrowid group by 字段abc ),
set 字段= 2 where rowid= (select 字段abc,max (rowid) from biao tt where trowid = ttrowid group by 字段abc ),
set 字段= 2 where 字段 is null
判断:查找表触发器,sequence是否有效。
原理:ORACLE通过使用触发器完成自动生成序列号的工作,这一点相较ACCESS类数据库不同。程序员通常先建立序列sequence,然后创建基于表的触发器以自动产生编号。
以创建sequence tb1_seq为例:
create sequence tb1_seq
minvalue 1
maxvalue 999999999999999999999999999
start with 1 --从1开始
increment by 1 --自增1
nocache; --不循环
创建一个行级触发器,当插入表tb1时,触发自动编号
create or replace trigger tb1_trigger
before insert on tb1 --插入前取当前序列值的下一个
for each row
begin
select tb1_seqnextval into :newId from dual;
end ;
PS:tb1_seqnextval可直接在存储过程中使用
以上就是关于oracle查询结果显示序列号问题全部的内容,包括:oracle查询结果显示序列号问题、oracle添加序号、oracle 树 查询 带分级序号等相关内容解答,如果想了解更多相关内容,可以关注我们,你们的支持是我们更新的动力!
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)