SQL多行作为列（优化）

Question

我有一个SQL查询，给出正确的结果，但执行速度太慢。

该查询对以下三个表进行操作：

• customers 包含许多客户数据，例如姓名，地址，电话等。为简化表格，我仅使用名称。

• customdatas包含某些自定义（而非客户）数据。（表是用软件创建的，这就是为什么该表的复数形式是错误的）

• customercustomdatarels 将自定义数据与客户相关联。

顾客

Id                                  Name            (many more columns)
-----------------------------------------------------------------------
8053c6f4c5c5c631054ddb13d9186117    MyCustomer      ...
2efd2aa5711ddfade1f829b12dd88cf3    CheeseFactory   ...

自订资料

id                                  key
-------------------------------------------------
22deb172c1af6e8e245634a751871564    favoritsport
86eea84d296df9309ad6ff36fd7f856e    favoritcheese

customercustomdatarels （客户数据和自定义数据之间的关系-具有相应的值）

customer                            customdata                          value
-------------------------------------------------------------------------------------
8053c6f4c5c5c631054ddb13d9186117    22deb172c1af6e8e245634a751871564    cycling
8053c6f4c5c5c631054ddb13d9186117    86eea84d296df9309ad6ff36fd7f856e    cheddar
2efd2aa5711ddfade1f829b12dd88cf3    22deb172c1af6e8e245634a751871564    football
2efd2aa5711ddfade1f829b12dd88cf3    86eea84d296df9309ad6ff36fd7f856e    mouldy

我想要的是一个基本上由所有数据组成的表，该表中包含customers数量可变的额外列，对应于中指定的自定义数据customercustomdatarels。这些列应在某处定义，因此我创建了下表，该表定义了这些额外的列并将它们映射到customdata表中的键：

test_customkeymapping

colkey  customkey
---------------------
1       favoritsport
2       favoritcheese

结果应为：

Name            ExtraColumn_1   ExtraColumn_2
---------------------------------------------
CheeseFactory   football        mouldy
MyCustomer      cycling         cheddar

（ ExtraColumn_1 因此是一个客户代名词最喜欢的运动和 ExtraColumn_2 是一个客户的代名词骄子奶酪。）

通过执行以下查询可以实现此结果：

SET @sql = NULL;

SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE
                WHEN ckm.colkey = ', colkey, ' THEN
                    (SELECT value FROM customercustomdatarels ccdr2
                     LEFT JOIN customdatas cd2
                       ON cd2.id = ccdr2.customdata
                     WHERE cd2.key = ckm.customkey AND c.Id = ccdr2.customer)
                END) AS ', CONCAT('`ExtraColumn_', colkey, '`'))
    ) INTO @sql
FROM test_customkeymapping;

SET @sql = CONCAT('SELECT c.Name, ', @sql, ' 
                   FROM customers c
                   LEFT JOIN customercustomdatarels ccdr
                     ON c.Id = ccdr.customer
                   LEFT JOIN customdatas cd
                     ON cd.Id = ccdr.customdata
                   LEFT JOIN test_customkeymapping ckm 
                     ON cd.key = ckm.customkey
                   GROUP BY c.Id');

PREPARE stmt FROM @sql;
EXECUTE stmt;

这行得通。但是太慢了（对于7000个客户，它需要约10秒的时间）。该查询在很大程度上受以下问题的解决方案的影响：
MySQL将多行作为列连接

如何优化此查询？

Answer 1

我不明白您为什么在group_concat()语句中使用子查询。这不会生成您真正想要运行的代码吗？

SET @sql = NULL;

SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN ckm.colkey = ', colkey, ' THEN ccd.value END) AS ',  
               CONCAT('ExtraColumn_', colkey, ''))
    ) INTO @sql
FROM test_customkeymapping;

SET @sql = CONCAT('SELECT c.Name, ', @sql, ' 
                   FROM customers c
                   LEFT JOIN customercustomdatarels ccdr
                     ON c.Id = ccdr.customer
                   LEFT JOIN customdatas cd
                     ON cd.Id = ccdr.customdata
                   LEFT JOIN test_customkeymapping ckm 
                     ON cd.key = ckm.customkey
                   GROUP BY c.Id');

PREPARE stmt FROM @sql;
EXECUTE stmt;

注意：这未经测试，但是想法是相同的。在工作中使用mainfrom语句中的值，而不要使用一些多余的，不必要的子查询中的值。