mysql - 将长重复结果分组/聚类到数据列中

Question

我正在mysql而不是excel中收集一些信息。为每种细胞类型定义了一些标签，但并非所有标签都存在。所以，我有 3 个标签、信息和单元格表。

select cell_name, label, information from onco_celldb_information as info 
left join onco_celldb_cells as cell on cell.`celldb_cell_id` = info.`celldb_cell_id`
left join onco_celldb_labels as label on info.`celldb_label_id` = label.`celldb_label_id`
order by cell.celldb_cell_id asc;

结果是:

running query above http://f.cl.ly/items/0m2k1a410s3D0K2Y0l1u/Screen%20Shot%202012-08-22%20at%2011.57.36%20AM.png

但是我想要的是这样的东西:

CellName    Species     CellType    Origin
---------+-----------+-----------+-----------
P-815      Murine      Mastroxxxx    Human
L292      Something      Megatrone    Mouse

因此，将它们按单元格名称分组，并将结果作为列。如果标签不存在，则那里只有 NULL(某些结果可能不存在标签)。

你有什么建议？

使用数据库结构进行编辑:

mysql> describe celldb_cells;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| celldb_cell_id | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| cell_name      | varchar(256)     | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

describe celldb_information;
+-----------------------+------------------+------+-----+---------+----------------+
| Field                 | Type             | Null | Key | Default | Extra          |
+-----------------------+------------------+------+-----+---------+----------------+
| celldb_information_id | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| celldb_cell_id        | int(11) unsigned | YES  | MUL | NULL    |                |
| celldb_label_id       | int(11) unsigned | NO   | MUL | NULL    |                |
| information           | text             | YES  |     | NULL    |                |
+-----------------------+------------------+------+-----+---------+----------------+

describe celldb_labels;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| celldb_label_id | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| label           | varchar(256)     | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

Answer 1

您尝试执行的操作称为PIVOT，不幸的是MySQL没有PIVOT功能，但您可以使用CASE复制它语句和聚合函数。

如果您提前知道所有标签并且它们的数量是可管理的，那么您可以对它们进行硬编码，如下所示:

SELECT cell_name,
  MAX(CASE WHEN label = 'Cell Type' THEN information END) 'Cell Type',
  MAX(CASE WHEN label = 'DSMZ no.' THEN information END) 'DSMZ no.'
FROM test
GROUP BY cell_name

参见 SQL Fiddle with Demo

通过您的查询，您可以执行以下操作:

SELECT cell_name,
  MAX(CASE WHEN label = 'Cell Type' THEN information END) 'Cell Type',
  MAX(CASE WHEN label = 'DSMZ no.' THEN information END) 'DSMZ no.'
from onco_celldb_information as info 
left join onco_celldb_cells as cell 
  on cell.`celldb_cell_id` = info.`celldb_cell_id`
left join onco_celldb_labels as label 
  on info.`celldb_label_id` = label.`celldb_label_id`
GROUP BY cell_name

但是，您似乎将拥有未知数量的列，因此您将需要使用准备好的语句:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when label = ''',
      label,
      ''' then information end) AS ''',
      label, ''''
    )
  ) INTO @sql
FROM test;


SET @sql = CONCAT('SELECT cell_name, ', @sql, ' FROM test
group by cell_name');

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

参见 SQL Fiddle with Demo

因此，对于您的具体示例，如果是这样的:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when label = ''',
      label,
      ''' then information end) AS ''',
      label, ''''
    )
  ) INTO @sql
FROM onco_celldb_labels;

SET @sql = CONCAT('SELECT cell_name, ', @sql, ' 
from onco_celldb_information as info 
left join onco_celldb_cells as cell 
  on cell.`celldb_cell_id` = info.`celldb_cell_id`
left join onco_celldb_labels as label 
  on info.`celldb_label_id` = label.`celldb_label_id`              
group by cell_name');

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