Совокупное побитовое ИЛИ в подзапросе
Учитывая следующую таблицу:
CREATE TABLE BitValues ( n int )
Можно ли вычислить побитовое ИЛИ для n для всех строк внутри подзапроса? Например, если BitValues содержит эти 4 строки:
+---+
| n |
+---+
| 1 |
| 2 |
| 4 |
| 3 |
+---+
Я бы ожидал, что подзапрос вернется 7. Есть ли способ сделать это inline, без создания UDF?
Ответы
Ответ 1
WITH Bits
AS ( SELECT 1 AS BitMask
UNION ALL
SELECT 2
UNION ALL
SELECT 4
UNION ALL
SELECT 8
UNION ALL
SELECT 16
)
SELECT SUM(DISTINCT BitMask)
FROM ( SELECT 1 AS n
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
) AS t
JOIN Bits ON t.n & Bits.BitMask > 0
Ответ 2
Простое решение, представляющее собой сочетание решений @AlexKuznetsov и @Andomar.
Бит-маска генерируется рекурсивным общим выражением таблицы, но более простым способом, чем в решении @Andomar.
Затем биты суммируются так же, как в решении @AlexKuznetsov.
В этом примере я предполагаю, что требуется 16-битная маска, следовательно, предел 65536. Вы можете указать маску N-бит, изменив значение 65536 на 2 ^ N.
WITH Bits AS
(
SELECT 1 BitMask
UNION ALL
SELECT 2 * BitMask FROM Bits WHERE BitMask < 65536 -- recursion
)
SELECT SUM(DISTINCT BitMask)
FROM
(SELECT 1 n
UNION ALL
SELECT 2 n
UNION ALL
SELECT 4 n
UNION ALL
SELECT 3 n) t
INNER JOIN Bits ON t.n & Bits.BitMask > 0
Ответ 3
Подготовка:
if object_id(N'tempdb..#t', N'U') is not null drop table #t;
create table #t ( n int );
insert into #t values (1), (2), (4), (3);
Решение:
select max(n & 8) + max(n & 4) + max(n & 2) + max(n & 1) from #t;
Ответ 4
Вы можете использовать переменную и выполнять "поразрядное" или "|" для каждой строки:
declare @t table (n int)
insert @t select 1 union select 2 union select 4
declare @i int
set @i = 0
select @i = @i | n
from @t
select @i
Отпечатает 7. Обратите внимание, что назначение переменных в select не поддерживается официально.
В более строго SQL-способе вы можете создать таблицу с одной строкой для каждого бита. Эта таблица будет иметь 31 строку, так как 32-й бит является отрицательным целым числом. В этом примере для создания этой таблицы используется рекурсивный CTE:
declare @t table (n int)
insert @t select 1 union select 2 union select 3
; with bits(nr, pow) as
(
select 1
, 1
union all
select nr + 1
, pow * 2
from bits
where nr <= 30
)
select sum(b.pow)
from bits b
where exists
(
select *
from @t t
where b.pow & t.n > 0
)
Это суммирует биты, где установлен бит в исходной таблице.
Ответ 5
Я вижу, что этот пост довольно старый, и есть полезные ответы, но это довольно сумасшедший метод прямой...
Select
SUM(DISTINCT(n & 0x01)) +
SUM(DISTINCT(n & 0x02)) +
SUM(DISTINCT(n & 0x04))
as OrN
From BitValues
Ответ 6
Я попытался использовать функцию COALESCE, и она работает, например:
DECLARE @nOrTotal INT
SELECT @nOrTotal = COALESCE(@nOrTotal, 0) | nValor
FROM (SELECT 1 nValor
UNION
SELECT 2
UNION
SELECT 2) t
SELECT @nOrTotal
>> Result: 3
Ответ 7
Это альтернатива без WITH (ура!!!):
select sum(distinct isnull(n & BitMask, 0)) as resultvalue
from
(
SELECT 1 AS n
UNION ALL
SELECT 2
UNION ALL
SELECT 4
UNION ALL
SELECT 3
) t
INNER JOIN (SELECT 0 BitMask union all SELECT 1 union all SELECT 2 union all SELECT 4 union all SELECT 8 union all SELECT 16 union all SELECT 32 union all SELECT 64 union all SELECT 128 union all SELECT 256 union all SELECT 512 union all SELECT 1024 union all SELECT 2048 union all SELECT 4096 union all SELECT 8192 union all SELECT 16384 union all SELECT 32768 union all SELECT 65536) Bits -- = SELECT POWER(2, 16)
ON n & BitMask = BitMask;
Также рассмотрим пример группы:
-- Setup temp table to produce an example --
create table #BitValues
(
id int identity(1,1)
,value int
,groupby varchar(10)
)
insert into #BitValues
SELECT 1 AS value, 'apples'
UNION ALL
SELECT 2, 'apples'
UNION ALL
SELECT 4, 'apples'
UNION ALL
SELECT 3, 'apples'
-- Bit operation: --
select groupby, sum(distinct isnull(value & BitMask, 0)) as tempvalue
from #BitValues
INNER JOIN (SELECT 0 BitMask union all SELECT 1 union all SELECT 2 union all SELECT 4 union all SELECT 8 union all SELECT 16 union all SELECT 32 union all SELECT 64 union all SELECT 128 union all SELECT 256 union all SELECT 512 union all SELECT 1024 union all SELECT 2048 union all SELECT 4096 union all SELECT 8192 union all SELECT 16384 union all SELECT 32768 union all SELECT 65536) Bits -- = SELECT POWER(2, 16)
ON value & BitMask = BitMask
group by groupby
Первый пример должен быть медленнее, чем WITH. Однако, когда вы используете GroupBy с некоторыми другими данными, запросы во многом одинаковы по затратам.
Другой способ сделать это -
select
groupby
,max(case when n & 1 = 1 then 1 else 0 end)
+
max(case when n & 2 = 2 then 2 else 0 end)
+
max(case when n & 4 = 4 then 4 else 0 end)
+
max(case when n & 8 = 8 then 8 else 0 end)
+
max(case when n & 16 = 16 then 16 else 0 end)
+
max(case when n & 32 = 32 then 32 else 0 end)
+
max(case when n & 64 = 64 then 64 else 0 end)
+
max(case when n & 128 = 128 then 128 else 0 end)
+
max(case when n & 256 = 256 then 256 else 0 end)
+
max(case when n & 512 = 512 then 512 else 0 end)
+
max(case when n & 1024 = 1024 then 1024 else 0 end)
as NewDNC
from #BitValues
group by groupby;
Это немного хуже из-за повторения в коде, немного более читаемого и подобного в стоимости исполнения.
Ответ 8
Вы ищете что-то вроде этого?
РЕДАКТИРОВАТЬ. Как отмечалось в других комментариях, этот ответ основывался на предположении, что таблица BitValues будет содержать только силу 2. Я попытался прочитать между строками вопроса и сделать вывод о том, для встроенного подзапроса.
declare @BitValues table (
n int
)
declare @TestTable table (
id int identity,
name char(10),
BitMappedColumn int
)
insert into @BitValues (n)
select 1 union all select 2 union all select 4
insert into @TestTable
(name, BitMappedColumn)
select 'Joe', 5 union all select 'Bob', 8
select t.id, t.name, t.BitMappedColumn
from @TestTable t
inner join (select SUM(n) as BitMask from @BitValues) b
on t.BitMappedColumn & b.BitMask <> 0
Ответ 9
Для меня это лучшее решение.
declare @res int
set @res=0
SELECT @[email protected]|t.n
FROM ( SELECT 1 AS n
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
) AS t
Ответ 10
Лучшим выбором для читаемого и повторно используемого решения было бы написать настраиваемое агрегирование CLR для выполнения поразрядного или. Учебник по созданию этого типа операции можно найти здесь: http://msdn.microsoft.com/en-us/library/91e6taax(VS.80).aspx
