如何访问使用CTE创建的临时列

Question

我已经创建了一个带有别名先前的列的列。我想访问此列并使用它来子集返回的表。我的查询是：

WITH t as
 (SELECT 
   col1,
   col2,
   col3,
   odds,
   col4
FROM 
 stuff)
SELECT * 
  , LAG(odds,1,0) OVER (partition by [col1] ORDER BY [col2] AS Previousodds
FROM t
-- What I would like to do but can't:
WHERE (1/Odds - 1/Previousodds) > 0.04

Answer 1

尝试此

WITH t as
 (SELECT 
   col1,
   col2,
   col3,
   odds,
   col4,
     LAG(odds) OVER (partition by [col1] ORDER BY [col2] AS Previousodds
 FROM  stuff
)
SELECT * 
FROM t
WHERE (1/Odds - 1/Previousodds) > 0.04

Answer 2

在子查询中选择语句：

WITH t as
 (SELECT 
   col1,
   col2,
   col3,
   odds,
   col4
FROM 
 stuff)
SELECT * 
FROM
    (
        SELECT * 
           , LAG(odds,1,0) OVER (partition by [col1] ORDER BY [col2] AS Previousodds
         FROM t
    )subquery
WHERE (1/Odds - 1/Previousodds) > 0.04

子征服和CTE也是相同的野兽，因此您可以将CTE作为该子查询的子查询移动，也可以将现在已成为子查询的选择移动到CTE中。

Answer 3

请参阅此。使用第一个CTE创建另一个CTE来实现并在实际查询中使用它们。jnevill的答案也将起作用。

WITH t as
 (SELECT 
   col1,
   col2,
   col3,
   odds,
   col4
FROM 
 stuff)
, 
x as (
SELECT * 
  , LAG(odds,1,0) OVER (partition by [col1] ORDER BY [col2] AS Previousodds
FROM t)    
SELECT * FROM x
WHERE (1/Odds - 1/Previousodds) > 0.04