一. 函数使用
1.1 listagg聚合函数
语法:
listagg(
col_XXX
,YYY
) WITHIN GROUP( ORDER BYZZZ
)。
参数解释:
①.col_XXX
: 需要聚合的字段
②.YYY
: 字段间分割符, 如’’, ‘,’ ,’#’
③.ZZZ
: 必填字段, 表示根据zzz字段来进行排序, 以保证col_XXX 能够按照业务指定的顺序进行字符串拼装.
可以配合 GROUP BY 进行聚合查询,也可以配合 PARTITION BY 进行聚合查询。
综合样例清单:
实现以下功能:
- 1.从虚表dual中构建一张存放在内存中的指标表, 该表有5个字段, 分别为业务指标名, 指标1~指标4;
- 2.设置表头;
- 3.从内存表 table_test_1 中读取所有的内容行, 作为html table元素的内容.
- 4.使用listagg 聚合所有的内容字段(table_content, 分割字符设置为空字符)
WITH table_test_1 AS (
SELECT
'指标名_1' AS idx_name,
11 AS col_1,
11.1 AS col_2,
11 AS col_3,
11.2 AS col_4
FROM
dual
UNION ALL
SELECT
'指标名_2' AS idx_name,
22 AS col_1,
22.2 AS col_2,
22 AS col_3,
22.3 AS col_4
FROM
dual
UNION ALL
SELECT
'指标名_3' AS idx_name,
33 AS col_1,
33.3 AS col_2,
33 AS col_3,
33.4 AS col_4
FROM
dual
UNION ALL
SELECT
'指标名_4' AS idx_name,
44 AS col_1,
44.4 AS col_2,
444 AS col_3,
44.5 AS col_4
FROM
dual
) --select * from table_test_1; --测试如上内存中的sql拼装是否正确. 测试完后请删除该select语句.
SELECT
listagg (
table_test_2.table_content, --希望聚合的字段
'' --聚合字段间的连接字符
) WITHIN GROUP (ORDER BY sort_no) AS assembly_string --装配完后的字符串
FROM
(
SELECT
'<table border="solid" cellspacing="0" cellpadding="10" frame="solid" rules="solid">' AS table_content,
1 AS sort_no
FROM
dual
UNION ALL
SELECT
'<tr align="center" valign="center" bgcolor="red"><th>Proj</th><th>time</th><th>indicator_1</th><th>indicator_2</th><th>indicator_3</th><th>indicator_4</th></tr>' AS table_content,
2 AS sort_no
FROM
dual
UNION ALL
SELECT
'<tr><td align="left">' || table_test_1.idx_name || '</td><td align="center">' || TO_CHAR (SYSDATE, 'yyyy-MM-dd') || '</td><td align="right">' || ROUND (table_test_1.col_1, 0) || '</td><td align="right">' || ROUND (table_test_1.col_2, 0) || '</td><td align="right">' || ROUND (table_test_1.col_3, 0) || '</td><td align="right">' || ROUND (table_test_1.col_4, 0) || '</td></tr>' AS table_content,
3 AS sort_no
FROM
table_test_1
UNION ALL
SELECT
'</table>' AS table_content,
4 AS sort_no
FROM
dual
) table_test_2;
查询结果如下:
<table border="solid" cellspacing="0" cellpadding="10" frame="solid" rules="solid"><tr align="center" valign="center" bgcolor="red"><th>Proj</th><th>time</th><th>indicator_1</th><th>indicator_2</th><th>indicator_3</th><th>indicator_4</th></tr><tr><td align="left">指标_1</td><td align="center">2020-04-27</td><td align="right">11</td><td align="right">11</td><td align="right">11</td><td align="right">11</td></tr><tr><td align="left">指标_2</td><td align="center">2020-04-27</td><td align="right">22</td><td align="right">22</td><td align="right">22</td><td align="right">22</td></tr><tr><td align="left">指标_3</td><td align="center">2020-04-27</td><td align="right">33</td><td align="right">33</td><td align="right">33</td><td align="right">33</td></tr><tr><td align="left">指标_4</td><td align="center">2020-04-27</td><td align="right">44</td><td align="right">44</td><td align="right">444</td><td align="right">45</td></tr></table>
1.2 with as构建临时表
with as 也叫做子查询部分,首先定义一个sql片段,该sql片段会被整个sql语句所用到,为了让sql语句的可读性更高些,作为提供数据的部分,也常常用在union [all]等集合操作中。
with as就类似于一个视图或临时表,可以用来存储一部分的sql语句作为别名,不同的是with as 属于一次性的,而且必须要和其他sql一起使用才可以!
其最大的好处就是适当的提高代码可读性,而且如果with子句在后面要多次使用到,这可以大大的简化SQL;更重要的是:一次分析,多次使用,这也是为什么会提供性能的地方,达到了“少读”的目标。
语法:
WITH t1 AS (
SELECT *FROM student
),
t2 AS (
SELECT *FROM course
) SELECT * FROM t1, t2
!!! Notice:
- 这里必须要整体作为一条sql查询,即with as语句后不能加分号,不然会报错。
- with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割;最后一个with 子句与下面的查询之间不能有逗号,只通过右括号分割,with 子句的查询必须用括号括起来.
- 如果定义了with子句,但其后没有跟select查询,则会报错!select 语句中有无使用WITH后的表名是无所谓的, 只要有select语句就不会报错.
- 前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句!
***** 样例代码 ****:
with t1 as (select * from student_course),
t2 as (select t1.student_id from student)
select * from t2