随机生成16位流水号；
SELECT
TO_CHAR(SYSDATE,'YYYYMMDD')||LPAD(TO_CHAR(PRODUCT_ORDER_SEQUENCE.NEXTVAL), 7, '0')
FROM DUAL;

create sequence SEQ_SUBSID
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 20
order;

SELECT dbms_random.value(1000,10000) FROM dual;
select substr(sys_guid(),1,2) from dual;

SELECT trunc(dbms_random.value(1,12)) FROM dual;

随机生成时间
trunc(sysdate-trunc(dbms_random.value(6000, 8345)))

行列转换
select t.user_name,
sum(decode(t.course, '语文', score,null)) as CHINESE,
sum(decode(t.course, '数学', score,null)) as MATH,
sum(decode(t.course, '英语', score,null)) as ENGLISH
from test_tb_grade t
group by t.user_name
order by t.user_name ;

select t2.SCORE_GP,
sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE,
sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH,
sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH
from (
select t.course,
         case when t.score <60 then '00-60'
              when t.score >=60 and t.score <80 then '60-80'
              when t.score >=80 then '80-100' end as SCORE_GP,
         count(t.score) as COUNTNUM
FROM test_tb_grade t
group by t.course,
        case when t.score <60 then '00-60'
              when t.score >=60 and t.score <80 then '60-80'
              when t.score >=80 then '80-100' end
order by t.course ) t2
group by t2.SCORE_GP
order by t2.SCORE_GP

create table wlbtest1(
   username varchar2(2),
   deptID number
);

create table wlbtest2(
   deptName varchar2(10),
   deptID number
);

insert into wlbtest1 values ('a', 1);
insert into wlbtest1 values ('b', 1);
insert into wlbtest1 values ('c', 1);
insert into wlbtest1 values ('d', 2);
insert into wlbtest1 values ('e', 2);
insert into wlbtest1 values ('f', 2);
insert into wlbtest2 values ( '部门1' ,1);
insert into wlbtest2 values ( '部门2', 2);

select deptID, wm_concat(username) username,'部门'||deptID deptName from wlbtest1 group by deptID

ITPub博客

学习之应用基础(1)

最新文章