Mogdb - 关于pivot/unpivot写法整理汇总

Oracle数据库使用pivot(透视)和unpivot(反透视)语法，可以非常便捷的实现数据表中一列或多列的数据行列转换。Mogdb 3.0.2版本目前尚不支持类似的关键字，但是可以通过改写SQL实现相同的业务逻辑。

透视(pivot)

oracle示例：

with temp as(
select '四川省' nation ,'成都市' city,'第一' ranking from dual union all
select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all
select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all
select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all
select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all
select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all
select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual
)
select * from (select nation,city,ranking from temp) pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四));
复制

mogdb示例：

create or replace synonym public.dual for pg_catalog.sys_dummy;

with t as (
select '张飞' name,	'2001-2002' term, '微积分'   as class,  90 score from dual union all
select '张飞' name,	'2001-2002' term, '线性代数' as class,	88 score from dual union all
select '张飞' name,	'2001-2002' term, '数据结构' as class,	85 score from dual union all
select '张飞' name,	'2001-2002' term, '操作系统' as class,	70 score from dual union all
select '关羽' name,	'2001-2002' term, '微积分'   as class,	80 score from dual union all
select '关羽' name,	'2001-2002' term, '线性代数' as class,	33 score from dual union all
select '关羽' name,	'2001-2002' term, '数据结构' as class,	75 score from dual union all
select '关羽' name,	'2001-2002' term, '操作系统' as class,	90 score from dual union all
select '刘备' name,	'2001-2002' term, '微积分'   as class,	70 score from dual union all
select '刘备' name,	'2001-2002' term, '线性代数' as class,	45 score from dual union all
select '刘备' name,	'2001-2002' term, '数据结构' as class,	65 score from dual union all
select '刘备' name,	'2001-2002' term, '操作系统' as class,	95 score from dual )
select name,term,
       max(case class when  '微积分' then score end)   "微积分",
       max(case class when  '线性代数' then score end) "线性代数",
       max(case class when  '数据结构' then score end) "数据结构",
       max(case class when  '操作系统' then score end) "操作系统"
   from t group by name,term;
复制

反透视(unpivot)

oracle示例：

with temp as(
select '四川省' nation ,'成都市' 第一,'绵阳市' 第二,'德阳市' 第三,'宜宾市' 第四  from dual union all
select '湖北省' nation ,'武汉市' 第一,'宜昌市' 第二,'襄阳市' 第三,'' 第四   from dual
)
select nation,name,title from temp
unpivot
(name for title in (第一,第二,第三,第四)) t;
复制

mogdb数据库不支持unpivot，对应的postgres示例：

with temp as(
SELECT '张飞' STU_NAME, '2001-2002' TERM, '90' 微积分, '88' 线性代数, '85' 数据结构, '70' 操作系统 union all
SELECT '关羽' STU_NAME, '2001-2002' TERM, '80' 微积分, '33' 线性代数, '75' 数据结构, '90' 操作系统 union all
SELECT '刘备' STU_NAME, '2001-2002' TERM, '70' 微积分, '45' 线性代数, '65' 数据结构, '95' 操作系统
)
select stu_name, 
       term, 
       key as class, 
       value::int as score
   from (select stu_name, term, row_to_json(t.*) as line from temp as t) as r 
     join lateral json_each_text(r.line) 
     on (key ~* '[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]');   --匹配所有中文字符集
复制