带有子查询的Oracle PIVOT子句的示例 [英] Example of an Oracle PIVOT clause with subquery
问题描述
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子句的示例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!