一周中的几天存储在一个字段中 [英] Multiple days of week stored in one field

问题描述

关于特定整数字段存储数据的方式，我遇到了一些障碍。

I've encountered a bit of a mental roadblock regarding the way a specific integer field is storing data.

具体来说，有一列包含整数的范围是1-127;每个整数代表一周中不同天的组合。例如：星期一= 2 ^ 0或1，星期二= 2 ^ 2或2，星期三= 2 ^ 3或8；可以选择加法，星期一+星期二= 3。

Specifically, there is a column with integers that range from 1 - 127; each integer represents a combination of different days of the week. For example: Monday = 2^0 or 1, Tuesday = 2^2 or 2, Wednesday = 2^3 or 8; with the option of addition, Monday + Tuesday = 3.

我已经可以使用此处。但是，当两天加在一起时（例如，星期一+星期二= 3），该特定示例将不起作用。谁能指出我正确的方向？

I've been able to extract the date values partially using the example found here. However, that particular example does not work when two days get added together (eg. Monday + Tuesday = 3). Can anyone point me in the right direction?

仅供参考，我正在使用SQL Server 2008 R2。我很抱歉，如果以前已经发布过，我看了一下，但是找不到其他发布。

FYI, I am using SQL Server 2008 R2. My apologies if this has been posted before, I took a look but was unable to find any other postings.

推荐答案

这里是一个很好地阅读，上面有简单的例子。

Here's a good read on it with clear simple examples.

为了完整起见，这是您在一周中的每一天查看的内容。

For the sake of completeness, here is what you're looking at broken down into columns for each day of the week.

DECLARE @bitwise TABLE (someValue TINYINT)

INSERT INTO @bitwise (someValue)
SELECT 1 UNION
SELECT 5 UNION
SELECT 127

SELECT someValue, CASE WHEN (1&someValue)=1 THEN 'SUNDAY' END
                , CASE WHEN (2&someValue)=2 THEN 'MONDAY' END
                , CASE WHEN (4&someValue)=4 THEN 'TUESDAY' END
                , CASE WHEN (8&someValue)=8 THEN 'WEDNESDAY' END
                , CASE WHEN (16&someValue)=16 THEN 'THURSDAY' END
                , CASE WHEN (32&someValue)=32 THEN 'FRIDAY' END
                , CASE WHEN (64&someValue)=64 THEN 'SATURDAY' END

FROM @bitwise

这篇关于一周中的几天存储在一个字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！