TVF UDF 不返回与 SELECT 相同的数据

Question

像这样调用 UDF：

SELECT
       product_name,
       SUM(quantity) AS SumQty,
       SUM(face_value) AS SumFaceValue,
       SUM(net_cost)AS SumNetCost,
       SUM(face_value - net_cost) AS SumScripRebate,
       organization_name
FROM getSalesSummary(@GLSCOrgId, @BeginDate, @EndDate) getSalesSummary
GROUP BY product_name, organization_name
ORDER BY product_name

收益：

   "Chili's      1    25.00   22.75  2.25   Sample Organization 1
    CVS/pharmacy 1  25.00   23.50  1.50   Sample Organization 1
    Macy's       1  100.00  90.00  10.00  Sample Organization 1"

使用 UDF 逻辑并使用 SELECT 测试结果：

SELECT 
       product_name,
       SUM(quantity) AS SumQty,
       SUM(face_value) AS SumFaceValue,
       SUM(net_cost) AS SumNetCost,
       SUM(face_value - net_cost) AS SumScripRebate,
       organization_name
FROM @ReturnTable
GROUP BY product_name, organization_name
ORDER BY product_name

收益：

   "Chili's       4   100.00  91.00   9.00   Sample Organization 1
    CVS/pharmacy  1   25.00   23.50   1.50   Sample Organization 1
    Macy's        1   100.00  90.00   10.00  Sample Organization 1"

@ReturnTable 是 UDF 返回的表，其创建方式如下：

INSERT INTO @ReturnTable(product_name,
                         unit_price,
                         quantity,
                         face_value,
                         net_cost,
                         organization_name)
(select * from @TablePartial UNION select * from @TableClosed)

使用 SELECT 和变量的测试返回正确的数据，但是调用 UDF 无法获取其他 3 个 Chili 的记录。我使用相同的数据作为参数。我对 UDF 还很陌生，我不确定为什么它会返回与 SELECT 不同的数据。有什么建议和/或答案吗？

Answer 1

您可能需要 UNION ALL 而不是 UNION

查看两个结果集，将其加起来就好像 4 个 Chilli 的行都是相同的。

Chili's      1    25.00   22.75  2.25   Sample Organization 1
Chili's      1    25.00   22.75  2.25   Sample Organization 1
Chili's      1    25.00   22.75  2.25   Sample Organization 1
Chili's      1    25.00   22.75  2.25   Sample Organization 1
-------------------------------------------------------------
Chili's       4   100.00  91.00   9.00   Sample Organization 1

使用 UNION 将删除重复项，留下一行。

Answer 2

我唯一能想到的是 UNION 将其更改为 UNION ALL UNION 将消除重复

运行这些查询以查看差异

select 1 as a
union 
select 1
union 
select 1


select 1 as a
union  all
select 1
union all
select 1