无关联表场景下使用SQL生成费率日期对应价格结果表咨询

现有三张数据表，需求是生成符合指定规则的结果表。

表1：费率数据表 #

存储费率基础数据，字段为Rate（费率名称）、ID（费率ID），具体数据如下：

表2：日期数据表 #

存储待匹配的日期数据，字段为Dates（日期），具体数据如下：

表3：费率生效价格数据表 #

存储对应费率在指定日期区间的生效价格，字段为Rate（费率名称）、ID（费率ID）、startdate（生效开始日期）、enddate（生效结束日期）、price（对应价格），具体数据如下：

需求 #

输出结果需包含表1全部Rate和表2全部Dates的所有组合，若对应日期落在该费率的生效起止日期区间内则展示对应price，否则price展示为0，预期输出如下：

实现思路 #

1. 用CROSS JOIN关联表1和表2，生成所有「费率-日期」的全组合，满足输出全部Rate和全部Dates组合的要求。
2. 将上述全组合结果和表3做LEFT JOIN，关联条件为：Rate字段相等，且日期落在表3的生效日期区间内。
3. 用COALESCE函数将未匹配到价格的空值替换为0，即可得到要求的结果。

通用SQL代码（日期为DATE类型时适用） #

SELECT 
  t1.Rate,
  t2.Dates AS `date`,
  COALESCE(t3.price, 0) AS price
FROM 
  表1 t1
CROSS JOIN 
  表2 t2
LEFT JOIN 
  表3 t3 
  ON t1.Rate = t3.Rate 
  AND t2.Dates BETWEEN t3.startdate AND t3.enddate
ORDER BY 
  t1.Rate, t2.Dates;

日期为DD-MM-YYYY格式字符串时的适配代码（以MySQL为例） #

如果你的日期字段存储为字符串格式，需要先转换为日期类型再做比较，避免逻辑错误：

SELECT 
  t1.Rate,
  t2.Dates AS `date`,
  COALESCE(t3.price, 0) AS price
FROM 
  表1 t1
CROSS JOIN 
  表2 t2
LEFT JOIN 
  表3 t3 
  ON t1.Rate = t3.Rate 
  AND STR_TO_DATE(t2.Dates, '%d-%m-%Y') BETWEEN STR_TO_DATE(t3.startdate, '%d-%m-%Y') AND STR_TO_DATE(t3.enddate, '%d-%m-%Y')
ORDER BY 
  t1.Rate, STR_TO_DATE(t2.Dates, '%d-%m-%Y');

内容的提问来源于stack exchange，提问作者Ahtesham ul haq