MySQL 多重连接

Question

我创建了以下查询，它从 3 个不同的表中提取数据:

    SELECT
    product_searchables_names.class_name AS Class_Name,
    product_searchables_names.label AS Attribute_Label,
    jss_products.extrafield102 AS Attribute1,
    jss_products.extrafield103 AS Attribute2,
    jss_products.extrafield104 AS Attribute3,
    jss_products.extrafield105 AS Attribute4,
    jss_products.extrafield106 AS Attribute5,
    jss_products.extrafield107 AS Attribute6
    FROM
    product_searchables_categories 
    JOIN product_searchables_names ON product_searchables_categories.searchableID = product_searchables_names.ID
    JOIN jss_products ON product_searchables_categories.categoryID = jss_products.categories
    WHERE
    jss_products.productID = '134890'
    ORDER BY
    product_searchables_categories.searchable_order ASC

以下列格式输出数据

但我需要这种格式，其中第 1 行从“Attribute”1 中选择值，第 2 行从“Attribute2”等中选择值:

我该怎么做？

提前致谢

Answer 1

您可以尝试使用 case 语句 http://dev.mysql.com/doc/refman/5.1/de/case-statement.html喜欢

select
    product_searchables_names.class_name AS Class_Name,
    product_searchables_names.label AS Attribute_Label,
    CASE 
        WHEN product_searchables_names.label = 'Resolution' THEN jss_products.extrafield104
        WHEN product_searchables_names.label = 'Networked?' THEN jss_products.extrafield106
        WHEN ...
    END
from
    product_searchables_categories
...

或者使用类名代替标签