修正:
select max(0x1&x)+max(0x10&x)+max(0x100&x)+……max(0x10000000&x) as x ,--对每个位求得是否有1 ,如果是tinyint 则写到 0x10000000(8位),smallint 到 0x1000000000000000(16位),int 到 0x1000000000000000000000000(32 位)
y from tb_test
group by y
对于整型数据,最高位为符号位,所以用 + 并不严密,另 位运算比 加法运算 好象会更好(?),因此改成:
select
max(0x1 & x) | max(0x10 & x) | max(0x100 &x ) | ……max(0x10000000 & x) as x ,y from tb_test
group by y
要更科学。
--测试数据
create table Z(X int,Y varchar(10))
insert Z select 127,'A'
union all select 1,'B'
union all select 12,'A'
go
--统计
select id=identity(int),X,Y into #t from Z order by Y
declare @Y varchar(10),@i int
update #t set @i=case @Y when Y then @i|X else X end
,X=@i,@Y=Y
select a.X,a.Y
from #t a,(select id=max(id) from #t group by Y)b
where a.id=b.id
如果只有两个:
select in(x) | max(x) as x,y from tb_test
group by y
如果多个:
select max(0x1&x)+max(0x10&x)+max(0x100&x)+……max(0x10000000&x) as x ,--对每个位求得是否有1 ,如果是tinyint 则写到 0x10000000(8位),smallint 到 0x1000000000000000(16位),int 到 0x1000000000000000000000000(32 位)
y from tb_test
group by y
--暂时没发现一句话的第3种写法