Oracle构造列

CREATE TABLE t_user3(
   id NUMBER PRIMARY KEY,
   user_name VARCHAR2(20),
   birt_date date    -->>java.util.Date(包含日期+时间)
)
INSERT INTO t_user3 VALUES (1,'小明',to_date('1995-10-15','yyyy-MM-dd'));
INSERT INTO t_user3 VALUES (2,'小黄',to_date('1985-09-05','yyyy-MM-dd'));
INSERT INTO t_user3 VALUES (3,'小军',to_date('1987-07-05','yyyy-MM-dd'));
INSERT INTO t_user3 VALUES (4,'大林',to_date('1967-09-15','yyyy-MM-dd'));
INSERT INTO t_user3 VALUES (5,'彤彤',to_date('2003-09-15','yyyy-MM-dd'));
commit

select * from t_user3

action
service -->>遍历 userList (age)
            --java的日历类有强大的时间求算能力
dao    -->>不对age
entity
   User
      int id;
      String userName;
      java.util.Date birtDate;
      int age;

-->>对于不在java中的程序，如果要算日期
--->>只能在sql运算
-- 1.算年龄，增加多1个字段
select 
  id,
  user_name,
  --  birt_date,
  trunc(months_between(sysdate,birt_date)/12) age
from 
  t_user3

-- 1.看单一值 （可以使用decode去取代）
-- 2.看范围
case when
  
-- 2.再增加一个年龄分层
select 
  id,
  user_name,
  age,
  case
    when age<19 then '未成年人'
    when age<25 then '年青人'
    when age<40 then '中青'
    when age<50 then '中年人'
    when age<60 then '中老年人'
    else '老人'
  end ageLevel
from (
  select 
    id,
    user_name,
    --  birt_date,
    trunc(months_between(sysdate,birt_date)/12) age
  from 
    t_user3
) t_user3_age

--3.对年龄分层进行统计
select agelevel,count(1) kk from (
select 
  id,
  user_name,
  age,
  case
    when age<19 then '未成年人'
    when age<25 then '年青人'
    when age<40 then '中青'
    when age<50 then '中年人'
    when age<60 then '中老年人'
    else '老人'
  end ageLevel
from (
  select 
    id,
    user_name,
    --  birt_date,
    trunc(months_between(sysdate,birt_date)/12) age
  from 
    t_user3
) t_user3_age
) t_user3_age_level
group by agelevel
order by kk desc


select * from t_user3

-- 2个非常重要的函数sign decode 
-- sign 根据参数值，0，正数，负数，分别返回0,1,-1
select 5-2 from dual
select sign(5-2) from dual  -->>  1
select sign(2-5) from dual  -->>  -1
select sign(5-5) from dual  -->>  0
--decode 
-- 1.返回值，（不支持范围，只支持值,但是可以配合sign实现）
--           (完全用case when 取代)
-- 2.返回一个查询结果 （难点）
--语法结构
--分解说明
select decode(888,  -- 参数值
15,'abc',          -- if 1
16,'kkk',          -- if 2
17,'ttt',          -- if 3
'iii'              -- else
) 
from dual

--真实写法
select decode(888,15,'abc',16,'kkk',17,'ttt','iii') from dual

--- 把查询结果创建除1张表
create table t_user4 as
  select 
    id,
    user_name,
    --  birt_date,
    trunc(months_between(sysdate,birt_date)/12) age
  from 
    t_user3

select 
  id,
  user_name,
  age,
  decode( sign(18-age),1,'是','否') 是否未成年
from t_user4

-------------------------
select 
  id,
  user_name,
  age,
  decode( 1,sign(18-age),'未成年人',sign(25-age),'年青人',sign(40-age),'中青',sign(50-age),'中年人',sign(60-age),'中老年人','老人') ageLevel
from t_user4

-- 行转列的应用1（decode）
-- 常考失分题目
CREATE TABLE t_user_score(
   id NUMBER PRIMARY KEY,
   user_name VARCHAR2(20),
   ke VARCHAR2(20),
   score number
);

insert into t_user_score values(1,'小黄','struts',80);
insert into t_user_score values(2,'小黄','spring',40);
insert into t_user_score values(3,'小黄','hibernate',100);
insert into t_user_score values(4,'小黄','oracle',89);
insert into t_user_score values(5,'小强','struts',79);
insert into t_user_score values(6,'小强','spring',38);
insert into t_user_score values(7,'小强','hibernate',80);
insert into t_user_score values(8,'小强','oracle',39);
commit

select * from t_user_score
select * from t_user_score order by score desc

select 
  user_name,
  sum(score) 总分,
  avg(score) 平均分
from t_user_score group by user_name
order by 总分 desc

select 
   id,
   user_name,
   ke,
   score,
   -- 构造列
   decode(ke,'struts',score,0) struts_score,
   decode(ke,'spring',score,0) spring_score,
   decode(ke,'hibernate',score,0) hibernate_score,
   decode(ke,'oracle',score,0) oracle_score
from t_user_score
-----------------------


select kkk.*，rownum 排名 from(
  select 
     user_name,sum(score) 总分,avg(score) 平均分,
     -- 构造列
     sum(decode(ke,'struts',score,0)) struts,
     sum(decode(ke,'spring',score,0)) spring,
     sum(decode(ke,'hibernate',score,0)) hibernate,
     sum(decode(ke,'oracle',score,0)) oracle
  from t_user_score
  group by user_name
  order by 总分 desc
) kkk

-- 行转列的应用2（oracle 11g 提供pivot行转列函数）
--确保你的数据库是11g以上版本
select * from v$version

select * from t_user_score
--第1步，查看有多少门课程（转回多少列）
select distinct ke from t_user_score
-- 查询的结果
struts,hibernate,spring,oracle
--第2步,通过pivot对4门课程进行行转列
select * from (
  select user_name,ke,score from t_user_score
)
pivot(
    sum(score)
    for ke in ('struts','hibernate','spring','oracle','linux')
)