Oracle SQL：嵌套子查询，外部相同，内部不同

Question

在这种情况下，我有几个子查询，通过联合连接，每个子查询都嵌套有一个内部子查询。外部子查询彼此完全相同，而内部查询不同且唯一。

重用整个外部子查询对于读取和进行更改来说是很麻烦的，如果它们可以定义一次并重用，那将是非常有益的。所以这是一个关于创建可重用的SQL查询的问题，但是有不同的内部子查询作为html" target="_blank">参数传递。

对于我的示例，我将给出一个简化的案例，它与我的实际代码具有相同的问题。

我们正在使用Oracle SQL进行我们的项目。

假设我们有一个学校或大学的数据库，其中有表PERSON、STUDENT、GRADE和COURSE，所有这些表都通过FK关系连接在一起。

我需要运行一个收集列表的查询，对每个标准计算一次人数:

• 姓氏以字母E开头的学生人数
• 20岁以上学生人数
• 女性人数（包括但不限于学生）
• 通过"挪威语中级"课程且成绩达到B级或更高的学生人数

预期成果：

  | Description                                     | Number_of_students
1 | Last names beginning with letter "E"            |  32
2 | Older than 20 years                             | 154
3 | All female persons                              | 356 
4 | Passed "Intermediate Norwegian" with grade >= B |  12

下面是一个查询，应该满足我的需求。它由 UNION 联接的几个子查询组成，并且都有自己独特的内部查询。

代码远非精彩，但这与重点无关。真正的问题是大幅提高可读性。外部子查询具有相同的结构，可以重复使用，但内部子查询不同。

SELECT * FROM
    -- 1st entry: Number of students on the last name 'E'
    (SELECT 'Last names beginning with letter "E"' AS Description, count(*) AS Number_of_students FROM 
      FROM PERSON p, STUDENT s, GRADE g, COURSE c
        WHERE p.ID = s.PERSON_ID 
        AND s.ID = g.STUDENT_ID 
        AND g.COURSE_ID = c.ID
        -- ... other complex code here

        AND p.ID IN(
          SELECT DISTINCT p1.ID FROM PERSON p1, STUDENT s1 
              WHERE p1.ID = s1.PERSON_ID AND p1.LASTNAME LIKE 'E%'
        )
    )
UNION

-- 2nd entry: Number of students older than 20 years
    (SELECT 'Older than 20 years' AS Description, count(*) AS Number_of_students FROM 
      FROM PERSON p, STUDENT s, GRADE g, COURSE c
        WHERE p.ID = s.PERSON_ID 
        AND s.ID = g.STUDENT_ID 
        AND g.COURSE_ID = c.ID
        -- ... other complex code here

        AND p.ID IN(
          SELECT DISTINCT p2.ID FROM PERSON p2, STUDENT s2 
              WHERE p2.ID = s2.PERSON_ID AND p2.AGE > 20
        )
    )
UNION

-- 3rd entry: Number of female persons, including but not limited to students
    (SELECT 'All female persons' AS Description, count(*) AS Number_of_students FROM 
      FROM PERSON p, STUDENT s, GRADE g, COURSE c
        WHERE p.ID = s.PERSON_ID 
        AND s.ID = g.STUDENT_ID 
        AND g.COURSE_ID = c.ID
        -- ... other complex code here

        AND p.ID IN(
          SELECT DISTINCT p3.ID FROM PERSON p3 WHERE p3.GENDER = 'Female'
        )
    )
UNION

-- 4th entry: Students who passed the course "Intermediate Norwegian" with grade B or higher
    (SELECT 'Passed "Intermediate Norwegian" with grade >= B' AS Description, count(*) AS Number_of_students FROM 
      FROM PERSON p, STUDENT s, GRADE g, COURSE c
        WHERE p.ID = s.PERSON_ID 
        AND s.ID = g.STUDENT_ID 
        AND g.COURSE_ID = c.ID
        -- ... other complex code here

        AND p.ID IN(
          SELECT DISTINCT p4.ID FROM PERSON p4, STUDENT s4, GRADE g4 AND COURSE c4
            WHERE p4.ID = s4.PERSON_ID 
            AND s4.ID = g4.STUDENT_ID 
            AND g4.COURSE_ID = c4.ID
            AND (g4.GRADE = 'A' OR g4.GRADE = 'B')
            AND c4.COURSE_NAME = 'Intermediate Norwegian'
        )
    )

就像我说的，代码一点也不精彩。如果你们中的一些人对刚刚读到的内容感到畏缩，我不会感到惊讶。

例如，整个第四个可以很容易地替换为查询，其中您将整个内部查询替换为g.GRADE='A'OR'B'和c.COURSE_NAME='中级挪威语'。

但就像我说的，这不是重点。每个外部子查询具有相同的结构:

 (SELECT 'Passed "Intermediate Norwegian" with grade >= B' AS Description, count(*) AS Number_of_students FROM 
      FROM PERSON p, STUDENT s, GRADE g, COURSE c
        WHERE p.ID = s.PERSON_ID 
        AND s.ID = g.STUDENT_ID 
        AND g.COURSE_ID = c.ID
        -- ... other complex code here

        AND p.ID IN(
           -- Inner Sub-query here
        )

而每个子查询都有一个彼此不同的内部查询。像第一个和第三个：

SELECT DISTINCT p1.ID FROM PERSON p1, STUDENT s1 WHERE p1.ID = s1.PERSON_ID AND p1.LASTNAME LIKE 'E%'

和

SELECT DISTINCT p3.ID FROM PERSON p3 WHERE p3.GENDER = 'Female'

我需要什么：

我正在处理的实际代码要复杂得多，但是也有上面例子中出现的问题。

• 结果必须是包含多个数字的列表，每个数字按其不同的标准进行分类（最好在第一列中描述）。
• 它由几个子查询组成，由 UNION 连接
• 这些子查询中的每一个都是完全相同的，除了一个完全唯一的内部子查询，并且与其他子查询不同。
• 生成的代码是一个巨大的野兽，但从理论上讲，如果外部代码只编写一次，并且与作为参数传递的不同内部代码一起重用，则可以使其更具可读性。

最近在Oracle SQL中碰到了WITH-clause。类似于以下变化的内容将非常有益:

WITH outer_sub_query AS (
   SELECT 'DESCRIPTION HERE' AS Description, count(*) AS Number_of_students FROM 
      FROM PERSON p, STUDENT s, GRADE g, COURSE c
        WHERE p.ID = s.PERSON_ID 
        AND s.ID = g.STUDENT_ID 
        AND g.COURSE_ID = c.ID
        -- ... other complex code here

        AND p.ID IN(
           -- INSERT INNER SUB-QUERY HERE
        )
)
SELECT * FROM (
    outer_sub_query -- Last Names beginning with letter 'E'
    UNION 
    outer_sub_query -- Age > 20
    UNION 
    outer_sub_query -- All female
    UNION 
    outer_sub_query -- Passed that course with grade >= B
)

不幸的是，我的需求尚未得到满足。我仍然需要传递内部子查询以及描述。类似于以下内容：

SELECT * FROM (
    outer_sub_query(
        'Last names beginning with letter "E",'
        SELECT DISTINCT p1.ID FROM PERSON p1, STUDENT s1 
          WHERE p1.ID = s1.PERSON_ID AND p1.LASTNAME LIKE 'E%'
    )
    UNION 
    outer_sub_query(
        'Older than 20 years.'
        SELECT DISTINCT p2.ID FROM PERSON p2, STUDENT s2 
          WHERE p2.ID = s2.PERSON_ID AND p2.AGE > 20
    )
    UNION 
    outer_sub_query(
        'All female persons'
        SELECT DISTINCT p3.ID FROM PERSON p3 WHERE p3.GENDER = 'Female'
    )
    UNION 
    outer_sub_query(
        'Passed "Intermediate Norwegian" with grade >= B'
        SELECT DISTINCT p4.ID FROM PERSON p4, STUDENT s4, GRADE g4 AND COURSE c4
        WHERE p4.ID = s4.PERSON_ID 
        AND s4.ID = g4.STUDENT_ID 
        AND g4.COURSE_ID = c4.ID
        AND (g4.GRADE = 'A' OR g4.GRADE = 'B')
        AND c4.COURSE_NAME = 'Intermediate Norwegian'
    )
)

问题是:

现在，我们很容易想到定义函数。但这仍然给我带来了一些问题：

• 乍一看，WIT-子句似乎不接受可以传递的参数。SQL或OracleSQL中是否有其他预先存在的子句或函数来处理这个问题？
• 是否可以从外部子查询中提取内部子查询，并且仍然获得相同的结果？（请记住：外部子查询本身没有变化）。
• 如果我要定义一个处理此问题的函数，是否可以像上面所做的那样传递纯SQL代码？
• 我还缺少其他聪明的解决方案吗？

谢谢你的忠告。

Answer 1

从这里开始，我相信你会处理好的。

select      count(case when lastname like 'e%' then 1 end)  as lastname_starts_with_e
           ,count(case when age > 20           then 1 end)  as age_greater_than_20
           ,count(case when gender = 'Female'  then 1 end)  as is_female

from        person
;

Answer 2

在您的案例中，通用表表达式（如您已经建议的那样）似乎是减少代码重复的一种可能方法，但您正试图让它为您做太多事情。CTE不能按照您希望的方式进行参数化；如果它们是，那么您设想的使用将不再具有它们的共同点。

是的，您可以编写一个表值函数，但是这看起来有点过了，并且对于查询规划器来说很难分析。以下是CTE能做到的极限:

WITH student_grades AS (
 SELECT
   p.id AS id,
   p.lastname AS lastname,
   p.age AS age,
   p.gender AS gender,
   c.course_name AS course_name,
   g.grade AS grade
 FROM
   -- You really, really should use ANSI JOIN syntax:
   PERSON p
   JOIN STUDENT s ON p.ID = s.PERSON_ID
   JOIN GRADE g   ON s.ID = g.STUDENT_ID 
   JOIN COURSE c  ON g.COURSE_ID = c.ID
   -- WHERE ... other complex code here
)

然后，您可以使用...

-- 1st entry: Number of students on the last name 'E'
SELECT
  'Last names beginning with letter "E"' AS Description,
  count(distinct sg1.id) AS Number_of_students
FROM student_grades sg1
WHERE sg1.lastname LIKE 'E%'

UNION

-- 2nd entry: Number of students older than 20 years
SELECT
  'Older than 20 years' AS Description,
  count(distinct sg2.id) AS Number_of_students
FROM student_grades sg2
WHERE sg2.AGE > 20

UNION

-- 3rd entry: Number of female persons, including but not limited to students
-- NOTE: THIS ONE MATCHES YOUR ORIGINAL, WHICH IS INCORRECT
SELECT
  'All female persons' AS Description,
  count(distinct sg3.id) AS Number_of_students
FROM student_grades sg3
WHERE sg3.GENDER = 'Female'

UNION

-- 4th entry: Students who passed the course "Intermediate Norwegian" with grade B or higher
SELECT
  'Passed "Intermediate Norwegian" with grade >= B' AS Description,
  count(distinct sg4.id) AS Number_of_students
FROM student_grades sg4
WHERE
  sg4.COURSE_NAME = 'Intermediate Norwegian'
  AND sg4.grade IN ('A', 'B')

这实际上是一个显着的改进。特别注意，您不需要将条件（无论是否子查询）传递到CTE中；相反，您可以查询CTE（您也可以将其连接到其他表等）。当然，部分原因是您的“内部”子查询是一种非常可怕的做事方式；相反，我使用count（不同的sg.id），只要person.id为非空，它就可以实现与这些子查询相同的功能，我推测这是因为它是PK。

但是还要注意，即使是需要一个不同的计数（以及查询的第三部分的缺陷），也是因为首先尝试使用相同的公共中间结果来完成所有四个部分。为了查询与个人特征严格相关的信息，您不需要加入课程或成绩信息，只要学生与个人有0,1:1的关系，省略课程和成绩信息将免费为您提供一个明确的计数。

至于第三部分，加入学生表会将您的结果限制为学生，这是您不想要的。您没有在“内部”子查询中设置该限制这一事实是无关紧要的;您正在使用该子查询来筛选仅包含最初属于学生的用户的结果。因此，*在这种情况下，您的方法无法产生您想要的结果。

也许你想找出一大块常见查询的愿望来自神秘的“其他复杂代码”。我不明白这样的事情如何适用于你提出的问题，但我倾向于怀疑你最好找到一种方法——或者每项单独的方法——来简化或消除代码。如果这段代码可以被忽略，那么我可能会这样写您的查询:

WITH student_person AS (
 SELECT
   p.lastname AS lastname,
   p.age AS age,
   p.gender AS gender,
   s.id AS student_id
 FROM
   PERSON p
   JOIN STUDENT s ON p.ID = s.PERSON_ID
)

-- 1st entry: Number of students on the last name 'E'
SELECT
  'Last names beginning with letter "E"' AS Description,
  count(*) AS Number_of_students
FROM student_person sp1
WHERE sp1.lastname LIKE 'E%'

UNION ALL

-- 2nd entry: Number of students older than 20 years
SELECT
  'Older than 20 years' AS Description,
  count(*) AS Number_of_students
FROM student_person sp2
WHERE sp2.AGE > 20

UNION ALL

-- 3rd entry: Number of female persons, including but not limited to students
-- NOTE: THIS ONE MATCHES YOUR ORIGINAL, WHICH IS INCORRECT
SELECT
  'All female persons' AS Description,
  count(*) AS Number_of_students
-- must select from PERSON, not STUDENT_PERSON:
FROM person p2
WHERE p2.GENDER = 'Female'

UNION ALL

-- 4th entry: Students who passed the course "Intermediate Norwegian" with grade B or higher
SELECT
  'Passed "Intermediate Norwegian" with grade >= B' AS Description,
  count(distinct sp3.student_id) AS Number_of_students
FROM student_person sp3
JOIN grades g ON sp3.student_id = g.student_id
JOIN course c ON g.course_id = c.id
WHERE
  c.COURSE_NAME = 'Intermediate Norwegian'
  AND g.grade IN ('A', 'B')