优化CTE查询

Question 1

我有一个很大的加入的15表。简化它看起来像这样

WITH CTE AS
(
  SELECT A.ID,
         B.K,
         X.Date,
         X.ID,
         ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY X.date ASC, x.id ASC) RN
         FROM A
         INNER JOIN B ON A.ID = B.ID        
         INNER JOIN C ON B.ID = C.ID    
         LEFT JOIN X ON (X.G = A.ID
                     OR X.H = B.ID
                     OR X.I = C.ID)
)
SELECT * 
FROM CTE
WHERE RN = 1

执行时间大约是1第二主要是因为最后加入的 X. 为了避免重复的我加入 ROW_NUMBER 部分，并把它变成一个CTE。

我怎么可能优化这一优选地使用ANSI SQL由于上运行SQL服务器和Oracle?

Question 2

我得到了它。这是超级快速喜欢这个(0.1秒)：

  SELECT A.ID,
         B.K,
         X.DATE,
         X.ID,
  FROM A
  INNER JOIN B ON A.ID = B.ID        
  INNER JOIN C ON B.ID = C.ID    
  LEFT JOIN 
  (
     SELECT G, H, I, ID, DATE,
     ROW_NUMBER() OVER (PARTITION BY G ORDER BY DATE ASC, ID ASC) RN
     FROM X
  ) X ON RN = 1 AND (X.G = A.ID
                     OR X.H = B.ID
                     OR X.I = C.ID)

juergen d · Answer 1 · 2021-10-27T13:48:09

我得到了它。这是超级快速喜欢这个(0.1秒)：

  SELECT A.ID,
         B.K,
         X.DATE,
         X.ID,
  FROM A
  INNER JOIN B ON A.ID = B.ID        
  INNER JOIN C ON B.ID = C.ID    
  LEFT JOIN 
  (
     SELECT G, H, I, ID, DATE,
     ROW_NUMBER() OVER (PARTITION BY G ORDER BY DATE ASC, ID ASC) RN
     FROM X
  ) X ON RN = 1 AND (X.G = A.ID
                     OR X.H = B.ID
                     OR X.I = C.ID)

优化CTE查询

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别