sql - 子查询作为联接？

Question

我的查询如下所示。

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 子句中执行子查询。

标签： sqlsql-servertsqletldenodo

Answer 1

您可以将您的子查询转移到 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