ORACLE1.15-构造列思想(行转列),decode,case,sgin,pivot四大金刚
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')
)
