如何在 SQL 中翻转随机位

发布于 2024-11-15 22:17:26 字数 284 浏览 1 评论 0原文

出于测试目的，我想通过将列中的位设置为随机值来更新表。

update [Planned] 
set [IsPlannable] = 1 * rand(cast(cast(newid() as binary(8)) as int))
WHERE [ComputerID] > 100

它似乎确实按其应有的方式工作，但不是我想要的方式。我猜问题是结果大多数时候都会高于 1。

如何翻转随机位为随机值？

原文

For testing purposes I want to update a table by setting bits in colums to a random value.

update [Planned] 
set [IsPlannable] = 1 * rand(cast(cast(newid() as binary(8)) as int))
WHERE [ComputerID] > 100

It does seem to work as it should but not the way I want it. I guess the problem is the result will most of the time be higher than 1.

How can I flip random bits to random values?

分享到QQ

分享到微博

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

发布评论

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

肥爪爪 2024-11-22 22:17:26

1 * 仍然产生一个小数 &鉴于 cast(0.1 as bit) 将产生 1，cast(0.9 as bit) 更新也将全部设置为 1

。

update Planned set IsPlannable = case when rand(cast(newid() as binary(8))) < 0.5 then 0 else 1 end

The 1 * is still yielding a fractional number & given that cast(0.1 as bit) will yeild 1 as will cast(0.9 as bit) the updates are all set to 1.

You could;

update Planned set IsPlannable = case when rand(cast(newid() as binary(8))) < 0.5 then 0 else 1 end

回复收藏 0 原文

昇り龍 2024-11-22 22:17:26

根据您必须使用的位字段数量，您可以使用如下方式生成所有可能的设置：

with test as (
    select 0 as myId, cast(0 as bit) col1, cast(0 as bit) col2, cast(0 as bit) col3
    union all
    select myId + 1, 
        case when myId & 1 = 1 then cast(1 as bit) else cast(0 as bit) end,
        case when myId & 2 = 2 then cast(1 as bit) else cast(0 as bit) end,
        case when myId & 4 = 4 then cast(1 as bit) else cast(0 as bit) end
        from test
        where myId<100
)
select distinct col1, col2, col3 from test

Depending upon how many bit fields you have to use, you can generate all of the possible settings using something like this:

with test as (
    select 0 as myId, cast(0 as bit) col1, cast(0 as bit) col2, cast(0 as bit) col3
    union all
    select myId + 1, 
        case when myId & 1 = 1 then cast(1 as bit) else cast(0 as bit) end,
        case when myId & 2 = 2 then cast(1 as bit) else cast(0 as bit) end,
        case when myId & 4 = 4 then cast(1 as bit) else cast(0 as bit) end
        from test
        where myId<100
)
select distinct col1, col2, col3 from test

回复收藏 0 原文