Collections:
BIT_XOR() - Bitwise XOR in Group
How to calculate the Bitwise XOR value of a field expression in result set groups using the BIT_XOR() function?
✍: FYIcenter.com
BIT_XOR(expr) is a MySQL built-in aggregate function that calculates the Bitwise XOR value of a field expression in result set groups. For example:
SELECT help_category_id, BIN(BIT_XOR(help_topic_id)), COUNT(help_topic_id) FROM mysql.help_topic GROUP BY help_category_id; -- +------------------+-----------------------------+----------------------+ -- | help_category_id | BIN(BIT_XOR(help_topic_id)) | COUNT(help_topic_id) | -- +------------------+-----------------------------+----------------------+ -- | 1 | 1 | 2 | -- | 2 | 11 | 35 | -- | 3 | 10 | 59 | -- | 4 | 1 | 2 | -- | 5 | 101111 | 3 | -- ... -- +------------------+-----------------------------+----------------------+ SELECT help_category_id, BIN(help_topic_id) FROM mysql.help_topic WHERE help_category_id = 5; -- +------------------+--------------------+ -- | help_category_id | BIN(help_topic_id) | -- +------------------+--------------------+ -- | 5 | 101000 | -- | 5 | 101011 | -- | 5 | 101100 | -- +------------------+--------------------+
BIT_XOR() is also a window function, you can call it with the OVER clause to calculate the bitwise XOR value of the given expression in the current window. For example:
SELECT help_topic_id, help_category_id, BIN(BIT_XOR(help_topic_id) OVER w) FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +---------------+------------------+------------------------------------+ -- | help_topic_id | help_category_id | BIN(BIT_XOR(help_topic_id) OVER w) | -- +---------------+------------------+------------------------------------+ -- | 0 | 1 | 1 | -- | 1 | 1 | 1 | -- | 2 | 2 | 11 | -- | 6 | 2 | 11 | -- ... -- | 5 | 4 | 1 | -- | 40 | 5 | 101111 | -- | 43 | 5 | 101111 | -- | 44 | 5 | 101111 | -- | 41 | 6 | 101001 | -- | 42 | 7 | 101010 | -- ... -- +---------------+------------------+------------------------------------+
Reference information of the BIT_XOR() function:
BIT_XOR(expr): bin Returns the bitwise XOR of all bits in expr. If there are no matching rows, BIT_XOR() returns a neutral value (all bits set to 0). Arguments, return value and availability: expr: Required. The field expression in result set groups. bin: Return value. The bitwise XOR value of the input expression. Available since MySQL 5.7.
⇒ COUNT() - Counting Items in Group
⇐ BIT_OR() - Bitwise OR in Group
2023-11-18, 270🔥, 0💬
Popular Posts:
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
How To Create a Table Index in Oracle? If you have a table with a lots of rows, and you know that on...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
How To Query Tables and Loop through the Returning Rows in MySQL? The best way to query tables and l...