子查询作为联接？

6xfqseft 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(261)

我的问题如下。

SELECT *,
SUM(A.money_step) over (
    partition by A.id_key, A.P 
    ORDER BY A.actual_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)as accumulated
FROM
(
    SELECT A.*,
    (
         SELECT CASE WHEN COUNT(*) >= 2 THEN 2 ELSE 1 END
         FROM example B 
         WHERE B.id_key = A.id_key
         AND B.actual_date <= A.actual_date
         AND attendance_status = 15
     ) P
     FROM example A
 )A
 ORDER BY A.id_key,A.actual_date

是否可以将子查询表示为from子句中的联接？我使用“denodo”工具，在这里我不能轻松地在from子句中执行子查询。

sql sql-server etl tsql denodo

来源：https://stackoverflow.com/questions/62188785/subquery-as-join

1条答案

按热度按时间

oxosxuxt1#

您可以将子查询转移到cte，如下所示-

WITH CTE AS
(
    SELECT A.*,
    (
        SELECT CASE WHEN COUNT(*) >= 2 THEN 2 ELSE 1 END
        FROM your_table B 
        WHERE B.id_key = A.id_key
        AND B.actual_date <= A.actual_date
        AND attendance_status = 15
    ) P
    FROM your_table A
)

SELECT actual_date,id_key,attendance_status,money_step,
SUM(A.money_step) over (
    partition by A.id_key, A.P 
    ORDER BY A.actual_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)as accumulated
FROM CTE A
ORDER BY A.id_key,A.actual_date

您还可以将子查询直接放入分区部分的窗口函数中，如下所示-

SELECT *,
SUM(A.money_step) over (
    partition by A.id_key,(
        SELECT CASE WHEN COUNT(*) >= 2 THEN 2 ELSE 1 END
        FROM your_table B 
        WHERE B.id_key = A.id_key
        AND B.actual_date <= A.actual_date
        AND attendance_status = 15
    )
    ORDER BY A.actual_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)as accumulated
FROM your_table A

赞(0）回复(0）举报 2021-07-26

我来回答

子查询作为联接？

1条答案

相关问题

热门标签

最新问答