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, 272🔥, 0💬
Popular Posts:
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not dele...
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...