如何获取嵌套大小写参数以返回值

Question

我想要这个案例参数嵌套中的 2 个值，这样我就可以将它们插入到结果表中以检查匹配项，然后我更新#game表中的结果。我已将更新作为选择语句的一部分。我得到

无效列错误

在standard_matches和bonus_match.然后，我尝试在 case 参数之后获取一个值，并出现相同的错误。这是案例参数代码。

BEGIN   -- 
    -- update #game set gwn = standard_matches ,gsn = bonus_match from 
-- where gameno = 1 
    --  from
        SELECT
            @Ball1 AS Ball1,
            @Ball2 AS Ball2,
            @Ball3 AS Ball3,
            @Ball4 AS Ball4,
            @Ball5 AS Ball5,
            @Ball6 AS Ball6,
            standard_matches
            bonus_match
        from #draws
    CROSS APPLY 
        ( SELECT
            CASE WHEN Winningball1 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 end + 
            CASE WHEN Winningball2 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 ENd +
            CASE WHEN Winningball3 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END +
            CASE WHEN Winningball4 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END +
            CASE WHEN Winningball5 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END +
            CASE WHEN Winningball6 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END as standard_matches,
            CASE WHEN Bonusball1 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END +
            CASE WHEN Bonusball2 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END as bonus_match
        ) AS ca1 
        set @sm = standard_matches
        set @bm = bonus_match
        update #game set gwn = @sm ,gsn = @bm where gno = 1
end

Answer 1

我想这就是你想要的？

; WITH ca1 AS
(
    SELECT
            standard_matches,  -- missing comma
            bonus_match
        from #draws
    CROSS APPLY 
        ( 
      SELECT
            CASE WHEN Winningball1 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 end + 
            CASE WHEN Winningball2 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 ENd +
            CASE WHEN Winningball3 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END +
            CASE WHEN Winningball4 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END +
            CASE WHEN Winningball5 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END +
            CASE WHEN Winningball6 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END as standard_matches,
            CASE WHEN Bonusball1 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END +
            CASE WHEN Bonusball2 IN (@Ball1, @Ball2, @Ball3, @Ball4, @Ball5, @Ball6) THEN 1 END as bonus_match
        ) a
)
UPDATE  g
SET gwn = ca1.standard_matches,
    gsn = ca1.bonus_match
FROM    #game g
CROSS JOIN ca1
WHERE   g.gno = 1