postgresql - 如何在 SELECT 中使用 WITH AS（带别名）

Question

我正在尝试将Query 1嵌套在另一个查询（Query 2）中，WITH AS但可能与q我正在使用的别名存在一些冲突。我可以在查询 2之后使用查询 1 ，但我想知道是否有机会在这里使用。太感谢了！FROMWITH AS

桌子topups

 id_user |    date    | promo_ind 
---------+------------+----------
       1 | 2017-06-20 | N        
       1 | 2017-05-20 | N        
       1 | 2017-04-20 | Y        
       1 | 2017-03-20 | Y         
       1 | 2017-02-20 | N         

查询 1

SELECT *,
        (SELECT max(date)
         FROM topups q2 
         WHERE id_user = q.id_user AND 
               q2.promo_ind = 'Y' AND 
               q.date > q2.date) AS last_promo_start
FROM topups q ORDER BY date DESC;

查询 1 的结果

 id_user |    date    | promo_ind |last_promo_start
---------+------------+-----------+----------------
       1 | 2017-06-20 | N         | 2017-04-20
       1 | 2017-05-20 | N         | 2017-04-20
       1 | 2017-04-20 | Y         | 2017-03-20
       1 | 2017-03-20 | Y         |    NULL
       1 | 2017-02-20 | N         |    NULL 

查询 2

WITH table_2 AS
    (SELECT *,
        (SELECT max(date)
         FROM topups q2 
         WHERE id_user = q.id_user AND 
               q2.promo_ind = 'Y' AND 
               q.date > q2.date) AS last_promo_start
    FROM topups q)

SELECT id_user,
        date, 
        promo_ind, 
        
        (CASE WHEN last_promo_start + 28 < date 
            THEN (CASE WHEN promo_ind LIKE 'Y'
                    THEN date
                    ELSE NULL
                  END)
            ELSE last_promo_start
         END
         ) AS promo_start, 
        
        (CASE WHEN last_promo_start + 28 < date 
            THEN (CASE WHEN promo_ind LIKE 'Y'
                    THEN date + 28
                    ELSE NULL
                  END)
            ELSE (CASE WHEN promo_ind LIKE 'Y'
                    THEN last_promo_start + 28 + 28
                    ELSE last_promo_start + 28
                  END)
         END
         ) AS promo_end
         
FROM table_2
ORDER BY id_user, date DESC;

查询 2 的预期结果

 id_user |    date    | promo_ind | promo_start | promo_end 
---------+------------+-----------+-------------+-----------
       1 | 2017-06-20 | N         |    NULL     |   NULL
       1 | 2017-05-20 | N         |    NULL     |   NULL
       1 | 2017-04-20 | Y         | 2017-04-20  | 2017-05-18
       1 | 2017-03-20 | Y         | 2017-03-20  | 2017-04-17
       1 | 2017-02-20 | N         |    NULL     |   NULL 

标签： postgresqlsubquery