mysql - 合并两个表并在列中分配 1/0

Question

我创建了两个表，如下所示:

CREATE TABLE IF NOT EXISTS `tools` (
  `pk_id` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  `title` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`pk_id`));

CREATE TABLE IF NOT EXISTS `features` (
  `pk_id` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  `feature` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`pk_id`),
  FOREIGN KEY (id) REFERENCES tools(id));

每个工具可能有多种功能。此外，每个功能可能属于不同的工具。在这种情况下，多对多关系是在没有第三个表的情况下完成的。只是使用不同的主键重复功能。

现在我想编写一个 SELECT 语句来查看每个工具的所有相应功能，如下所示:

Tools  Feature1 Feature2 Feature3 Feature4
=========================================
ToolA  1        0        0        1
ToolB  0        0        1        0

我该怎么做？

目前我写了这个语句，但它没有分配 1 或 0，具体取决于特定工具的功能。

SELECT tools.title, features.feature
FROM tools
INNER JOIN features
ON tools.id=features.id;

更新:

根据@sagi的评论，我检查了以下内容link并尝试使代码适应我的需要:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.feature = ''',
      feature ,
      ''', pa.feature , NULL)) AS ',
      feature 
    )
  ) INTO @sql
FROM features;

SET @sql = CONCAT('SELECT p.id
                    , p.title
                    , ', @sql, ' 
                   FROM tools p
                   LEFT JOIN features AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

但是我得到了一个错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '8,MAX(IF(pa.feature = 'feature 2', pa.feature, NULL)) AS ' at line 3 

Answer 1

SELECT tools.title,
       MAX(case when features.feature = 'Feature1' then 1 else 0 end) as Feature1,
       MAX(case when features.feature = 'Feature2' then 1 else 0 end) as Feature2,
       MAX(case when features.feature = 'Feature3' then 1 else 0 end) as Feature3
FROM tools
INNER JOIN features
ON tools.id = features.id
group by tools.title