SQL Kata JOIN条件中能否使用SQL函数？SQL Server场景实现方案求助

1. 在JOIN条件中嵌入SQL函数的正确方式 #

SQLKata支持通过OnRaw()方法在JOIN条件中直接写入原生SQL函数逻辑，完全适配SQL Server的日期处理需求。比如要将细粒度的营业额日期与日期范围表的时间单位对齐，示例代码如下：

假设日期范围表值函数为DateRange，营业额表为Turnover，按月份分组的实现：

var query = new Query()
    .With("DateSeries", q => q
        .FromRaw("dbo.DateRange(@startDate, @endDate, 'month')", new { startDate = "2024-01-01", endDate = "2024-06-01" })
    )
    .Select("ds.DateRangeStart AS PeriodStart")
    .SelectRaw("COALESCE(SUM(t.Amount), 0) AS TotalTurnover")
    .From("DateSeries AS ds")
    .LeftJoin("Turnover AS t", j => j
        .OnRaw("DATEFROMPARTS(YEAR(t.TransactionDate), MONTH(t.TransactionDate), 1) = ds.DateRangeStart")
    )
    .GroupBy("ds.DateRangeStart");

你可以根据间隔类型（日/周/年）替换OnRaw()中的函数逻辑，比如按周匹配时用DATEPART(week, t.TransactionDate) = DATEPART(week, ds.DateRangeStart) AND DATEPART(year, t.TransactionDate) = DATEPART(year, ds.DateRangeStart)。

2. 修复双CTE的"空基查询"错误 #

使用多个CTE时，必须在最后添加明确的主查询逻辑（即从CTE中选择数据并关联），否则SQLKata会因无最终查询目标报错。示例如下：

var query = new Query()
    // 第一个CTE：按间隔预聚合营业额
    .With("AggregatedTurnover", q => q
        .SelectRaw("DATEFROMPARTS(YEAR(TransactionDate), MONTH(TransactionDate), 1) AS PeriodStart")
        .SelectRaw("SUM(Amount) AS Total")
        .From("Turnover")
        .GroupByRaw("DATEFROMPARTS(YEAR(TransactionDate), MONTH(TransactionDate), 1)")
    )
    // 第二个CTE：生成日期范围序列
    .With("DateSeries", q => q
        .FromRaw("dbo.DateRange(@start, @end, 'month')", new { start = "2024-01-01", end = "2024-06-01" })
    )
    // 主查询：关联两个CTE，补全无营业额的时间段
    .Select("ds.DateRangeStart AS PeriodStart")
    .SelectRaw("COALESCE(at.Total, 0) AS TotalTurnover")
    .From("DateSeries AS ds")
    .LeftJoin("AggregatedTurnover AS at", j => j
        .On("at.PeriodStart", "ds.DateRangeStart")
    );

3. 动态适配可配置间隔 #

可以封装方法动态生成不同间隔的日期匹配SQL，实现灵活切换：

string GetDateMatchSql(string interval)
{
    return interval switch
    {
        "day" => "CAST(t.TransactionDate AS DATE) = ds.DateRangeStart",
        "week" => "DATEPART(week, t.TransactionDate) = DATEPART(week, ds.DateRangeStart) AND DATEPART(year, t.TransactionDate) = DATEPART(year, ds.DateRangeStart)",
        "month" => "DATEFROMPARTS(YEAR(t.TransactionDate), MONTH(t.TransactionDate), 1) = ds.DateRangeStart",
        "year" => "YEAR(t.TransactionDate) = YEAR(ds.DateRangeStart)",
        _ => throw new ArgumentException("无效的间隔类型")
    };
}

之后在查询中动态传入间隔参数即可：

var interval = "month"; // 可从配置中读取
var query = new Query()
    .With("DateSeries", q => q
        .FromRaw("dbo.DateRange(@start, @end, @interval)", new { start = "2024-01-01", end = "2024-06-01", interval })
    )
    .Select("ds.DateRangeStart AS PeriodStart")
    .SelectRaw("COALESCE(SUM(t.Amount), 0) AS TotalTurnover")
    .From("DateSeries AS ds")
    .LeftJoin("Turnover AS t", j => j
        .OnRaw(GetDateMatchSql(interval))
    )
    .GroupBy("ds.DateRangeStart");

内容的提问来源于stack exchange，提问作者Nihad Abou-Zid