我有一张桌子上摆满了各种各样的“面具”，例如：
Type Mask1 Mask2 Mask3 0 fff fff ff 1 aff fff ff 2 aff fff 92 3 001 fff 00基本上我想查询数据库并查看特定查询是否匹配，例如a00-111-12 。 任何地方都有一个f （这都是十六进制）我想说有一个匹配。 所以我取值a00-111-12并且它应该与行0和1匹配但不匹配2和3，因为在行0中，所有f都出现，因此与它们进行AND值将导致相同的值。 但是，AND-ing不起作用，因为如果使用第2行测试，Mask3列值92和12使用12会产生12，但我不希望该行匹配。
我发现这是一个很难回答的问题，使用一些MySQL查询可能无法实现，但我想避免将整个表导入PHP，然后从那里找到正确的行。
查询的想法是：
SELECT * FROM TABLE WHERE Mask1 = a00 AND Mask2 = 111 AND ... 但是，需要对Mask1,2,3或发送到查询的值进行某些操作。最终目标是从匹配的行中获取Type 。 如果您需要更多信息，请询问。
I have a table that is filled with a variety of "masks" such at this:
Type Mask1 Mask2 Mask3 0 fff fff ff 1 aff fff ff 2 aff fff 92 3 001 fff 00And basically I want to query the database and see if a particular query matches, say a00-111-12. Anywhere there is an f (this is all in hex) I want to say there is a match. So I take the value a00-111-12 and it should match with rows 0 and 1 but not 2 and 3 because in row 0, all f's appear and thus a value AND'd with them would result in that same value. BUT, AND-ing does not work since if testing with row 2, Mask3 column value 92 AND'd with 12 results in 12, however I don't want that row to be a match.
I find this a difficult question to ask, it may not be possible with a few MySQL Queries but I want to avoid importing the entire table into PHP and then finding the correct rows from there.
An idea of a query would be:
SELECT * FROM TABLE WHERE Mask1 = a00 AND Mask2 = 111 AND ... However some operation would need to be done on either Mask1, 2, 3 or the value being sent to the query.The end goal is to get the Type from the matching rows. If you need more information please ask.
最满意答案
创建子掩码表以使您的工作更轻松，添加一行
z1 : z2 : z3 0xf : 0xf0 : 0xf00然后使用以下查询
Select t.* from Table t inner join submasks s on ( ((t.Mask1 & s.z1) = s.z1 || (t.Mask1 & s.z1) = (a00 & s.z1)) && ((t.Mask1 & s.z2) = s.z2 || (t.Mask1 & s.z2) = (a00 & s.z2)) && ((t.Mask1 & s.z2) = s.z2 || (t.Mask1 & s.z2) = (a00 & s.z2)) && ((t.Mask2 & s.z1) = s.z1 || (t.Mask2 & s.z1) = (111 & s.z1)) && ((t.Mask2 & s.z2) = s.z2 || (t.Mask2 & s.z2) = (111 & s.z2)) && ((t.Mask2 & s.z2) = s.z2 || (t.Mask2 & s.z2) = (111 & s.z2)) && ((t.Mask3 & s.z1) = s.z1 || (t.Mask3 & s.z1) = (12 & s.z1)) && ((t.Mask3 & s.z2) = s.z2 || (t.Mask3 & s.z2) = (12 & s.z2)) )这种方式的工作方式是比较各个十六进制数字，通过执行按位AND与z1 ， z2和z2分别得到3个数字中的每一个。
所以
<any value> & z1将除last之外的所有十六进制数设置为0，即0x123变为0x123 <any value> & z2将除最后一个之外的所有十六进制数字设置为0，即0x123变为0x020 <any value> & z3将除最后一个之外的所有十六进制数字设置为0，即0x123变为0x100使用此过滤器可以构建每个数字的测试
((mask & filter) = filter) // is the digit f || // OR ((mask & filter) = (test & filter)) // is the digit the same.对z1 ， z2和z3 （即0x00f ， 0x0f0和0xf00 ）中的每一个重复测试，将结果与a和条件组合，您可以检查掩码的所有3个十六进制数字是f还是测试值。
然后对Mask2和Mask3重复这一过程（但只有z1，z2，因为Mask3是2位数）。
通过对子掩码表使用内连接，结果将仅包括掩码条件为真的表中的值。
更新 - 您可能希望执行select distinct而不是仅select ，就好像两个掩码匹配Table中的单个行，然后返回2个结果。
Create a submasks table to make your job easier, add one row
z1 : z2 : z3 0xf : 0xf0 : 0xf00Then use the following query
Select t.* from Table t inner join submasks s on ( ((t.Mask1 & s.z1) = s.z1 || (t.Mask1 & s.z1) = (a00 & s.z1)) && ((t.Mask1 & s.z2) = s.z2 || (t.Mask1 & s.z2) = (a00 & s.z2)) && ((t.Mask1 & s.z2) = s.z2 || (t.Mask1 & s.z2) = (a00 & s.z2)) && ((t.Mask2 & s.z1) = s.z1 || (t.Mask2 & s.z1) = (111 & s.z1)) && ((t.Mask2 & s.z2) = s.z2 || (t.Mask2 & s.z2) = (111 & s.z2)) && ((t.Mask2 & s.z2) = s.z2 || (t.Mask2 & s.z2) = (111 & s.z2)) && ((t.Mask3 & s.z1) = s.z1 || (t.Mask3 & s.z1) = (12 & s.z1)) && ((t.Mask3 & s.z2) = s.z2 || (t.Mask3 & s.z2) = (12 & s.z2)) )The way this works is by comparing individual hex digits by performing a bitwise AND with z1,z2 and z2 to get each of the 3 digits respectively.
so
<any value> & z1 sets all hex digits except the last to 0, ie 0x123 becomes 0x003 <any value> & z2 sets all hex digits except the second from last to 0, ie 0x123 becomes 0x020 <any value> & z3 sets all hex digits except the third from last to 0, ie 0x123 becomes 0x100Using this filter the test for each digit can be built as
((mask & filter) = filter) // is the digit f || // OR ((mask & filter) = (test & filter)) // is the digit the same.Repeat the test for each of z1,z2 and z3 (ie 0x00f, 0x0f0, and 0xf00) combine the results with an and condition and you can check all 3 hex digits of the mask are either f or exactly the test value.
This is then repeated for Mask2 and Mask3 (but only z1,z2 as Mask3 is 2 digits).
By using inner join with the submasks table the result will only include the values from Table where the mask conditions are true.
UPDATE - you may want to perform select distinct instead of just select as if two masks match a single row in Table then 2 results will be returned.
更多推荐
发布评论