时间戳

首页 > 解决方案 > 如何在没有'with'的情况下将多个子查询合并为一个？

sql - 如何在没有'with'的情况下将多个子查询合并为一个？

问题描述

我有一个由多个子查询组成的查询。我使用'join'，因为我不允许使用'with'。子查询具有“来自”子句，这会产生问题。

我必须显示两列，每列包含要显示的某些逻辑。为了打印这两列，我需要使用需要“from”子句的子查询。我不确定如何编写“from”子句以适应整个查询并使其可运行。我检查了各个查询，它们都工作正常。


select lead(dt) over 
(partition by t1.id_user order by f.topup_date desc rows between 0 
preceding and unbounded following )
from 
  (select *,    
  (max(case when f.topup_value >= 20 then f.topup_date  end) over (partition 
   by f.id_user order by f.topup_date desc rows between 0 preceding and 
   unbounded following )) as dt 
   from topups f) as f, //(<-I think this is incorrect) 

CAST(f.topup_value as float)/CAST(t1.topup_value as float) from 
(SELECT t1.seq,t1.id_user,t1.topup_value,row_number() 
over (partition by t1.id_user order by t1.topup_date ) 
as rowrank from topups t1) as t1 
inner join topups f on f.id_user=t1.id_user 
inner join topups t2 on t1.seq=t2.seq

标签： sqlpostgresqlwindow-functions

解决方案

真的很难阅读该查询。您标记为可能不正确的内容是错误的，因为您试图SELECT在原始FROM子句之后添加看起来像另一个的内容。那是不正确的语法。将您的FROM子查询视为临时表。你不能说这样的话：

SELECT some_column
FROM a_table, some_other_column

那是交叉连接语法。some_other_column需要成为一个表格才能使其有效。

考虑添加 aCREATE TABLE和样本数据，以便我们进行测试。

您可能正在寻找类似以下内容的内容：

SELECT  LEAD(temp.dt) OVER(PARTITION BY temp.id_user ORDER BY temp.topup_date DESC ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING)
        , temp.division
FROM
(
    SELECT  (max(CASE WHEN f.topup_value >= 20 THEN f.topup_date  END) OVER(PARTITION BY f.id_user ORDER BY f.topup_date DESC ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING )) AS dt
            , f.topup_value::float / t1.topup_value::float AS division
            , t1.id_user
            , f.topup_date
    FROM topups t1
        JOIN topups f USING (id_user)
) temp
;

只是一种意见，但使用::运算符来转换变量的噪音较小。而不是CAST(f.topup_value as float)仅仅使用f.topup_value::float

推荐阅读