MySQL 查询将位域扩展为单独的命名列？

Question

我有以下问题我有两个表，第一个表包含一个类似 01001101110 的字符串，每个数字代表表二中提到的一项技能。

因此第一个数字显示为零，这意味着该人不具备 id 1 的技能。第二个数字表明该人确实具有 id 2 的技能

表 1:

|-----------|-----------------------------|
|  name  |             skillset            |
|-----------|-----------------------------|
|   John  | 01001101110             |
|-----------|-----------------------------|

表 2:

|-----------|-----------------------------|
|    id       |              skill              |
|-----------|-----------------------------|
|       1    | polite                         |
|-----------|-----------------------------|
|      2     | easy going                 |
|-----------|-----------------------------|

现在我需要创建带有结果的查询

|-----------|-------------------|------------------|
|  name  |      polite        |   easy going  |
|-----------|-------------------|------------------|
|  John   |          0            |            1        |
|-----------|-------------------|------------------|

Answer 1

我见过很多这样的故事，“一位前编码员实现了这个，现在我们却坚持使用它。”我想象只有一个独立的编码员，从一家公司跳槽到另一家公司，以尽可能最聪明和最不易维护的方式实现事情，然后迅速转移到他的下一个受害公司。

我还觉得讽刺的是，有人会在 varchar 中实现位域，因为他们最终使用一个完整的字节(8 位)来存储每个 1 或 0。:facepalm:

无论如何，要解决您的任务，您必须使用动态 SQL 查询。

事实上，任何数据透视表查询都需要动态 SQL，因为在检查列出您的技能的表之前，您不知道列数，但如果没有，您将无法运行查询首先知道列数。因此，您至少需要运行两个查询。

测试数据如下:

create table table1 (name varchar(20), skillset varchar(200));

insert into table1 values ('John', '01001101110110');

create table table2 (id int, skill varchar(20));

insert into table2 values
    (1, 'polite'),
    (2, 'easy going'),
    (3, 'trustworthy'),
    (4, 'loyal'),
    (5, 'helpful'),
    (6, 'friendly'),
    (7, 'courteous'),
    (8, 'kind'),
    (9, 'obedient'),
    (10, 'cheerful'),
    (11, 'thrifty'),
    (12, 'brave'),
    (13, 'clean'),
    (14, 'reverent');

Now 是一个聪明的查询，它通过在选择列表中为技能表中的每个条目附加一个字段来生成动态查询的 SQL。关键是MySQL的GROUP_CONCAT()功能。

select concat(
    'select name,',
    group_concat(concat(' mid(skillset,',id,',1) as `',skill,'`')),
    ' from table1;') as _sql
from table2;

上述查询的输出如下:

select name, 
 mid(skillset,1,1) as `polite`, 
 mid(skillset,2,1) as `easy going`, 
 mid(skillset,3,1) as `trustworthy`, 
 mid(skillset,4,1) as `loyal`, 
 mid(skillset,5,1) as `helpful`, 
 mid(skillset,6,1) as `friendly`, 
 mid(skillset,7,1) as `courteous`, 
 mid(skillset,8,1) as `kind`, 
 mid(skillset,9,1) as `obedient`, 
 mid(skillset,10,1) as `cheerful`, 
 mid(skillset,11,1) as `thrifty`, 
 mid(skillset,12,1) as `brave`, 
 mid(skillset,13,1) as `clean`, 
 mid(skillset,14,1) as `reverent` 
from table1;

我确保用反引号分隔列别名，以防其中一个技能名称包含特殊字符或空格或与 SQL 保留字冲突。

然后这可以作为第二个查询运行，结果如下:

+------+--------+------------+-------------+-------+---------+----------+-----------+------+----------+----------+---------+-------+-------+----------+
| name | polite | easy going | trustworthy | loyal | helpful | friendly | courteous | kind | obedient | cheerful | thrifty | brave | clean | reverent |
+------+--------+------------+-------------+-------+---------+----------+-----------+------+----------+----------+---------+-------+-------+----------+
| John | 0      | 1          | 0           | 0     | 1       | 1        | 0         | 1    | 1        | 1        | 0       | 1     | 1     | 0        |
+------+--------+------------+-------------+-------+---------+----------+-----------+------+----------+----------+---------+-------+-------+----------+