SQL自连接生成百分比列：CTE过滤规则选择性应用问题求助

我明白你遇到的核心问题：想要让分子同时应用Quirks_CTE和Surplus_CTE的双重过滤，而分母只应用Quirks_CTE的过滤条件，但之前的自连接写法导致分母总是取全量数据，完全没按预期过滤。

问题根源分析 #

你原来的LEFT JOIN搭配WHERE EXISTS的写法，实际上会把左连接转换成内连接——因为WHERE子句里的条件依赖于a表（即Quirks_CTE的关联行），这就导致分母的b表只有在a表存在匹配行时才会被统计，完全偏离了“分母是Quirks_CTE中符合条件的全集”的需求。

修正后的函数代码 #

我们可以换一种思路：分别计算分子和分母的计数，再做除法。这样逻辑更清晰，也能精准控制各自的过滤条件：

CREATE FUNCTION [dbo].[ExampleOfQueryStructure] ( @MyInputParameter int )
RETURNS @MyOutputParameter TABLE ( [MyPercentage] float )
AS
BEGIN
    WITH Quirks_CTE (AnimalDateTime, Quirks) AS
    (
        SELECT 
            AnimalDateTime, 
            (COALESCE(Lion, 0) + COALESCE(Zebra, 0) + COALESCE(Antelope, 0) + COALESCE(Giraffe, 0))
        FROM dbo.tblAnimals
    ),
    Surplus_CTE AS
    (
        SELECT 
            JobEntryDateTime,
            CASE WHEN tblJobEntries.Stance = 1 THEN ExitLevel5 - BeginLevel ELSE BeginLevel - ExitLevel5 END AS [5_SURPLUS],
            CASE WHEN tblJobEntries.Stance = 1 THEN ExitLevel8 - BeginLevel ELSE BeginLevel - ExitLevel8 END AS [8_SURPLUS],
            CASE WHEN tblJobEntries.Stance = 1 THEN ExitLevel20 - BeginLevel ELSE BeginLevel - ExitLevel20 END AS [20_SURPLUS],
            CASE WHEN tblJobEntries.Stance = 1 THEN ExitLevelStone - BeginLevel ELSE BeginLevel - ExitLevelStone END AS [StoneProfit]
        FROM dbo.tblJobEntries
    )
    INSERT INTO @MyOutputParameter ([MyPercentage])
    SELECT 
        -- 处理分母为0的情况，避免除以0错误
        CASE WHEN Denominator = 0 THEN 0.0 ELSE CAST(Numerator AS float) / Denominator END AS MyPercentage
    FROM (
        -- 计算分子：同时满足Quirks阈值和Surplus过滤的行数
        SELECT 
            (SELECT COUNT(*) 
             FROM Quirks_CTE q
             WHERE q.Quirks <= @MyInputParameter
               AND EXISTS(
                   SELECT 1 
                   FROM Surplus_CTE s 
                   WHERE q.AnimalDateTime = s.JobEntryDateTime 
                     AND ([5_SURPLUS] > 0 OR [8_SURPLUS] > 0 OR [20_SURPLUS] > 0 OR [StoneProfit] > 0)
               )) AS Numerator,
            -- 计算分母：仅满足Quirks阈值的行数
            (SELECT COUNT(*) 
             FROM Quirks_CTE q
             WHERE q.Quirks <= @MyInputParameter) AS Denominator
    ) AS Counts
    RETURN
END

关键改进点 #

• 分离分子分母计算：用子查询分别统计符合条件的分子和分母数量，彻底避免自连接带来的逻辑混淆
• 除以0防护：添加CASE语句处理分母为0的情况（比如输入0时，分母为0，直接返回0）
• 类型转换：把分子转成float再做除法，确保得到小数结果（而不是整数除法）

预期验证 #

按照你的输入输出要求：

• 输入0 → 输出0
• 输入1 → 输出0.5
• 输入2 → 输出0.666...
• 输入3 → 输出0.666...

这个写法可以精准实现你的需求，后续你可以直接用FORMAT函数把结果转换成百分比格式（比如FORMAT(MyPercentage, 'P')）。

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