没理由用0/1字符串来表示的,查询的时候玩死人都有可能--> 借鉴就完全借鉴,用一个整数表示,31个二进制位刚好够用: select day = number, bitmap = power(2,number-1) from master.dbo.spt_values where type = 'P' and number between 1 and 31 /* day bitmap ----------- ----------- 1 1 2 2 3 4 4 8 5 16 6 32 7 64 8 128 9 256 10 512 11 1024 12 2048 13 4096 14 8192 15 16384 16 32768 17 65536 18 131072 19 262144 20 524288 21 1048576 22 2097152 23 4194304 24 8388608 25 16777216 26 33554432 27 67108864 28 134217728 29 268435456 30 536870912 31 1073741824 */--> 比如4,5,6用整数56表示,查询也很简单(可以建一个静态的bitmap表cross join查询): select * from ( select day = number, bitmap = power(2,number-1) from master.dbo.spt_values where type = 'P' and number between 1 and 31 ) t where bitmap & 56 > 0 /* day bitmap ----------- ----------- 4 8 5 16 6 32 */
也许是这样create table tb(id int,m int,d binary(4)) go insert tb select 1,1,0x00000001 union select 1,2,0x00000002 union select 1,3,0x00000003 union select 1,5,0xFFFFFFFFselect * from tb where d&2=2
没理由用0/1字符串来表示的,查询的时候玩死人都有可能--> 借鉴就完全借鉴,用一个整数表示,31个二进制位刚好够用:
select day = number, bitmap = power(2,number-1) from master.dbo.spt_values where type = 'P' and number between 1 and 31
/*
day bitmap
----------- -----------
1 1
2 2
3 4
4 8
5 16
6 32
7 64
8 128
9 256
10 512
11 1024
12 2048
13 4096
14 8192
15 16384
16 32768
17 65536
18 131072
19 262144
20 524288
21 1048576
22 2097152
23 4194304
24 8388608
25 16777216
26 33554432
27 67108864
28 134217728
29 268435456
30 536870912
31 1073741824
*/--> 比如4,5,6用整数56表示,查询也很简单(可以建一个静态的bitmap表cross join查询):
select * from
(
select day = number, bitmap = power(2,number-1) from master.dbo.spt_values where type = 'P' and number between 1 and 31
) t
where bitmap & 56 > 0
/*
day bitmap
----------- -----------
4 8
5 16
6 32
*/
go
insert tb
select 1,1,0x00000001 union
select 1,2,0x00000002 union
select 1,3,0x00000003 union
select 1,5,0xFFFFFFFFselect * from tb where d&2=2
跟之前在程序中用位移累加有异曲同工之妙,但因为sqlserver中没有位移运算所以一直都是在程序中计算