关于子查询嵌套聚合函数改写临时表

Question

SELECT * 
FROM (
    SELECT * 
    FROM CV 
    WHERE EEFDATE < CURRENT_DATE 
      AND ARR = 'A'
) A
INNER JOIN B 
  ON A.POL = B.POL 
     AND A.CO = B.CO 
     AND B.STS = 'A'
     AND B.CHEDATE = (
         SELECT MAX(CHEDATE) 
         FROM B B1 
         WHERE B1.POL = B.POL 
           AND B1.CO = B.CO 
           AND B1.STS = 'A' 
           AND B1.CHEDATE <= current_date 
     )
     AND B.CRDATE = (
         SELECT MAX(CRDATE) 
         FROM B B2 
         WHERE B2.POL = B.POL 
           AND B2.CO = B.CO 
           AND B2.STS = 'A' 
           AND B2.CHEDATE = B.CHEDATE
     )
LEFT JOIN C 
  ON C.POL = B.POL 
     AND C.NUMCO = B.NUMCO 
     AND C.TU = 'U' 
     AND C.STS = 'A'
     AND C.CHEDATE = (
         SELECT MAX(CHEDATE) 
         FROM C C1 
         WHERE C1.POL = C.POL 
           AND C1.NUMCO = C.NUMCO 
           AND C1.STS = 'A' 
           AND C1.CHEDATE <= A.CHEDATE
     )
     AND C.CRDATE = (
         SELECT MAX(CRDATE) 
         FROM C C2 
         WHERE C2.POL = C.POL 
           AND C2.NUMCO = C.NUMCO 
           AND C1.STS = 'A' 
           AND C2.CHEDATE = C.CHEDATE 
           AND C2.CRDATE <= A.EFFDATE
     )

系统不支持子查询里面套聚合函数这种写法，并且第三四个子查询里面还有涉及 A 表的字段对比，但是 c 和 a 表没有直接关联
有没有人可以给个改写建议？

Answer 1

以下回复参考：皆我百晓生、券券喵儿等免费微信小程序作答:

确实，直接在子查询中使用聚合函数可能会导致性能问题，特别是在大型数据库中。针对您的情况，我会建议使用临时表或者公用表表达式（CTE）来改写这个查询。这样可以帮助我们更有效地组织和处理数据，同时避免子查询中的复杂性和性能问题。以下是一个可能的改写方案：

首先，我们可以使用临时表或CTE来存储子查询的结果，然后在主查询中使用这些结果。这里的关键是确保我们正确地处理了所有必要的连接条件和筛选条件。以下是使用临时表的示例：

-- 创建临时表存储子查询结果
CREATE TEMPORARY TABLE TempSubQueries AS
SELECT 
    POL, CO, MAX(CHEDATE) AS MaxCheDate, MAX(CRDATE) AS MaxCrDate
FROM B
WHERE STS = 'A' AND CHEDATE <= CURRENT_DATE
GROUP BY POL, CO;

-- 主查询使用临时表数据
SELECT * 
FROM (
    SELECT A.*, B.* 
    FROM CV A 
    INNER JOIN B ON A.POL = B.POL AND A.CO = B.CO AND B.STS = 'A' AND B.EEFDATE < CURRENT_DATE AND B.ARR = 'A'
    INNER JOIN TempSubQueries ON B.POL = TempSubQueries.POL AND B.CO = TempSubQueries.CO
) AS AInnerJoinB
LEFT JOIN C ON C.POL = AInnerJoinB.POL AND C.NUMCO = AInnerJoinB.NUMCO AND C.TU = 'U' AND C.STS = 'A' AND C.CHEDATE <= AInnerJoinB.CHEDATE AND C.CRDATE <= AInnerJoinB.`EFFDATE`;

在这个例子中，我们首先创建了一个临时表 TempSubQueries 来存储每个 POL 和 CO 组合的最大 CHEDATE 和 CRDATE。然后，在主查询中，我们使用该临时表中的数据来连接表 CV 和表 B，然后再连接表 C。通过这种方式，我们避免了在子查询中使用聚合函数的问题。请注意，这只是一个示例，您可能需要根据您的具体数据库结构和需求进行调整。此外，使用临时表可能会影响性能，特别是在处理大量数据时，因此请确保在生产环境中进行充分的测试。