mysql查询结果作为基于不同表上的2个字段的矩阵表

Question

有 2 个数据库表，如下表 Table_1 和 Table_2。是否可以根据不同表上的2个字段将查询结果提取为表矩阵？

Table_1

| ID_PRODUCT    | NAME_PRODUCT  |
|------------   |-------------- |
| 1             | PRODUCT A     |
| 2             | PRODUCT B     |
| 3             | PRODUCT C     |

Table_2

| ID_SURVEY     | ID_PRODUCT    | TOTAL SALES   |
|-----------    |------------   |-------------  |
| 1             | 1             | 10            |
| 1             | 2             | 20            |
| 2             | 2             | 30            |
| 2             | 3             | 40            |
| 3             | 3             | 50            |

预期输出:

| ID_SURVEY     | PRODUCT A     | PRODUCT B     | PRODUCT C     |
|-----------    |-----------    |-----------    |-----------    |
| 1             | 10            | 20            | 0             |
| 2             | 0             | 30            | 40            |
| 3             | 0             | 0             | 50            |

Answer 1

如果table_1有固定的产品，你可以这样做，

select
    ID_SURVEY,
    max(case when ID_PRODUCT = 1 then `TOTAL SALES` else 0 end) as `PRODUCT A`,
    max(case when ID_PRODUCT = 2 then `TOTAL SALES` else 0 end) as `PRODUCT B`,
    max(case when ID_PRODUCT = 3 then `TOTAL SALES` else 0 end) as `PRODUCT C`
from Table_2
group by ID_SURVEY

Demo Here

但是这里你必须使用动态sql来做到这一点:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN t2.ID_PRODUCT = ',
      t2.ID_PRODUCT,
      ' THEN t2.`TOTAL SALES` ELSE 0 END) AS `',
      t1.NAME_PRODUCT, '`'
    )
  ) INTO @sql
FROM Table_2 t2
JOIN Table_1 t1
ON t2.ID_PRODUCT = t1.ID_PRODUCT;
SET @sql = CONCAT('SELECT t2.ID_SURVEY, ', @sql,
                  ' FROM Table_2 t2
                    JOIN Table_1 t1
                    ON t2.ID_PRODUCT = t1.ID_PRODUCT
                    GROUP BY t2.ID_SURVEY');

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

Demo Here