使用单个 SELECT 语句和 CASE 将表值分配给多个变量？

发布于 2024-08-26 16:36:27 字数 912 浏览 5 评论 0原文

我正在尝试使用具有多个 CASE 语句的单个 SELECT 将查找表中包含的值分配给多个变量。

该表是一个包含两列的查找表，如下所示：

[GreekAlphabetastic]

  SystemID    Descriptor
  --------    ----------
  1           Alpha
  2           Beta
  3           Epsilon

这是我的语法：

SELECT 

    @VariableTheFirst = 
        CASE
            WHEN myField = 'Alpha' THEN tbl.SystemID
        END,

    @VariableTheSecond = 
        CASE
            WHEN myField = 'Beta' THEN tbl.SystemID
        END,

    @VariableTheThird = 
        CASE
            WHEN myField = 'Epsilon' THEN tbl.SystemID
        END

FROM GreekAlphabetastic tbl

但是，当我在执行此语句后检查变量时，我希望为每个变量分配适当的值，但只有最后已分配值。

SELECT 
    @VariableTheFirst AS First, 
    @VariableTheSecond AS Second, 
    @VariableTheThird AS Third

Results:

    First    Second    Third
    NULL     NULL      3

我做错了什么？

原文

I'm trying to assign values contained in a lookup table to multiple variables by using a single SELECT having multiple CASE statements.

The table is a lookup table with two columns like so:

[GreekAlphabetastic]

  SystemID    Descriptor
  --------    ----------
  1           Alpha
  2           Beta
  3           Epsilon

This is my syntax:

SELECT 

    @VariableTheFirst = 
        CASE
            WHEN myField = 'Alpha' THEN tbl.SystemID
        END,

    @VariableTheSecond = 
        CASE
            WHEN myField = 'Beta' THEN tbl.SystemID
        END,

    @VariableTheThird = 
        CASE
            WHEN myField = 'Epsilon' THEN tbl.SystemID
        END

FROM GreekAlphabetastic tbl

However, when I check the variables after this statement executes, I expected each to be assigned the appropriate value, but instead only the last has a value assigned.

SELECT 
    @VariableTheFirst AS First, 
    @VariableTheSecond AS Second, 
    @VariableTheThird AS Third

Results:

    First    Second    Third
    NULL     NULL      3

What am I doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最近可好 2024-09-02 16:36:27

当从 SELECT 中对局部变量进行赋值时，只有最后处理的行才会影响变量。对于第三行，CASE myField = 'Alpha' 和 CASE myField = 'Beta' 为 false，并且变量设置为 NULL。 CASE myField = 'Epsilon' 为 true，并且分配了 @VariableTheThird 3.

如果您希望此功能正常工作，请执行以下操作：

SELECT @VariableTheFirst = tbl.SystemID WHERE myField = 'Alpha'
SELECT @VariableTheSecond = tbl.SystemID WHERE myField = 'Beta'
SELECT @VariableTheThird = tbl.SystemID WHERE myField = 'Epsilon'

when making assignments to local variables from a SELECT, only the last row processed will affect the variables. for the third row, the CASE myField = 'Alpha' and CASE myField = 'Beta' are false and the variables are set to NULL. The CASE myField = 'Epsilon' is true and @VariableTheThird is assigned 3.

if you want this to work do this:

SELECT @VariableTheFirst = tbl.SystemID WHERE myField = 'Alpha'
SELECT @VariableTheSecond = tbl.SystemID WHERE myField = 'Beta'
SELECT @VariableTheThird = tbl.SystemID WHERE myField = 'Epsilon'

回复收藏 0 原文

[旋木] 2024-09-02 16:36:27

前 2 个变量在分配后将重置为 null。即，当它到达 Epsilon 记录时，前 2 个变量将被分配为 null，因为在 CASE 语句中没有任何内容可以阻止这种情况。

所以，试试这个：

SELECT     
    @VariableTheFirst = 
        CASE
            WHEN Descriptor = 'Alpha' THEN tbl.SystemID
            ELSE @VariableTheFirst
        END,

    @VariableTheSecond = 
        CASE
            WHEN Descriptor = 'Beta' THEN tbl.SystemID
            ELSE @VariableTheSecond
        END,

    @VariableTheThird = 
        CASE
            WHEN Descriptor = 'Epsilon' THEN tbl.SystemID
            ELSE @VariableTheThird
        END
FROM GreekAlphabetastic tbl

The first 2 variables are being reset to null after being assigned. i.e. when it hits the Epsilon record, the first 2 variables are being assigned to null as there is nothing to prevent that in the CASE statement.

So, try this:

SELECT     
    @VariableTheFirst = 
        CASE
            WHEN Descriptor = 'Alpha' THEN tbl.SystemID
            ELSE @VariableTheFirst
        END,

    @VariableTheSecond = 
        CASE
            WHEN Descriptor = 'Beta' THEN tbl.SystemID
            ELSE @VariableTheSecond
        END,

    @VariableTheThird = 
        CASE
            WHEN Descriptor = 'Epsilon' THEN tbl.SystemID
            ELSE @VariableTheThird
        END
FROM GreekAlphabetastic tbl

回复收藏 0 原文

酒废 2024-09-02 16:36:27

SELECT     
        CASE
            WHEN Descriptor = 'Alpha' THEN @VariableTheFirst = isnull(@VariableTheFirst,tbl.SystemID)
            WHEN Descriptor = 'Beta' THEN @VariableTheSecond = isnull(@VariableTheSecond,tbl.SystemID)
            WHEN Descriptor = 'Epsilon' THEN @VariableTheThird = isnull(@VariableTheThird,tbl.SystemID)
        END
FROM GreekAlphabetastic tbl

SELECT     
        CASE
            WHEN Descriptor = 'Alpha' THEN @VariableTheFirst = isnull(@VariableTheFirst,tbl.SystemID)
            WHEN Descriptor = 'Beta' THEN @VariableTheSecond = isnull(@VariableTheSecond,tbl.SystemID)
            WHEN Descriptor = 'Epsilon' THEN @VariableTheThird = isnull(@VariableTheThird,tbl.SystemID)
        END
FROM GreekAlphabetastic tbl

回复收藏 0 原文

~没有更多了~