Collections:
BIT_AND() - Bitwise AND in Group
How to calculate the Bitwise AND value of a field expression in result set groups using the BIT_AND() function?
✍: FYIcenter.com
BIT_AND(expr) is a MySQL built-in aggregate function that calculates the Bitwise AND value of a field expression in result set groups. For example:
SELECT help_category_id, BIN(BIT_AND(help_topic_id)), COUNT(help_topic_id) FROM mysql.help_topic GROUP BY help_category_id; -- +------------------+-----------------------------+----------------------+ -- | help_category_id | BIN(BIT_AND(help_topic_id)) | COUNT(help_topic_id) | -- +------------------+-----------------------------+----------------------+ -- | 1 | 0 | 2 | -- | 2 | 0 | 35 | -- | 3 | 0 | 59 | -- | 4 | 100 | 2 | -- | 5 | 101000 | 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_AND() is also a window function, you can call it with the OVER clause to calculate the bitwise AND value of the given expression in the current window. For example:
SELECT help_topic_id, help_category_id, BIN(BIT_AND(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_AND(help_topic_id) OVER w) | -- +---------------+------------------+------------------------------------+ -- | 0 | 1 | 0 | -- ... -- | 4 | 4 | 100 | -- | 5 | 4 | 100 | -- | 40 | 5 | 101000 | -- | 43 | 5 | 101000 | -- | 44 | 5 | 101000 | -- | 41 | 6 | 101001 | -- | 42 | 7 | 101010 | -- | 45 | 9 | 0 | -- ... -- +---------------+------------------+------------------------------------+
Reference information of the BIT_AND() function:
BIT_AND(expr): bin Returns the bitwise AND of all bits in expr. If there are no matching rows, BIT_AND() returns a neutral value (all bits set to 1). Arguments, return value and availability: expr: Required. The field expression in result set groups. bin: Return value. The bitwise AND value of the input expression. Available since MySQL 5.7.
⇒ BIT_OR() - Bitwise OR in Group
⇐ AVG() - Average Value in Group
2023-11-18, 267🔥, 0💬
Popular Posts:
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops? You can u...
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 download and install Microsoft SQL Server Management Studio Express in SQL Server? Microsoft ...