如何编写Dynamic SQL查询计算客户入驻首日起前10天的累计营收

安全阿岳

2026-9-28

参考数据表

1. 客户表（Customer Table）

id	name	onboarded_date
1	xxxx	2021-10-01
2	yyyy	2021-10-02
3	zzzz	2021-10-05

2. 营收表（Revenue Table）

id	customer_id	date	revenue
1	1	2021-10-01	100
1	2	2021-10-02	300
3	2	2021-10-03	200
4	3	2021-10-07	100
5	2	2021-10-10	100
6	3	2021-10-12	300
7	3	2021-10-14	600

实现查询语句

不需要额外编写复杂的动态SQL存储逻辑，直接关联两张表，通过动态日期范围过滤即可实现需求：

SELECT 
    c.id,
    c.name,
    c.onboarded_date AS start_date,
    DATE_ADD(c.onboarded_date, INTERVAL 9 DAY) AS end_date, -- 含入驻当天共10天，所以向后加9天
    COALESCE(SUM(r.revenue), 0) AS total_revenue
FROM customer c
LEFT JOIN revenue r 
    ON c.id = r.customer_id
    AND r.date BETWEEN c.onboarded_date AND DATE_ADD(c.onboarded_date, INTERVAL 9 DAY)
GROUP BY c.id, c.name, c.onboarded_date
ORDER BY c.id;

不同数据库的日期运算函数略有差异：SQL Server可替换为DATEADD(day,9,c.onboarded_date)，Oracle可替换为c.onboarded_date + 9，核心逻辑一致。