SQL事件总收益计算问题：现有代码结果不符求助

需求规则 #

针对每个事件行，判断其Event Name是否同时满足：

• 该名称在更早的Event Date有过记录（存在过往事件）
• 该名称在更晚的Event Date有过记录（存在未来事件）
  若两个条件都满足，记录该事件的Price作为总收益；否则总收益为0。

规则示例：

• 2023年9月3日的事件无过往记录，总收益为0；
• 2023年9月7日仅Jazz Festival同时存在过往和未来记录，总收益为60；
• 2023年9月12日仅Theater Play同时存在过往和未来记录，总收益为40；
• 最后一个日期的事件无未来记录，总收益为0。

表结构 #

表[event].[dbo].[events]包含字段：Event Date、Transaction Date、Event Name、Price

现有代码问题 #

原代码核心错误：

1. JOIN条件未限定Event Name一致，导致关联所有当前及未来事件，错误累加无关事件的Price；
2. CASE逻辑未判断当前事件名称的过往存在性，也未限定未来事件为同名称，完全偏离需求逻辑。

现有代码：

WITH EventEarnings AS (
    SELECT
        e1.Event_Date AS "Event Date", e1.Event_Name AS "Event Name", e1.Price AS "Price",
        CASE
            WHEN e2.Event_Date > e1.Event_Date AND e2.Transaction_Date <= e1.Event_Date THEN e2.Price ELSE 0
        END AS "Earnings"
    FROM  [event].[dbo].[events] e1
    JOIN        [event].[dbo].[events] e2 ON e1.Event_Date <= e2.Event_Date
)
SELECT
    "Event Date",
    "Event Name",
    "Price",
    SUM("Earnings") AS "Total Earnings"
FROM
    EventEarnings
GROUP BY  "Event Date", "Event Name", "Price"
ORDER BY
    "Event Date";

当前输出 #

Event Date  Event Name          Price   Total Earnings
03/09/2023  Classical Concert   70      370
03/09/2023  Jazz Festival       60      370
07/09/2023  Classical Concert   70      260
07/09/2023  Theater Play        40      260
12/09/2023  Jazz Festival       60      150
12/09/2023  Rock Concert        50      150
18/09/2023  Comedy Night        30      70
18/09/2023  Rock Concert        50      70
22/09/2023  Comedy Night        30      0
22/09/2023  Theater Play        40      0

预期输出 #

Event Date  Event Name          Price   Total Earnings
03/09/2023  Classical Concert   70      0
03/09/2023  Jazz Festival       60      0
07/09/2023  Classical Concert   70      0
07/09/2023  Theater Play        40      0
07/09/2023  Jazz Festival       60      60
07/09/2023  Jazz Festival       60      60
12/09/2023  Theater Play        40      40
12/09/2023  Theater Play        40      40
22/09/2023  Comedy Night        30      0
22/09/2023  Theater Play        40      0

（数值序列对应预期：$0、$0、$60、$60、$40、$40、$40、$40、$0、$0）

修正后的代码 #

使用EXISTS子查询直接判断当前事件名称的过往和未来存在性，无需关联无关事件：

WITH EventEarnings AS (
    SELECT
        e1.Event_Date AS "Event Date",
        e1.Event_Name AS "Event Name",
        e1.Price AS "Price",
        CASE
            -- 检查同名称事件是否有更早的记录（过往）和更晚的记录（未来）
            WHEN EXISTS (
                SELECT 1 
                FROM [event].[dbo].[events] e2 
                WHERE e2.Event_Name = e1.Event_Name 
                  AND e2.Event_Date < e1.Event_Date
            )
            AND EXISTS (
                SELECT 1 
                FROM [event].[dbo].[events] e3 
                WHERE e3.Event_Name = e1.Event_Name 
                  AND e3.Event_Date > e1.Event_Date
            )
            THEN e1.Price
            ELSE 0
        END AS "Total Earnings"
    FROM [event].[dbo].[events] e1
)
SELECT
    "Event Date",
    "Event Name",
    "Price",
    "Total Earnings"
FROM EventEarnings
ORDER BY "Event Date";

代码说明 #

1. 第一个EXISTS子查询：验证当前事件名称是否存在更早日期的记录；
2. 第二个EXISTS子查询：验证当前事件名称是否存在更晚日期的记录；
3. 仅当两个条件都满足时，总收益取当前事件的Price，否则为0；
4. 无需GROUP BY，每个事件行的收益独立判断，直接输出即可。

内容的提问来源于stack exchange，提问作者Smilesync