mysql - 如何消除相同行的重复

Question

我的数据库中有一个表，看起来像(表中可以有相同的元组):

+-------------+---------+--------+
| ProductName | Status  | Branch |
+-------------+---------+--------+
| P1          | dead    |      1 |
| P1          | dead    |      2 |
| P1          | dead    |      2 |
| P2          | expired |      1 |
+-------------+---------+--------+

我想在as之后显示结果(Branch属性是动态的):

+-------------+---------+--------+
| ProductName | Branch 1|Branch 2|
+-------------+---------+--------+
| P1          | dead    |    dead|
| P2          | expired |     OK |
+-------------+---------+--------+

经过一些帮助，我想出了以下解决方案:

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
  'GROUP_CONCAT(case when branch = ''',
  branch,
  ''' then status ELSE NULL end) AS ',
  CONCAT('Branch',branch)
 )
) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT productName, ', @sql, ' 
               FROM Table1 
               GROUP BY productName');


PREPARE stmt FROM @sql;
EXECUTE stmt;

显示的结果是这样的:

+-------------+---------+-----------+
| productName | Branch1 |  Branch2  |
+-------------+---------+-----------+
| p1          | dead    | dead,dead |
| p2          | expired | (null)    |
+-------------+---------+-----------+

SQL Fiddle .
我现在想要的是在状态为 null 时将产品状态显示为“OK”而不是 null，并且如果表中有重复的产品也不需要连接状态。尝试了很多，但无法解决。提前致谢。

Answer 1

这有点棘手，因为如果状态相同会重复，所以合并必须在 GROUP_CONCAT 的外部

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COALESCE(GROUP_CONCAT(DISTINCT case when branch = ''',
      branch,
      ''' then  status  end),''OK'') AS ',
      CONCAT('Branch',branch)
    )
  ) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT productName, ', @sql, ' 
                   FROM Table1 
                   GROUP BY productName');



PREPARE stmt FROM @sql;
EXECUTE stmt;

Link