php - 如何生成像数据透视表这样的 SQL 查询

Question

我想将三个表联合到一个查询，如数据透视表。我的主表名称是 products，两个支持表名称是 code_keys 和 code_values

我有一个主表名称:products

+------------------+ 
| Field            |
+------------------+     
| productid        |     
| catpath          |  
| da               |
| l0               |
| ig               |
| des              |
| notes            |
| wire             |
| lmm              |
+------------------+

示例数据:

+-----------+------+------+------+
| productid | da   | ig   | des  |
+-----------+------+------+------+
|         5 | 78   | 33   | 23   |
|         8 | 88   | 13   | 21   |
+-----------+------+------+------+

和 2 个配置表名称:code_keys。存储代码结构。

+--------+
| Field  |
+--------+
| codeid |
| title  |
| codekey|
| format |
+--------+

示例数据:

+--------+----------------+
| codeid | title          |
+--------+----------------+
|      2 | St Code        |
|      3 | Rear Les       |
+--------+----------------+

code_values

+-------+
| Field |
+-------+
| id    |
| pid   | -> Product ID
| codeid| 
| value |
+-------+

最后是示例数据:

+----+------+--------+--------------+
| id | pid  | codeid | value        |
+----+------+--------+--------------+
|  9 |    5 |      2 | ST 102 200 R |
| 10 |    5 |      3 | 12 000 33    |
| 11 |    6 |      2 | ST 343 432 R |
| 12 |    6 |      3 | 34 343 24    |
| 15 |    8 |      2 | ST 100 101 R |
| 16 |    8 |      3 | 11 223 34    |
| 17 |    0 |      2 | ST 343 432 R |
| 18 |    0 |      3 | 34 343 24    |
+----+------+--------+--------------+

我想在一个查询中这样显示:

产品专栏 | *code_keys 行 > 列* |

+-----------+------+------+------+--------------+----------+
| productid | da   | ig   | des  | St Code      | Rear Les |
+-----------+------+------+------+--------------+----------+
|         5 | 78   | 33   | 23   | ST 102 200 R | 12 000 33| 
|         8 | 88   | 13   | 21   | ST 100 101 R | 11 223 34| 
+-----------+------+------+------+--------------+----------+

有什么想法吗？

Answer 1

您可以使用 CASE 语句来做到这一点:

SELECT p.productid, p.da, p.ig, p.des
      ,GROUP_CONCAT(CASE WHEN ck.title = 'St Code' 
                         THEN cv.value ELSE NULL END) AS 'St Code'
      ,GROUP_CONCAT(CASE WHEN ck.title = 'Rear Les' 
                         THEN cv.value ELSE NULL END) AS 'Rear Les'
FROM Products p
JOIN code_values cv ON p.productid = cv.pid
JOIN code_keys ck ON cv.codeid = ck.codeid
GROUP BY p.productid;

如果你有未知数量的 code_keys 你可以尝试这个动态查询:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN `title` = ''',
      `title`,
      ''' THEN cv.value ELSE NULL END) AS `',
      `title`, '`'
    )
  ) INTO @sql
FROM code_keys ck JOIN code_values cv ON cv.codeid = ck.codeid;

SET @sql = CONCAT('SELECT p.productid, p.da, p.ig, p.des, ', @sql,'
                     FROM Products p
                     JOIN code_values cv ON p.productid = cv.pid
                     JOIN code_keys ck ON cv.codeid = ck.codeid
                    GROUP BY p.productid
                  ');

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

输出:

| PRODUCTID | DA | IG | DES |      ST CODE |  REAR LES |
--------------------------------------------------------
|         5 | 78 | 33 |  23 | ST 102 200 R | 12 000 33 |
|         8 | 88 | 13 |  21 | ST 100 101 R | 11 223 34 |

参见this SQLFiddle