在使用统计sql或者帆软报表中特殊的需求很多.

行专列

decode函数

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

case when

select t.user_name,
    sum(case when t.course = '语文' then score else 0 end) as chinese,
    sum(case when t.course = '数学' then score else 0 end) as math,
    sum(case when t.course = '英语' then score else 0 end) as english
from test_tb_grade t
group by t.user_name
order by t.user_name

pivot语法

--pivot语法：pivot(任一聚合函数 for 需转列的值所在列名 in (需转为列名的值))；
select *
  from r2c
pivot(max(score)                 --转换行里需要显示的数据，一般是数字的那列
   for subject in('语文' as "语文", '数学' as "数学", '英语' as "英语"));
   --重新定义列名

列转行

select 字段 from 数据集
unpivot（自定义列名/*列的值*/ for 自定义列名 in（列名））

select stuname, coursename ,score from
score_copy  t
unpivot
(score for coursename in (英语,数学,语文))

查询树tree

start with connect by prior递归

//查询所有子节点
SELECT *
FROM district
START WITH NAME ='巴中市'
CONNECT BY PRIOR ID=parent_id

//查询所有父节点
SELECT *
FROM district
START WITH NAME ='平昌县'
CONNECT BY PRIOR parent_id=ID //只需要交换 id 与parent_id的位置即可

//查询指定节点的，根节点
SELECT d.*,
connect_by_root(d.id),
connect_by_root(NAME)
FROM district d
WHERE NAME='平昌县'
START WITH d.parent_id=1    --d.parent_id is null 结果为四川省
CONNECT BY PRIOR d.ID=d.parent_id

//查询巴中市下行政组织递归路径
SELECT ID,parent_id,NAME,
sys_connect_by_path(NAME,'->') namepath,
LEVEL
FROM district 
START WITH NAME='巴中市'
CONNECT BY PRIOR ID=parent_id

SELECT
            tab.itemid as "indexTypeId",
            tab.itemname as "indexTypeName"
        FROM
            (
                SELECT
                    itemid ,
                    itemname ,
                    PARENT_ITEMID
                FROM
                    t_ems_rm_index_item
                UNION ALL
                SELECT
                    index_type_id ,
                    index_type_name ,
                    'root' PARENT_ITEMID
                FROM
                    T_ems_RM_INDEX_TYPE )tab
        WHERE
            level = 3 //指定递归的层数
            START WITH tab.PARENT_ITEMID = 'root' CONNECT BY tab.PARENT_ITEMID = prior tab.itemid

with as

//with递归子类
WITH t (ID ,parent_id,NAME) --要有列名
AS(
SELECT ID ,parent_id,NAME FROM district WHERE NAME='巴中市'
UNION ALL
SELECT d.ID ,d.parent_id,d.NAME FROM t,district d --要指定表和列表，
WHERE t.id=d.parent_id
)
SELECT * FROM t;

//递归父类
WITH t (ID ,parent_id,NAME) --要有表
AS(
SELECT ID ,parent_id,NAME FROM district WHERE NAME='通江县'
UNION ALL
SELECT d.ID ,d.parent_id,d.NAME FROM t,district d --要指定表和列表，
WHERE t.parent_id=d.id
)
SELECT * FROM t;

查询每条最新状态记录partition BY

SELECT
    c.whse_id,
    c.levels,
    rn
FROM
( SELECT
    --记录序号,  根据s1.WHSE_ID,对LEVEL_DATE进行倒序排列
--按s1.WHSE_ID分组  按s1.LEVEL_DATE DESC排序
    row_number() over(partition BY s1.WHSE_ID ORDER BY s1.LEVEL_DATE DESC ) rn ,
    s1.*
  FROM T_SC_WAREHOUSE_level s1 
  --rn = 1 只拿WHSE_ID中倒序第一条的数据,即最新数据
) c where rn = 1

select * from 
(select form_id from formid where user_id = '28be9d85d0764c518ca074832fbad1b6' order by insert_time desc)
 where rownum = 1

常用的函数：

row_number() over(partition by … order by …)

rank() over(partition by … order by …)

dense_rank() over(partition by … order by …)

count() over(partition by … order by …) 求分组后的总数

max() over(partition by … order by …) 求分组后的最大值

min() over(partition by … order by …) 求分组后的最小值

sum() over(partition by … order by …) 求分组后的总和

avg() over(partition by … order by …) 求分组后的平均值

first_value() over(partition by … order by …) 求分组后的第一个值

last_value() over(partition by … order by …) 求分组后的最后一个值

lag() over(partition by … order by …) 取出分组后前n行数据

lead() over(partition by … order by …) 取出分组后后n行数据