这个 CASE 表达式如何到达 ELSE 子句? [英] How does this CASE expression reach the ELSE clause?

问题描述

我需要将一些测试数据加载到我的 Account 表的 Channel 字段中.Channel 可以是 10 个不同值之一，所以我想我会使用 CASE 表达式和 ABS(CHECKSUM(NewId())) % 随机分配值之一10 像这样:

I need to load some test data into the Channel field on my Account table. The Channel can be one of 10 different values, so I thought I'd randomly assign the Channel one of the values using a CASE expression along with ABS(CHECKSUM(NewId())) % 10 like so:

SELECT 
    id,
    name,
    Channel = 
      CASE ABS(CHECKSUM(NewId())) % 10
        WHEN 0 THEN 'Baby Only'
        WHEN 1 THEN 'Club'
        WHEN 2 THEN 'Drug'
        WHEN 3 THEN 'Food'
        WHEN 4 THEN 'Internet'
        WHEN 5 THEN 'Liquidators'
        WHEN 6 THEN 'Mass'
        WHEN 7 THEN 'Military'
        WHEN 8 THEN 'Other'
        WHEN 9 THEN 'Speciality'
        ELSE '*NONE*'            -- How is this ever getting reached?
      END
FROM 
    retailshelf_nil...account A

因为我使用的是模 10，所以我认为唯一可能的值应该是 0-9.但是当我运行上面的代码时，我发现确实达到了 ELSE 子句，并且我的数据在某些记录上出现了NONE"，如下所示:

Since I'm using modulo 10 I thought the only possible values should be 0-9. But when I run the above code, I'm finding that the ELSE clause is indeed being reached and that my data is coming up with 'NONE' on some records as shown:

id                  name    Channel
001L000000KpgFqIAJ  Acct1   *NONE*
001L000000KpgFrIAJ  Acct2   Mass
001L000000KpgFsIAJ  Acct3   Club
001L000000KpgFtIAJ  Acct4   *NONE*
001L000000KpgFuIAJ  Acct5   Baby Only
001L000000KpgFvIAJ  Acct6   *NONE*
001L000000KpgFwIAJ  Acct7   Mass

有人可以解释一下我犯了什么逻辑错误导致 ELSE 子句可以达到吗?

当我运行一个简单的测试来生成随机数时:

When I run a simple test to just generate the random number like so:

SELECT
    RadomNum = ABS(CHECKSUM(NewId())) % 10
FROM 
    retailshelf_nil...account A
ORDER BY 
    1

生成的所有数字都按预期从 0 到 9，那么第一个 SQL 有什么不同?

All the numbers generated are from 0-9 as expected, so what's different about this first SQL?

是否有解决方法可以确保永远不会到达 ELSE?

And is there a workaround to ensure that ELSE is never reached?

推荐答案

查询的书写形式扩展为:

The written form of the query is expanded to:

Channel = 
      CASE
        WHEN ABS(CHECKSUM(NewId())) % 10 = 0 THEN 'Baby Only'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 1 THEN 'Club'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 2 THEN 'Drug'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 3 THEN 'Food'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 4 THEN 'Internet'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 5 THEN 'Liquidators'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 6 THEN 'Mass'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 7 THEN 'Military'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 8 THEN 'Other'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 9 THEN 'Speciality'
        ELSE '*NONE*'            -- How is this ever getting reached?
      END

NEWID 的新值用于每个测试.

A new value for NEWID is used in each test.

这篇关于这个 CASE 表达式如何到达 ELSE 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！