使用逻辑操作获取数据列表的MySQL查询

Question

以下是顾客在图书馆里阅读的不同种类的书籍的清单。这些值以2的幂存储在一个名为bookType的列中。

​

​

我需要从具有逻辑操作查询的数据库中获取与阅读only Novel Or only Fairytale Or only BedTime Or both Novel + Fairytale的人员组合的书籍列表。

获取下列组合的列表：

• 只读小说的人(存储在DB中为1)
• 同时阅读小说和童话的人(以1+2 =3的形式存储在DB中)
• 阅读所有这三种内容的人，即{小说+童话+床时}(存储在DB中的1+2+4 = 7)

它们的计数存储在一个名为BookType的列中(在图中标记为红色)。

如何使用MySQL查询获取上述列表

从这个例子中，我需要获取像小说阅读器这样的用户(1,3,5,7)。

Answer 1

这个问题的核心是将十进制转换为二进制，mysql只有一个函数- CONV(num，from_base，to_base )；在本例中，from_base为10，to_base为2。

MariaDB [sandbox]> select id,username
    -> from users
    -> where id < 8;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | Jane     |
|  3 | Ali      |
|  6 | Bruce    |
|  7 | Martha   |
+----+----------+
5 rows in set (0.00 sec)

MariaDB [sandbox]> select * from t;
+------+------------+
| id   | type       |
+------+------------+
|    1 | novel      |
|    2 | fairy Tale |
|    3 | bedtime    |
+------+------------+
3 rows in set (0.00 sec)

这个UDF

drop function if exists book_type;
delimiter //

CREATE DEFINER=`root`@`localhost` FUNCTION `book_type`(
    `indec` int
)
RETURNS varchar(255) CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin
declare   tempstring varchar(100);
declare   outstring  varchar(100);
declare   book_types varchar(100);
declare   bin_position int;
declare   str_length int;
declare  checkit int;
set tempstring =  reverse(lpad(conv(indec,10,2),4,0));
set str_length = length(tempstring);
set checkit = 0;
set bin_position = 0;
set book_types = '';
looper: while   bin_position < str_length do
        set bin_position = bin_position + 1;
        set outstring = substr(tempstring,bin_position,1);


        if outstring = 1    then
            set book_types = concat(book_types,(select trim(type) from t where id = bin_position),','); 
        end if;
end while; 

set outstring = book_types;

return outstring;
end //
delimiter ;

结果在

+----+----------+---------------------------+
| id | username | book_type(id)             |
+----+----------+---------------------------+
|  1 | John     | novel,                    |
|  2 | Jane     | fairy Tale,               |
|  3 | Ali      | novel,fairy Tale,         |
|  6 | Bruce    | fairy Tale,bedtime,       |
|  7 | Martha   | novel,fairy Tale,bedtime, |
+----+----------+---------------------------+
5 rows in set (0.00 sec)

注意UDF中的循环以遍历二进制字符串，并且1的位置与查找表中的it相关；我让您来编写错误代码和整理。