sql - 从列中选择条目的SQL Server的SQL语句包含2的幂的和(SQL statement for SQL Server that Selects entry's from a column witch contains a value sum of power of 2)

Question

I wish to write an SQL statement for SQL Server that Selects entry's from a column witch contains a value sum of power of 2 (2^n), for instance I have TABLE xxx:

(我希望为SQL Server编写一条SQL语句，该语句从一列中选择条目，其中包含2的幂的总和（2 ^ n），例如，我具有表xxx：)

_________________________________________________________
| id | col_pow |col_pow=sum of element power of 2 (2^n) |
|----|---------|----------------------------------------|
| 1  | 11      |11  = 8+2+1   = 2^3 + 2^1 +2^0          |           
| 2  | 3       |3   = 2+1     = 2^1 + 2^0               |
| 3  | 514     |514 = 512+2   = 2^9 + 2^1               |
| 4  | 49      |49  = 32+16+1 = 2^5 + 2^4 +2^0          |
| 5  | 7       |7   = 4+2+1   = 2^2 + 2^1 +2^0          |
---------------------------------------------------------

EX.

(例如)

SELECT

(选择)

1) SELECT id TABLE xxx WHERE col_pow can_contains 4 (=2^2)

(1） SELECT id TABLE xxx WHERE col_pow can_contains 4 (=2^2))

• expected result:

  (预期结果：)

| id |
|----|
| 5  |

2) SELECT id TABLE xxx WHERE col_pow can_contains 2 (=2^1)

(2） SELECT id TABLE xxx WHERE col_pow can_contains 2 (=2^1))

• expected result:

  (预期结果：)

| id |
|----|
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |

3) SELECT id TABLE xxx WHERE col_pow can_contains 512(=2^9)

(3） SELECT id TABLE xxx WHERE col_pow can_contains 512(=2^9))

• expected result:

  (预期结果：)

| id |
|----|
| 3  |

How to write the 3 statements?

(如何写这3条语句？)

  ask by niac translate from so

Answer 1

If I understand you correctly, using Bitwise AND (&) may help here.

(如果我对您的理解正确，那么使用按位与（＆）可能会有所帮助。)

Table:

(表：)

CREATE TABLE #Data (
    id int,
    col_pow int
)
INSERT INTO #Data 
    (id, col_pow)
VALUES
    (1, 11),
    (2, 3),
    (3, 514),
    (4, 49),
    (5, 7)

Statement:

(声明：)

DECLARE @power int = 9
SELECT *
FROM #Data
WHERE (col_pow & POWER(2, @power)) = POWER(2, @power) 

Result:

(结果：)

id  col_pow
3   514

Calculations (for 2^9 ):

(计算（对于2^9 ）：)

-------|----------------|-----
A      | 0010 0000 0010 | 514
B      | 0010 0000 0000 | 512
-------|----------------|-----
A & B  | 0010 0000 0000 | 512