日期:2014-05-18 浏览次数:20496 次
--> 测试数据:[tbl] if object_id('[tbl]') is not null drop table [tbl] create table [tbl]([VALUE] int,[DESCRIPTION] varchar(30)) insert [tbl] select 1,'相当于shift 1 Enabled' union all select 2,'相当于Shift 2 Enabled' union all select 3,'相当于Shift 1&2 Enabled' union all select 4,'相当于Shift 3 Enabled]' union all select 5,'相当于Shift 1&3 Enabled]' union all select 6,'相当于Shift 2&3 Enabled]' union all select 7,'相当于Shift 1,2&3 Enabled' --> 测试数据:[shift] if object_id('[shift]') is not null drop table [shift] create table [shift]([id] int,[shift_nbr] int) insert [shift] select 1,1 union all select 2,2 union all select 3,3 union all select 4,1 union all select 5,2 union all select 6,3 select * from [shift] where CHARINDEX(ltrim([shift_nbr]),(select [DESCRIPTION] from tbl where [VALUE]=4))>0 /* id shift_nbr 3 3 6 3 */ select * from [shift] where CHARINDEX(ltrim([shift_nbr]),(select [DESCRIPTION] from tbl where [VALUE]=6))>0 /* id shift_nbr 2 2 3 3 5 2 6 3 */ select * from [shift] where CHARINDEX(ltrim([shift_nbr]),(select [DESCRIPTION] from tbl where [VALUE]=7))>0 /* id shift_nbr 1 1 2 2 3 3 4 1 5 2 6 3 */
------解决方案--------------------
不是相当于做‘位’的操作,就是位操作。
--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(id int, shift_nbr int) insert into # select 1, 1 union all select 2, 2 union all select 3, 3 union all select 4, 1 union all select 5, 2 union all select 6, 3 declare @shift_cntrl_nbr int = 7 select * from # where @shift_cntrl_nbr & power(2, shift_nbr-1)>0 /* id shift_nbr ----------- ----------- 1 1 2 2 3 3 4 1 5 2 6 3 */