如何调整SQL以按日期排序列？

Question

我正在使用Oracle Apex 23.2，并搭配一个Oracle 21.c数据库。我有一个应用程序用于跟踪事件的出席情况，该程序使用了三个表。

**VOL_ATTENDANCE**
| Prim_Key | Event_Fkey | Contact_Fkey | Attendance-Type |
|----------|------------|--------------|----------------|
| 1        | 1          | 258          | In-Person       |
| 2        | 1          | 801          | In-Person       |
| 3        | 1          | 234          | Zoom            |
| 4        | 2          | 258          | In-Person       |
| 5        | 2          | 234          | Zoom            |

**VOL_CONTACT**
| Prim_Key | Last_Name | First_Name |
|----------|-----------|------------|
| 234      | Potter    | Harry      |
| 258      | Weasley   | Ron        |
| 801      | Granger   | Hermione   |
| 500      | Weasley   | Ginny      |

**VOL_EVENT**
| Prim_Key | Event_Name | Event_Date  |
|----------|------------|-------------|
| 1        | Meeting    | 30-MAR-23   |
| 2        | Meeting    | 18-APR-23   |

我想得到以下格式的报告（按姓氏行排序，列按日期排列，用“I”和“Z”分别代表In-Person和Zoom出席）：

期望报告

|            | Mar 30, 2023 | Apr 18, 2023 |
|------------|--------------|--------------|
| Hermione Granger | I           |              |
| Harry Potter     | Z           | Z            |
| Ginny Weasley    |             |              |
| Ron Weasley      | I           | I            |

当我运行以下SQL时，我得到了一个标准的表格报告：

Select
   vc.first_name,
   vc.last_name,
   va.attendance_type,
   ve.event_date
From
   VOL_CONTACT vc,
   VOL_EVENT ve,
   VOL_ATTENDANCE va
Where
   va.contact_fkey = vc.prim_key
And va.event_fkey = ve.prim_key
Order by
   ve.event_date desc,
   vc.last_name,
   vc.first_name;

我不清楚如何调整上述SQL（或PL/SQL）来获取上文描述的期望报告。

Joe · Answer

首先，你需要应用的概念是PIVOT操作（参见《管理透视报表》[Managing Pivot Reports]）。根据你提供的数据，以下是示例代码（请注意，这是SQL而非PL/SQL）：

SELECT 
    first_name,
    last_name,
    LEFT(ISNULL([30-MAR-23],''),1) AS "Mar 30, 2023",
    LEFT(ISNULL([18-APR-23],''),1) AS "Apr 18, 2023"
FROM
(
    SELECT
        vc.first_name,
        vc.last_name,
        va.attendance_type,
        TO_CHAR(ve.event_date, 'DD-MON-YY') AS event_date
    FROM
        VOL_CONTACT vc
    LEFT OUTER JOIN
        VOL_ATTENDANCE va ON va.contact_fkey = vc.prim_key
    LEFT OUTER JOIN 
        VOL_EVENT ve ON va.event_fkey = ve.prim_key
) src
PIVOT
(
    MAX(attendance_type) FOR event_date IN ('30-MAR-23', '18-APR-23')
) pvt
ORDER BY 
    last_name,
    first_name;

然而，这种方法依赖于你在编写SQL时已知VOL_EVENT中的日期。因此，接下来你需要掌握的概念是“动态SQL”或“动态查询”，在Oracle中，这通常通过EXECUTE IMMEDIATE语句实现（参考文档：Oracle 动态SQL执行）。

Lightness Races in Orbit · Answer

在SQL（不仅仅是Oracle）中，当你在查询中使用列时，需要知道列的数量和标识符；因此，在不预先知道列的情况下，Oracle并不直接支持动态透视操作。

如果你有已知且固定的日期需要进行透视处理（例如 2023-03-30 和 2023-04-18），可以使用以下查询：

SELECT *
FROM (
  SELECT vc.first_name || ' ' || vc.last_name AS name,
         SUBSTR(va.attendance_type, 1, 1) AS attendance_type,
         ve.event_date
  FROM   VOL_CONTACT vc
         INNER JOIN VOL_ATTENDANCE va
         ON va.contact_fkey = vc.prim_key
         INNER JOIN VOL_EVENT ve
         ON va.event_fkey = ve.prim_key
)
PIVOT (
  MAX(attendance_type) FOR event_date IN (
    DATE '2023-03-30' AS "30 Mar, 2023",
    DATE '2023-04-18' AS "18 Apr, 2023"
  )
)
ORDER BY name;

对于以下样例数据：

-- 创建联系人表
CREATE TABLE VOL_CONTACT (Prim_Key, Last_Name, First_Name) AS
SELECT 234, 'Potter',  'Harry' FROM DUAL UNION ALL
SELECT 258, 'Weasley', 'Ron' FROM DUAL UNION ALL
SELECT 801, 'Granger', 'Hermione' FROM DUAL UNION ALL
SELECT 500, 'Weasley', 'Ginny' FROM DUAL;

-- 创建活动表
CREATE TABLE VOL_EVENT (Prim_Key, Event_Name, Event_Date) AS
SELECT 1, 'Meeting', DATE '2023-03-30' FROM DUAL UNION ALL
SELECT 2, 'Meeting', DATE '2023-04-18' FROM DUAL;

-- 创建出席记录表
CREATE TABLE VOL_ATTENDANCE (Prim_Key, Event_Fkey, Contact_Fkey, Attendance_Type) AS
SELECT 1, 1, 258, 'In-Person' FROM DUAL UNION ALL
SELECT 2, 1, 801, 'In-Person' FROM DUAL UNION ALL
SELECT 3, 1, 234, 'Zoom' FROM DUAL UNION ALL
SELECT 4, 2, 258, 'In-Person' FROM DUAL UNION ALL
SELECT 5, 2, 234, 'Zoom' FROM DUAL;

上述查询会输出以下结果：

| 名称          | 30 Mar, 2023 | 18 Apr, 2023 |
|--------------|--------------|--------------|
| Harry Potter | Z            | Z            |
| Hermione Granger | I            | NULL         |
| Ron Weasley  | I            | I            |

如果不知道要返回多少个（或哪些）日期，则最好按行形式返回数据（而不是列形式）：

SELECT vc.first_name || ' ' || vc.last_name AS name,
       SUBSTR(va.attendance_type, 1, 1) AS attendance_type,
       ve.event_date
FROM   VOL_CONTACT vc
       INNER JOIN VOL_ATTENDANCE va
       ON va.contact_fkey = vc.prim_key
       INNER JOIN VOL_EVENT ve
       ON va.event_fkey = ve.prim_key
ORDER BY event_date, name;

此查询会输出按日期和姓名排序的行数据：

| 名称          | 出席类型 | 活动日期         |
|--------------|----------|-----------------|
| Harry Potter | Z        | 2023-03-30 00:00:00 |
| Hermione Granger | I      | 2023-03-30 00:00:00 |
| Ron Weasley  | I        | 2023-03-30 00:00:00 |
| Harry Potter | Z        | 2023-04-18 00:00:00 |
| Ron Weasley  | I        | 2023-04-18 00:00:00 |

否则，您可能需要尝试以下方法：

使用动态SQL来创建透视查询；
或者在Oracle Apex经典报告中采用转置行到列的方式呈现数据。

样例数据的在线演示可在此链接查看：fiddle。