计算mysql中重叠的按位列

发布于 2024-10-30 01:06:06 字数 463 浏览 1 评论 0原文

我有一个表，其中有一个按位列，表示可以附加到条目的状态列表。每个条目可以选择多个状态（因此使用按位逻辑）。

我想做的是提取一个查询，该查询将告诉我每个状态有多少个整体（即每个位打开了多少次）。我遇到的困难是，当然存在重叠，因此 GROUP BY 或 DISTINCT 不会削减它（据我所知）。

举个例子，我们只有两个值 1 和 2。以及以下数据

Id | Status
1 | 1
2 | 1
3 | 2
4 | 3

现在，我想计算每个位有多少个条目，所以我想要将 3 值计入 1 和 2 总数的东西，输出类似这样的内容：

Bit | Count
1 | 3
2 | 2

到目前为止，我能得到的最接近的结果似乎是提取不同的值，然后使用 PHP 将具有多个条目的值添加到相应的计数中。显然，我想做一些更优雅的事情。

有什么想法吗？

原文

I have a table in which there is a bitwise column representing a list of statuses that can be attached to an entry. Each entry can have multiple statuses selected (hence the use of the bitwise logic).

What I'd like to do is pull a query that will tell me how many entires there are for each status (i.e. how many times each bit is turned on). The difficulty I have is that there is of course overlap so a GROUP BY or a DISTINCT is not going to cut it (as far as I can see).

As an example let's just have two values, 1 and 2. and the following data

Id | Status
1 | 1
2 | 1
3 | 2
4 | 3

Now, I want to count how many entries there are for each bit so I'd like something that counts that 3 value into both the 1 and 2 totals, outputting something like this:

Bit | Count
1 | 3
2 | 2

The closest I can get so far seems to be pulling out the distinct values and then adding those with multiple entries into their corresponding counts using PHP. Obviously, I'd like to do something a bit more elegant.

Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

生生漫 2024-11-06 01:06:06

根据需要扩展位表

select `bit`, count(*) `count`
from bitt s
inner join (select 1 `bit` union all
            select 2 union all
            select 3 union all
            select 4 union all
            select 5) bits on s.status & Pow(2,bits.`bit`-1)
group by bits.`bit`

Expand the bits table as required

select `bit`, count(*) `count`
from bitt s
inner join (select 1 `bit` union all
            select 2 union all
            select 3 union all
            select 4 union all
            select 5) bits on s.status & Pow(2,bits.`bit`-1)
group by bits.`bit`

回复收藏 0 原文

凝望流年 2024-11-06 01:06:06

你可以这样做：

SUM(IF(`Status`&1,1,0)) as `count1`,
SUM(IF(`Status`&2,1,0)) as `count2`,
SUM(IF(`Status`&4,1,0)) as `count4`

如果你想优化它，你仍然可以GROUP BY Status，但是你需要（一点）后处理来对 3 位得到的 8 行求和情况。

You could do:

SUM(IF(`Status`&1,1,0)) as `count1`,
SUM(IF(`Status`&2,1,0)) as `count2`,
SUM(IF(`Status`&4,1,0)) as `count4`

If you want to optimize it, you can still GROUP BY Status, but then you would need (a little) post-processing to sum the 8 rows you would get for a 3-bit situation.

回复收藏 0 原文

情绪 2024-11-06 01:06:06

另一种变体——

SELECT 0, COUNT(IF(status >> 0 & 1 = 1, 1, NULL)) FROM table1
UNION
SELECT 1, COUNT(IF(status >> 1 & 1 = 1, 1, NULL)) FROM table1
UNION
SELECT 2, COUNT(IF(status >> 2 & 1 = 1, 1, NULL)) FROM table1
...

One more variant -

SELECT 0, COUNT(IF(status >> 0 & 1 = 1, 1, NULL)) FROM table1
UNION
SELECT 1, COUNT(IF(status >> 1 & 1 = 1, 1, NULL)) FROM table1
UNION
SELECT 2, COUNT(IF(status >> 2 & 1 = 1, 1, NULL)) FROM table1
...

回复收藏 0 原文

~没有更多了~