Lightdb-A 兼容oracle unpivot和pivot

最新推荐文章于 2024-10-02 11:52:29 发布

觅含

最新推荐文章于 2024-10-02 11:52:29 发布

阅读量69

点赞数

文章标签： oracle 数学建模数据库

本文链接：https://blog.csdn.net/weixin_44031114/article/details/131007022

版权

前言

Lightdb-A 支持行转列、列转行功能：
pivot支持的语法如下：
pivot_clause

unpivot支持的语法如下：

pivot和unpivot支持项测试

pivot测试

create table hs_pivot(name varchar(40),course varchar(100),score int);
insert into hs_pivot values(‘zhangsan’,‘chinese’,90);
insert into hs_pivot values(‘zhangsan’,‘math’,100);
insert into hs_pivot values(‘lisi’,‘chinese’,90);
insert into hs_pivot values(‘lisi’,‘math’,88);

select * from hs_pivot pivot (sum(score) for course in(‘chinese’ as er,‘math’ as wr));
name | er | wr
----------±—±----
lisi | 90 | 88
zhangsan | 90 | 100
(2 rows)

select * from hs_pivot pivot (sum(score) for course in(‘chinese’ as a, ‘math’ as b )) c where c.a =90;
name | a | b
----------±—±----
zhangsan | 90 | 100
lisi | 90 | 88
(2 rows)

unpivot测试

不带include nulls 和 exclude nulls

create table hs_unpivot(name varchar(40),chinese int,math int);
insert into hs_unpivot values(‘zhangsan’,90,100);
insert into hs_unpivot values(‘lisi’,88,99);

include nulls 和 exclude nulls

CREATE TABLE test_grades_2(
姓名 VARCHAR(20),
语文 INT DEFAULT NULL,
数学 INT DEFAULT NULL,
英语 INT DEFAULT NULL
);
INSERT INTO test_grades_2 VALUES(‘张三’, 88, 98, 76);
INSERT INTO test_grades_2 VALUES(‘李四’, 74, 93, 96);
INSERT INTO test_grades_2 VALUES(‘王五’, 97, 77, 92);
INSERT INTO test_grades_2 VALUES(‘周六’, 32, NULL, 45);
INSERT INTO test_grades_2 VALUES(‘吴七’, NULL, 70, 66);

默认为exclude nulls

SELECT * FROM test_grades_2 unpivot (成绩 for 科目 in (语文,数学,英语)) order by 姓名;
姓名 | 科目 | 成绩
------±-----±-----
吴七 | 数学 | 70
吴七 | 英语 | 66
周六 | 语文 | 32
周六 | 英语 | 45
张三 | 语文 | 88
张三 | 数学 | 98
张三 | 英语 | 76
李四 | 语文 | 74
李四 | 数学 | 93
李四 | 英语 | 96
王五 | 语文 | 97
王五 | 数学 | 77
王五 | 英语 | 92
(13 rows)

带exclude nulls

SELECT * FROM test_grades_2 unpivot exclude nulls (成绩 for 科目 in (语文,数学,英语)) order by 姓名;
姓名 | 科目 | 成绩
------±-----±-----
吴七 | 数学 | 70
吴七 | 英语 | 66
周六 | 语文 | 32
周六 | 英语 | 45
张三 | 语文 | 88
张三 | 数学 | 98
张三 | 英语 | 76
李四 | 语文 | 74
李四 | 数学 | 93
李四 | 英语 | 96
王五 | 语文 | 97
王五 | 数学 | 77
王五 | 英语 | 92
(13 rows)

带include nulls

SELECT * FROM test_grades_2 unpivot include nulls (成绩 for 科目 in (语文,数学,英语)) order by 姓名;
姓名 | 科目 | 成绩
------±-----±-----
吴七 | 语文 |
吴七 | 数学 | 70
吴七 | 英语 | 66
周六 | 语文 | 32
周六 | 数学 |
周六 | 英语 | 45
张三 | 语文 | 88
张三 | 数学 | 98
张三 | 英语 | 76
李四 | 语文 | 74
李四 | 数学 | 93
李四 | 英语 | 96
王五 | 语文 | 97
王五 | 数学 | 77
王五 | 英语 | 92
(15 rows)

不支持情况

Pivot不支持项：

1.具有ANY的XML不支持
添加XML关键字，无法在IN子句中指定值，我们将需要使用子查询或使用关键字ANY
2.执行多个聚集函数不支持
PIVOT (
SUM(sale_amount),
COUNT(sale_amount)
FOR customer_id
IN (1, 2, 3, 4)
);

PIVOT (
SUM(sale_amount) AS sum_sales,
COUNT(sale_amount) AS count_sales
FOR customer_id
IN (1, 2, 3, 4)
);
3.按多列分组不支持
PIVOT (
SUM(sale_amount)
FOR (customer_id, prod_category)
IN (
(1, ‘furniture’) AS furn1,
(2, ‘furniture’) AS furn2,
(1, ‘electronics’) AS elec1,
(2, ‘electronics’) AS elec2
)
4.不支持in(())：
select * from test123
PIVOT (
SUM(score) as tc
FOR course
IN ());
5.不支持 ‘(’ select_with_parens opt_alias_clause opt_conversion_clause ‘)’ opt_alias_clause
6.as别名为小写，这部分需要兼容oracle大写的逻辑进行处理。
select * from hs_pivot pivot (sum(score) for course in(‘chinese’ as er,‘math’ as wr));
7.函数的参数只支持字段，不支持数字和*。

unpivot不支持项

1.unpivot_in_clause目前不支持别名；
2.不支持 ‘(’ select_with_parens opt_alias_clause opt_conversion_clause ‘)’ opt_alias_clause
3、按多列分组不支持
select *
from (select IDNUM,F1,F2,F3,E1,E2,H1,H2, null as E3,null as H3
from T5)
UnPivot((F,E,H) for sk in ((F1,E1,H1), (F2,E2,H2), (F3,E3,H3)));
4、执行多个字段不支持
select *
from (select IDNUM,F1,F2,F3,E1,E2,H1,H2, null as E3,null as H3
from T5)
UnPivot((F,E,H) for sk in ((F1,E1,H1), (F2,E2,H2), (F3,E3,H3)));