带有子查询的 Oracle PIVOT 子句示例

Example of an Oracle PIVOT clause with subquery(带有子查询的 Oracle PIVOT 子句示例)

问题描述

Oracle 的PIVOT 子句的定义指定可以在 IN 子句中定义子查询.我想象的一个虚构的例子是这样的

Oracle's definition of the PIVOT clause specifies that there is a possibility to define a subquery in the IN clause. A fictional example of what I would imagine this to be is this

... PIVOT (AVG(salary) FOR (company) IN (SELECT DISTINCT company FROM companies))

然而，我得到一个 ORA-00936: Missing expression 错误.不幸的是，这个新的 PIVOT 子句的错误通常是相当神秘的.谁能给我一个很好的例子，说明如何在 PIVOT 子句的 IN 子句中使用子查询?

With that, however, I get an ORA-00936: Missing expression error. Unfortunately, errors from this new PIVOT clause are usually rather cryptic. Can anyone give me a good example of how a subquery can be used in the IN clause of the PIVOT clause?

推荐答案

显然，我懒得读到文档的最后......再往下，文档说明:

Apparently, I was too lazy to read to the end of the documentation... Further down, the documentation states:

子查询 子查询仅与 XML 关键字结合使用.当您指定子查询时，子查询找到的所有值都用于透视.[...]

subquery A subquery is used only in conjunction with the XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting. [...]

这会起作用

PIVOT XML (AVG(salary) FOR (company) IN (SELECT DISTINCT company FROM companies))

查看完整文档

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFAFIE

这篇关于带有子查询的 Oracle PIVOT 子句示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！