如何通过标志bigint列对tsql进行分组 [英] How do I group tsql set by a flag bigint column

问题描述

我正在开展一个搜索功能效率至关重要的项目。



我有几个标志列（如c＃中的枚举标志）。搜索这些数据的速度非常快（往返时间为3毫秒），但我现在需要进行群组计数。



所以，我有项目'A'包含红色（1），白色（8）和蓝色（64），因此'颜色'列保存数字73.



要搜索我可以使用此

Hi,

I'm working on a project where efficiency of the search functionality is critical.

I have several flag columns (like enum flags in c#). Searching on this data is super fast (3 milliseconds round trip) but I've come-a-cropper now I have to perform group counts.

So, I have an item 'A' that contains Red (1), White (8) and blue (64) so the 'Colours' column holds the number 73.

To search I can search for items with red with this

Declare @colour int
set @colour = 1
Select * from Items where (Colour & @colour) > 0

效果很好。现在我必须分组（也超快）



所以如果我总共有8个项目，5个包含红色，3个包含白色，7个包含蓝色，结果会看起来像：

That works great. Now I have to group it (also super fast)

so if I have 8 items in total, 5 contain red, 3 contain white and 7 contain blue the results would look like:

Colour      Qty
1           5
8           3
64          7

（我不是我不得不担心这个名字）



所以：有什么方法可以把数字73按顺序分成小组吗？



（第2部分：如何将其转换为Linq to SQL？）



任何建议都将不胜感激

谢谢^ _ ^



我尝试过：



尝试在Linq写入登录但是忘记了它没有转换成TSQL。



我正在考虑废弃这个专栏并将其转回连接表，但我真的不想第三次重新设计这个东西> _<

( I don't have to worry about the name )

So: Is there any way I can take the number 73 and bitwise split it into groups?

(Part 2: How do I translate that into Linq to SQL?)

Any advise would be appreciated
Thanks ^_^

What I have tried:

Tried writing the login in Linq but forgot that it would have no translation into TSQL.

I'm thinking about scrapping this column and turning it back into a linking table, but I really don't want to have to redesign this thing for a third time >_<

推荐答案

好的 - 我想我我找到了最好的解决方案：



我尝试用cte查看视图：

Ok - I think I've worked out the best solution:

I tries a view with a cte:

with cte as (
	select cast(1 as bigint) as flag, 1 pow
	union all
	select POWER(cast(2 as bigint),pow), pow + 1
	from cte
	where flag < POWER(cast(2 as bigint),62)
)
, cte2 as (
	select flag from cte
	union select -9223372036854775808
)

但这太慢了所以现在我把它变成了静态表。我加入了一个按位'&'：

but that was too slow so now I have made it into a static table. I join with a bitwise '&':

select Flag, Count(*)
From FlagValues fv 
inner join Items i on (fv.Flag & i.Colour)

快得多^ _ ^

Much faster ^_^

SELECT VAL, COUNT(*)
FROM (
	SELECT 
		COLOR AS COLOR_VAL, 
		COLOR & 1 AS [1],
		COLOR & 2 AS [2],
		COLOR & 4 AS [4],
		COLOR & 8 AS [8],
		COLOR & 16 AS [16],
		COLOR & 32 AS [32],
		COLOR & 64 AS [64],
		COLOR & 128 AS [128]
	FROM (SELECT 73 AS COLOR UNION SELECT 53 UNION SELECT 91) AS COLORS) AS SUMMARY
UNPIVOT  
   (VAL FOR COLOR IN   
      ([1], [2], [4], [8], [16], [32], [64], [128])  
) AS UP
WHERE VAL > 0 
GROUP BY VAL

粗体部分是您实际选择颜色值的地方......

The bold part is where you actual select for your color values comes in...

这篇关于如何通过标志bigint列对tsql进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！