如何在枢轴块中调用聚合函数的结果

Question

以下示例仅用于了解为什么我要做自己的工作。但是第二个例子最好理解问题。

我想创建一个表。每列表示应该在表中的值。

WITH
    FUNCTION f(arg INTEGER, colum_name VARCHAR2)
        RETURN VARCHAR2
    IS
    BEGIN
        IF arg = 0
        THEN
            RETURN 'this column doesn''t exist';
        ELSE
            RETURN colum_name;
        END IF;
    END;
    t (a) AS (SELECT COLUMN_VALUE FROM sys.odcivarchar2list ('a', 'b', 'd'))
SELECT *
  FROM (SELECT a FROM t)
       PIVOT (f(COUNT (*),a)
             FOR a
             IN ('a', 'b', 'c', 'd'));

它应该返回的内容：

a	b	c	d
a	b	此列不存在	d

，因为我需要列的名称，所以我无法制作一个子查询，该子查询占据了聚合功能的结果，并在主查询。

---

现在是第二个示例：

第一个查询将每行与值1，然后将值2计数

SELECT * FROM (SELECT 1 a FROM DUAL) PIVOT (COUNT (*)       FOR a IN (1, 2));

。它正在工作。

但是此查询不起作用。计数（*）+1不被视为促进功能子

SELECT *
  FROM (SELECT 1 a FROM DUAL) PIVOT (COUNT (*)      +   1 FOR a IN (1, 2));

[错误]执行（40：53）：ORA-56902：期待枢轴操作中的聚合功能

我不能这样做，因为：第一个示例中的BOLD文本。

---

测试代码：

-----------------------------------------------------------------------------------------------》（MTO）得到答案后添加了：

WITH
  FUNCTION f(
    arg        IN INTEGER,
    colum_name IN VARCHAR2
  ) RETURN VARCHAR2
  IS
  BEGIN
    IF arg = 0 THEN
      RETURN 'this column doesn''t exist';
    ELSE
      RETURN colum_name;
    END IF;
  END;
  t (a) AS (
    SELECT COLUMN_VALUE
    FROM   sys.odcivarchar2list ('a', 'b', 'd')
  )
SELECT f(a, 'a') AS a,
       f(b, 'b') AS b,
       f(c, 'c') AS c,
       f(d, 'd') AS d
FROM   t
PIVOT (
  COUNT (*)
  FOR a IN (
    'a' AS a,
    'b' AS b,
    'c' AS c,
    'd' AS d
  )
);

它正在工作，但是：

• 但是实际情况，我有很多专栏，我想避免将它们全部重写。
• 我想避免键入错误。编写2个时间相同的名称易于打字错误。

Answer 1

它将不在 pivot 内部工作必须调用 pivot 的功能：

WITH
  FUNCTION f(
    arg        IN INTEGER,
    colum_name IN VARCHAR2
  ) RETURN VARCHAR2
  IS
  BEGIN
    IF arg = 0 THEN
      RETURN 'this column doesn''t exist';
    ELSE
      RETURN colum_name;
    END IF;
  END;
  t (a) AS (
    SELECT COLUMN_VALUE
    FROM   sys.odcivarchar2list ('a', 'b', 'd')
  )
SELECT f(a, 'a') AS a,
       f(b, 'b') AS b,
       f(c, 'c') AS c,
       f(d, 'd') AS d
FROM   t
PIVOT (
  COUNT (*)
  FOR a IN (
    'a' AS a,
    'b' AS b,
    'c' AS c,
    'd' AS d
  )
);

输出：

a	b	c	d
a	b	此列不存在	d

---

解决评论：

因为我需要列的名称，所以我无法制作一个子查询，该子查询符合聚合函数的结果并在主查询中使用F。

是的，你可以；正如上面显示的那样。

SQL语句（在任何方言，不仅是Oracle）必须具有已知的固定数量输出列，然后才能编译。因此，您可以知道将从 pivot 语句输出的所有列（ for 条款中的每个项目一个未在 pivot 子句中使用），可以在您想要的那些列上调用该功能。

我想避免键入错误。写2个时间相同的名字易于打字错误。

编写代码后，对代码进行了评论，并使用测试检查其正常工作。

---

您的第二个查询可以使用完全相同的原理来固定（除了您需要引用的标识符，而没有明确提供标识符，枢轴创建的标识符以数字开始，并且必须引用）：

SELECT "1" + 1 AS "1",
       "2" + 1 AS "2"
FROM   (SELECT 1 a FROM DUAL)
PIVOT (COUNT (*) FOR a IN (1, 2));

哪些输出：输出：

1	2
2	1

db＆lt;＆gt; fiddle

Answer 2

该解决方案使得可以在表中获取元素的名称，而无需编写这些元素2次。
表格中不在表中的元素具有值null。如果要更改使用空物以外的其他内容，则必须将此查询用作子查询并使用函数NVL。但是您必须再次重写所有列的名称。
该解决方案适合我，但并不完全令人满意...

with
    t (a) AS (SELECT COLUMN_VALUE FROM sys.odcivarchar2list ('a', 'b', 'd'))
SELECT *
  FROM t
       ANY_VALUE(a)
             FOR a
             IN ('a', 'b', 'c', 'd'));