如何生成此SQL查询，如数据透视表 [英] How to generate this SQL query like pivot table

问题描述

我想将三个表合并为一个查询，如数据透视表. 我的主表名称是产品，两个支持表的名称是code_keys和code_values

I want to three table union to one query like a pivot table. My main table name is products and two support tables name are code_keys and code_values

我有一个主表名称:产品

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

示例数据:

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

和2个conf表名称: code_keys .商店代码结构.

and 2 conf table names: code_keys. Store code structure.

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

示例数据:

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

代码值

+-------+
| 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    |
+----+------+--------+--------------+

我想在一个查询中显示以下内容:

I want to show like this into one query:

产品列| * 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| 
+-----------+------+------+------+--------------+----------+

有什么想法吗?

推荐答案

您可以使用CASE语句执行此操作:

You can do this using CASE statement:

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，则可以尝试以下动态查询:

If you have unknown number of code_keys you can try this dynamic query:

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 |

这篇关于如何生成此SQL查询，如数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！