如何在SQL的UNION子查询中额外返回Promotions Id字段

不需要重复编写第二个查询语句，直接调整UNION子查询的列结构即可实现需求。

UNION运算要求所有查询分支返回的列数、对应位置的数据类型完全匹配，你只需要给两个UNION分支都补充ID字段：

• 旧促销表PROMGEN没有对应的Promotions Id，直接用NULL AS PromotionId占位，保证列结构一致
• 新促销表Promotions分支在返回定价的同时，补充返回P.Id AS PromotionId，注意原分支用了MIN()聚合取最低定价，加了ID字段后需要按P.Id分组，避免不同促销的价格被合并计算

具体修改步骤 #

1. 调整UNION内部的两个查询分支，统一返回定价、促销ID两列：
   • 旧表分支（PROMGEN）：在SELECT子句加NULL AS PromotionId，其余JOIN、WHERE逻辑完全不变
   • 新表分支（Promotions）：SELECT子句加P.Id AS PromotionId，末尾加GROUP BY P.Id，其余逻辑不变
2. 调整外层取TOP 1的子查询，同时返回Pricing和PromotionId两个字段，排序规则保持按Pricing ASC优先取最低价即可；如果遇到新旧促销价格相同的场景，你可以自行加排序规则决定优先取哪一侧的结果，比如加CASE WHEN PromotionId IS NOT NULL THEN 0 ELSE 1 END优先返回新促销的ID。
3. 因为取促销的子查询现在返回两列，建议用CROSS APPLY把促销计算逻辑关联到主查询，避免重复写逻辑，最终主查询可以同时返回PromotionPrice和PromotionId两个字段。

核心修改后的代码片段参考 #

-- 外层主查询部分调整，用CROSS APPLY关联促销计算
SELECT ISNULL(F.Price, ISNULL(R.SellingPrice, 0)) AS StandardPrice,
       -- 原有PricelistPrice子查询保持不变
       (SELECT DiscountLines.amount
        FROM Customers
             INNER JOIN CustDiscounts ON Customers.pricelist = CustDiscounts.no
             INNER JOIN DiscountLines ON CustDiscounts.no = DiscountLines.no
             INNER JOIN FinGoodsParent FP ON FP.Id = DiscountLines.FinGoods_ID
        WHERE Customer_Code = @CustomerCode
          AND CONVERT(date, CustDiscounts.startdate) <= @PricingDate
          AND CONVERT(date, CustDiscounts.enddate) >= @PricingDate
          AND DiscountLines.TYPE = @PriceType
          AND FP.Id = @StockParentId) AS PricelistPrice,
       BestPromo.Pricing AS PromotionPrice,
       BestPromo.PromotionId
FROM StockParent
     LEFT JOIN Fingoods F ON F.Id = StockParent.ID
     LEFT JOIN RAWMAT R ON R.Id = StockParent.ID
     -- 用CROSS APPLY取最优促销，一次返回价格和ID
     CROSS APPLY (
         SELECT TOP 1 Pricing, PromotionId
         FROM (
             -- 旧促销分支，补NULL作为PromotionId
             SELECT Pricing, NULL AS PromotionId
             FROM PROMGEN
                  INNER JOIN FinGoodsParent FP ON FP.Id = PROMGEN.FinGoods_ID
                  INNER JOIN PromCateg ON PROMGEN.No = PromCateg.PromGen_No
                  INNER JOIN PromGen_Channels ON PROMGEN.No = PromGen_Channels.PromGen_No
                  INNER JOIN Channels ON PromGen_Channels.Channels_Id = Channels.Id
             WHERE PROMGEN.MINQUANT <= @QTY
               AND @PricingDate >= CONVERT(date, PROMGEN.START_DATE)
               AND @PricingDate <= CONVERT(date, PROMGEN.END_DATE)
               AND Channels_Id = @ChannelID
               AND PromCateg.CustCateg_No = @CustCategoryNo
               AND FP.Id = @StockParentId
               AND PROMGEN.TYPE = @PriceType
             UNION ALL
             -- 新促销分支，补P.Id，加GROUP BY
             SELECT MIN(SP_P.PricingRuleTypeAmount) AS Pricing, P.Id AS PromotionId
             FROM StockParent_Promotions SP_P
                  INNER JOIN Promotions P ON P.Id = SP_P.Promotions_Id
                  INNER JOIN Promotions_Channels PC ON PC.Promotions_Id = P.Id
                  INNER JOIN StockParent SP ON SP_P.StockParent_Id = SP.Id
                  INNER JOIN Promotions_CustCateg PCC ON PCC.Promotions_Id = P.Id
                  INNER JOIN CUSTCATEG CC ON CC.NO = PCC.CustCateg_No
             WHERE SP_P.MinPurchaseQuantity <= @QTY
               AND @PricingDate >= CONVERT(date, P.DateValidFrom)
               AND @PricingDate <= CONVERT(date, P.DateValidTo)
               AND Channels_Id = @ChannelID
               AND PCC.CustCateg_No = @CustCategoryNo
               AND SP.Id = @StockParentId
               AND SP_P.PricingRuleType = @PriceType
               AND ((P.IsPromotionForEndUsers = 1 AND @CustomerDealerType = 1)
                 OR (P.IsPromotionForDealers = 1 AND @CustomerDealerType = 0))
               AND P.IsDeleted = 0
             GROUP BY P.Id
         ) OldAndNewPromotions
         WHERE Pricing IS NOT NULL
         ORDER BY Pricing ASC
     ) BestPromo
WHERE StockParent.Id = @StockParentId;

补充优化提示：你当前用CONVERT(date, 日期字段)的写法会导致字段上的索引无法生效，如果表数据量较大，建议改成范围判断的写法，比如判断新表促销日期时用P.DateValidFrom < DATEADD(DAY, 1, @PricingDate) AND P.DateValidTo >= @PricingDate，可以大幅提升查询性能。

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