sql存储过程和按位运算符
我有一个类别表：
id;description;special
----------------------
1;Spares;TRUE
2;Accessories;TRUE
4;Consumables;TRUE
8;Services;TRUE
11;Printer;FALSE
12;Monitor;FALSE
13;Other;FALSE
特殊字段指定具有固定ID且无法删除或修改的特殊类别。我已经为它们分配了可按位或运算的 id。
然后我有项目，每个项目都属于一个类别（具有 1:n 关系）。
现在我想编写一个存储过程，它接受一个包含 ids 的 OR 组合的输入参数：
1 I want spare parts
2 I want accessories
4 I want consumables
**5 I want consumables AND spare parts**
etc
如果参数为 NULL，那么我想要每个项目，无论其类别如何。
这很简单，假设参数名为 _or_category，那么 WHERE 子句可能类似于：
SELECT
*
FROM
items I
JOIN
categories C ON (C.id = I.category)
WHERE
(_or_category IS NULL) OR (C.special = TRUE AND C.id | _or_categoria = _or_categoria)
;
第一个问题： *编辑：抱歉，这不是问题，因为我在 WHERE 子句中有 C.special=TRUE。* 类别 12 可以被“视为”id=8 或 4，因此，如果我只想选择消耗品，我也会得到显示器！
第二个问题： 我不知道如何指定何时需要所有非服务项目（类别：8）。
I have a categories table:
id;description;special
----------------------
1;Spares;TRUE
2;Accessories;TRUE
4;Consumables;TRUE
8;Services;TRUE
11;Printer;FALSE
12;Monitor;FALSE
13;Other;FALSE
The special field designates special categories that have fixed ids and cannot be deleted nor modified. I've assigned bitwise OR-able ids to those.
Then I have items, each item belongs to a category (with a 1:n relationship).
Now I'd like to write a stored procedure that takes an input parameter containing an OR-ed combination of ids:
1 I want spare parts
2 I want accessories
4 I want consumables
**5 I want consumables AND spare parts**
etc
If the parameter is NULL, then I want every item regardless of its category.
This is quite easy, say the parameter is called _or_category, then the WHERE clause could be something like:
SELECT
*
FROM
items I
JOIN
categories C ON (C.id = I.category)
WHERE
(_or_category IS NULL) OR (C.special = TRUE AND C.id | _or_categoria = _or_categoria)
;
First problem:
*edit: sorry this is not a problem, since I have C.special=TRUE in the WHERE clause.*
category 12 could be "seen" as id=8 OR 4, thus if I want to select only the consumables, I would get also the monitors!
Second problem:
I don't know how to specify when I want all the items which are NOT a service (cat: 8).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果我理解你的问题，我认为你正在寻找按位反转位
~例如
If I understand your question I think you're looking for the bitwise Invert bits
~for example
你需要的
不是服务的所有东西 =
0x7F & ~0x08编辑：如果您只想将前 4 个内容作为标志，则没有太大不同。前 4 位专门保留用于位比较。因此，您不能拥有任何需要前 4 位（从右开始）中的值的附加 id...
同样，
Ax(~Sx) = 0x3F & ~0x08You need
And all things not services =
0x7F & ~0x08Edit: If you only want the first 4 things to be flags it is not much different. The first 4 bits are reserved exclusively for your bit comparisons. So you cannot have any additional ids that would require a value in the first 4 bits (from the right)...
And again,
Ax(~Sx) = 0x3F & ~0x08