SQL函数子查询报错：构建月度营收视图受阻求助

Hey，我一眼就揪出问题所在了——你遇到的Msg 512报错，核心原因是给@AccOmzet和@FietsOmzet赋值时，子查询返回了不止一行结果，但单个变量只能存储一个整数，自然就冲突了。

为啥会返回多行？ #

看你函数里的子查询，加了GROUP BY h.Huurovereenkomst_Begin_datum, h.Huurovereenkomst_Eind_datum，这会把每一组不同的起止日期都单独计算一次营收，返回多个值。但你要的是整个月份的总营收，不是每组的细分营收，所以这个GROUP BY完全是多余的，反而坑了你。

另外提个小细节：你的fnOmzet函数用了DATEDIFF(Y, @BeginDatum, @EindDatum)，这是算年份差啊！如果是按天收租金的话，应该用DATEDIFF(DAY, ...)吧？不然比如租了5天，年份差是0，营收直接变0，这逻辑肯定不对，后面我也会一起修正。

修复后的函数代码 #

我把两个子查询的GROUP BY去掉，并且把fnOmzet的调用放到SUM里面，确保整个子查询只返回一个总营收值：

CREATE FUNCTION fnOmzetPMaand ( @Maand AS int ) 
RETURNS int 
AS 
BEGIN 
    DECLARE @AccOmzet AS int 
    SET @AccOmzet = ( 
        SELECT SUM(dbo.fnOmzet(a.Accessoire_Dagprijs, h.Huurovereenkomst_Begin_datum, h.Huurovereenkomst_Eind_datum))
        FROM Huurovereenkomst h 
        INNER JOIN HuurovereenkomstAccessoire ha ON ha.HuurovereenkomstAccessoire_Huurovereenkomst_id = h.Huurovereenkomst_id 
        INNER JOIN Accessoire a ON a.Accessoire_id = ha.HuurovereenkomstAccessoire_Accessoire_id 
        WHERE MONTH(h.Huurovereenkomst_Betaal_datum) = @Maand 
    )

    DECLARE @FietsOmzet AS int 
    SET @FietsOmzet = ( 
        SELECT SUM(dbo.fnOmzet(f.Fiets_Dagprijs, h.Huurovereenkomst_Begin_datum, h.Huurovereenkomst_Eind_datum))
        FROM Huurovereenkomst h 
        INNER JOIN HuurovereenkomstFiets hf ON hf.HuurovereenkomstFiets_Huurovereenkomst_id = h.Huurovereenkomst_id 
        INNER JOIN Fiets f ON f.Fiets_id = hf.HuurovereenkomstFiets_Fiets_id 
        WHERE MONTH(h.Huurovereenkomst_Betaal_datum) = @Maand 
    )

    -- 处理NULL情况：如果某个月份没有配件/自行车营收，默认算0
    RETURN ISNULL(@FietsOmzet, 0) + ISNULL(@AccOmzet, 0) 
END

视图也要优化一下 #

你的原视图会给每条租赁记录都计算一次对应月份的营收，结果会重复出现同一个月份多次。我们按月份分组，只返回每个月份的一次结果：

CREATE VIEW vOmzetPMaand AS 
SELECT 
    DATENAME(MONTH, Huurovereenkomst_Betaal_datum) Maand,
    dbo.fnOmzetPMaand(MONTH(Huurovereenkomst_Betaal_datum)) Omzet
FROM Huurovereenkomst
GROUP BY MONTH(Huurovereenkomst_Betaal_datum), DATENAME(MONTH, Huurovereenkomst_Betaal_datum)
ORDER BY MONTH(Huurovereenkomst_Betaal_datum)

最后修正fnOmzet的逻辑 #

刚才说的日期差问题，改成按天计算：

ALTER FUNCTION [dbo].[fnOmzet] ( 
    @Bedrag AS int, 
    @BeginDatum AS DATETIME, 
    @EindDatum AS DATETIME 
) RETURNS int 
AS 
BEGIN 
    RETURN @Bedrag * DATEDIFF(DAY, @BeginDatum, @EindDatum)
END

这样运行视图后，就能得到你想要的结果：

May 100
June 200
July 150

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