使用SQL游标计算Cross-Sell Rate遇Msg156语法错误求解决

问题诊断 #

你遇到的Msg 156语法错误主要来自两个核心问题：

• INSERT语句顺序颠倒：在游标循环内，你把SELECT ...放在了Insert Into #Output前面，正确的语法应该是INSERT INTO 表名 SELECT ...，顺序错误直接导致语法解析失败。
• 表别名引用错误：在#TempB的查询中，你使用了未定义的别名p（表tblorder的别名明明是op），这也是触发语法报错的关键原因。
• 额外隐患：每次循环中临时表的DROP操作虽然存在，但如果第一次循环后清理不及时，也可能引发对象重复创建的问题，但主要报错还是来自前两个语法问题。

修正后的游标版本代码 #

下面是修复了语法错误的完整代码，同时优化了订单数统计的准确性：

-----------------------DECLARE VARIABLES------------------------------------ 
DECLARE @brand int 
SET @brand = 1 
DECLARE @store int 
SET @store = 01920 
DECLARE @sku nvarchar(max) 
SET @sku = 'xxx' -- Related Product
DECLARE @vsku nvarchar(max) --USED for Cursor to insert Base Products 
DECLARE @startdate datetime 
SET @startdate = '2018-05-09' --Set Start Date 
DECLARE @enddate datetime 
SET @enddate = '2018-05-22' -- Set End Date 

------------------------CREATE TEMPDB -------------------------------------- 
IF OBJECT_ID('tempdb..#TempA') IS NOT NULL --TempA: Pull ALL ORDERS CONTAINING RELATED Product 
DROP TABLE #TempA 
IF OBJECT_ID('tempdb..#TempB') IS NOT NULL --TempB: PULL ALL ORDERS CONTAINING Base Product 
DROP TABLE #TempB 
IF OBJECT_ID('tempdb..#TempC') IS NOT NULL--TempC: PULL ALL ORDERS THAT CONTAIN BOTH Related and Base Product 
DROP TABLE #TempC 

CREATE TABLE #Output( 
    [BaseProduct] nvarchar(max), 
    [RelatedProduct] nvarchar(max), 
    [Cross-SellRate] nvarchar(max) 
) 

-------TEMPA: PULL ALL OrderID CONTAINING Related Product----------------- 
SELECT DISTINCT OpOrID, OpPrSKU, OpQty, OpCancelled 
INTO #TempA 
FROM tblOrder op (NOLOCK) 
JOIN tblpayment orp (NOLOCK) ON op.oporid = orp.PyOrID 
WHERE orp.PyDateNew BETWEEN @startdate AND @enddate 
    AND opprsku = @sku 
    AND opcancelled = 0 

---------------------DECLARE CURSOR----------------------------------------- 
Declare x cursor for 
Select distinct [Base Product] from tblCrossSellData 

Open x 
Fetch Next From X into @vsku 

While @@FETCH_STATUS = 0 
BEGIN 
    ------TEMPB: USE CURSOR TO PULL ALL ORDERIDS CONTAINING THE SPECIFIC ANCHOR Product FOR A SKU LIST 
    SELECT DISTINCT OpOrID, OpSoID, OpPrSKU, OpQty, ClID 
    INTO #TempB 
    FROM tblorder op (NOLOCK) 
    INNER JOIN tblProduct m (NOLOCK) ON m.prsku = op.opprsku 
    INNER JOIN tblproclass c (NOLOCK) ON c.prsku = op.opprsku 
    INNER JOIN tblpayment orp (NOLOCK) ON op.oporid = orp.PyOrID 
    WHERE orp.PyDateNew BETWEEN @startdate AND @enddate 
        AND opsoid = @store 
        AND op.opprsku = @vsku 
        AND OpCancelled = 0 

    ------TEMPC: SELECT MUTUAL ORID--------------------------------------------- 
    SELECT DISTINCT a.OpOrID, a.OpSoID 
    INTO #TempC 
    FROM #TempA a 
    INNER JOIN #TempB b (NOLOCK) ON a.OpOrID = b.OpOrID 

    -----------------CALCULATION FOR Attachment Rate---------------------------- 
    INSERT INTO #Output (BaseProduct, RelatedProduct, Cross-SellRate)
    SELECT 
        @vsku as 'Base SKU', 
        @sku as 'Related SKU', 
        CAST(CAST(((CAST((SELECT COUNT(DISTINCT OpOrID) FROM #TempC) as float)) /
               CAST((SELECT COUNT(DISTINCT OpOrID) FROM #TempB) as float)*100) as decimal(18,3)) as varchar(5)) + ' %' AS 'Cross-Sell Rate'

    Drop table #TempB 
    Drop table #TempC 

    FETCH NEXT FROM X into @vsku 
End 

Close X 
Deallocate X 

-----Retrieve 8k Rows of Base Product, Related Product and Attachment Rate-- 
Select * from #output 
drop table #TempA 
drop table #output 

关键修复点说明 #

1. 调整了INSERT INTO #Output的语句顺序，先写INSERT目标表，再写SELECT查询结果
2. 把#TempB查询中的p.opprsku全部替换为正确的别名op.opprsku
3. 在计算Cross-Sell Rate时，使用COUNT(DISTINCT OpOrID)确保统计的是唯一订单数，避免重复订单数据影响计算结果

优化建议：替换游标为集合操作（大幅提升性能） #

游标循环8000次的效率极低，推荐使用集合式查询一次性完成所有计算，彻底避免循环开销。以下是无游标优化版本：

-----------------------DECLARE VARIABLES------------------------------------ 
DECLARE @brand int 
SET @brand = 1 
DECLARE @store int 
SET @store = 01920 
DECLARE @sku nvarchar(max) 
SET @sku = 'xxx' -- Related Product
DECLARE @startdate datetime 
SET @startdate = '2018-05-09' --Set Start Date 
DECLARE @enddate datetime 
SET @enddate = '2018-05-22' -- Set End Date 

-- 先获取所有包含Related Product的订单ID
WITH RelatedOrders AS (
    SELECT DISTINCT OpOrID 
    FROM tblOrder op (NOLOCK) 
    JOIN tblpayment orp (NOLOCK) ON op.oporid = orp.PyOrID 
    WHERE orp.PyDateNew BETWEEN @startdate AND @enddate 
        AND op.opprsku = @sku 
        AND op.opcancelled = 0
),
-- 获取所有Base Product对应的订单ID
BaseOrders AS (
    SELECT 
        csd.[Base Product] AS BaseProduct,
        op.OpOrID
    FROM tblCrossSellData csd
    JOIN tblorder op (NOLOCK) ON op.opprsku = csd.[Base Product]
    JOIN tblpayment orp (NOLOCK) ON op.oporid = orp.PyOrID
    WHERE orp.PyDateNew BETWEEN @startdate AND @enddate 
        AND op.opsoid = @store 
        AND op.OpCancelled = 0
    GROUP BY csd.[Base Product], op.OpOrID
)
-- 计算每个Base Product的Cross-Sell Rate
SELECT 
    bo.BaseProduct,
    @sku AS RelatedProduct,
    CAST(
        CAST(
            (CAST(COUNT(DISTINCT CASE WHEN ro.OpOrID IS NOT NULL THEN bo.OpOrID END) AS FLOAT) / 
             CAST(COUNT(DISTINCT bo.OpOrID) AS FLOAT)) * 100 
        AS DECIMAL(18,3)
    ) AS VARCHAR(5)) + ' %' AS Cross-SellRate
FROM BaseOrders bo
LEFT JOIN RelatedOrders ro ON bo.OpOrID = ro.OpOrID
GROUP BY bo.BaseProduct
ORDER BY bo.BaseProduct;

这个版本通过CTE一次性完成所有数据的关联和计算，性能会比游标版本提升数倍，尤其适合处理8000个Base Product的场景。

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