代码之家 › 专栏 › 技术社区 › HPWD

撤消CTE(公用表表达式)

common-table-expression mariadb mysql

HPWD · 技术社区 · 6 年前

WITH temptable (column1, column2, column3) 
     AS (SELECT t3.column1, 
                t3.column2, 
                CASE 
                  WHEN t3.column3 = 1 
                       AND t2.column3 = 1 THEN 2 
                  ELSE COALESCE(t2.column3, 0) 
                END AS column3 
         FROM   table1 t1 
                JOIN table2 t2 
                  ON t1.column5 = t2.column5 
                     AND t1.column6 = t2.column6 
                JOIN table3 t3 
                  ON t3.column1 = t2.column1 
         WHERE  t1.column4 = :var1 
                AND t1.column6 = :var2 
                AND t3.column7 = 0)
SELECT column2, 
       column3 
FROM   temptable 
UNION 
SELECT t3.column2, 
       t3.column3 
FROM   table3 t3 
WHERE  t3.column7 = -1 
UNION 
SELECT t3.column2, 
       0 AS column3 
FROM   table3 t3 
       LEFT JOIN temptable temp 
              ON temp.column2 = t3.column2 
WHERE  temp.action IS NULL 
       AND t3.column7 = 0;

1 回复 | 直到 6 年前

spencer7593 6 年前

temptable

切掉查询的开头,这部分,

WITH temptable 
( column1
, column2
, column3
) 
AS
( SELECT t3.column1
       , t3.column2
       , CASE
           WHEN t3.column3 = 1 AND t2.column3 = 1 THEN 2
           ELSE COALESCE(t2.column3, 0)
         END AS column3 
    FROM table1 t1
    JOIN table2 t2
      ON t1.column5   = t2.column5
     AND t1.column6   = t2.column6
    JOIN table3 t3
      ON t3.column1   = t2.column1
   WHERE t1.column4   = :var1
     AND t1.column6   = :var2 
     AND t3.column7   = 0
)

SELECT a.column2
     , a.column3 
  FROM temptable a

 UNION 

SELECT b.column2
     , b.column3 
  FROM table3 b
 WHERE b.column7 = -1

 UNION 

SELECT p.column2
     , 0 AS column3
  FROM table3 p
  LEFT
  JOIN temptable q
    ON q.column2 = p.column2
 WHERE q.action IS NULL
   AND p.column7 = 0

(如对该问题的评论中所述, action 行动 (第页)

使用内联视图定义。

a 和 q

SELECT a.column2
     , a.column3 
  FROM -- temptable
       ( 
         SELECT t3.column1
              , t3.column2
              , CASE
                  WHEN t3.column3 = 1 AND t2.column3 = 1 THEN 2
                  ELSE COALESCE(t2.column3, 0)
                END AS column3 
           FROM table1 t1
           JOIN table2 t2
             ON t1.column5   = t2.column5
            AND t1.column6   = t2.column6
           JOIN table3 t3
             ON t3.column1   = t2.column1
          WHERE t1.column4   = :var1
            AND t1.column6   = :var2 
            AND t3.column7   = 0
       ) a

 UNION

SELECT b.column2
     , b.column3 
  FROM table3 b
 WHERE b.column7 = -1

 UNION 

SELECT p.column2
     , 0 AS column3
  FROM table3 p
  LEFT
  JOIN -- temptable 
       (
         SELECT t3.column1
              , t3.column2
              , CASE
                  WHEN t3.column3 = 1 AND t2.column3 = 1 THEN 2
                  ELSE COALESCE(t2.column3, 0)
                END AS column3 
           FROM table1 t1
           JOIN table2 t2
             ON t1.column5   = t2.column5
            AND t1.column6   = t2.column6
           JOIN table3 t3
             ON t3.column1   = t2.column1
          WHERE t1.column4   = :var1
            AND t1.column6   = :var2 
            AND t3.column7   = 0
       ) q
    ON q.column2 = p.column2
 WHERE q.action IS NULL
   AND p.column7 = 0

编辑

:var1 和 :var2 :var1b :var2b (至少在使用PDO的命名占位符中是这样的,它们必须是唯一的)

和

跟进

和和