MYSQL Adminer环境下多条SELECT查询合并为单条查询咨询

• 运行环境：MySQL Adminer 4.8.0、4.8.1版本
• 需求：将7条独立SELECT查询合并为单条查询，尝试过UNION和自行编写JOIN查询都没有成功。

待合并的7条原始查询 #

SELECT `value` from `core_config_data`
WHERE `path` = 'catalog/product/base_media_url';

SELECT `label`
FROM `catalog_product_entity_media_gallery_value`
WHERE `value_id` = (SELECT `value_id`
FROM `catalog_product_entity_media_gallery` 
WHERE `value` LIKE '%test%')
LIMIT 1;

SELECT `child_id`
FROM `catalog_product_relation`
WHERE `parent_id` = 9622
LIMIT 1;

SELECT `rule_price`
FROM `catalogrule_product_price`
WHERE  `product_id` = 9622
LIMIT 1;

SELECT value
FROM `catalog_product_entity_decimal`
WHERE `row_id` = 9622  AND `attribute_id` IN (SELECT `attribute_id` FROM `eav_attribute` 
WHERE `attribute_code` IN ('price','special_price'))

SELECT `value`
FROM `core_config_data`
WHERE `scope` = 'websites' AND `path` = 'currency/options/default' AND `scope_id` = 3;

SELECT e.sku,value from catalog_product_entity e
inner join catalog_product_entity_varchar v on e.entity_id = v.row_id and e.entity_id = 9622 and
attribute_id IN (select attribute_id from eav_attribute where attribute_code IN ('name','sku'));

自行编写的错误查询 #

SELECT parent.entity_id AS parent_id, 
       simple.entity_id AS simple_id, 
       parent.sku AS sku, simple.sku AS simple_sku 
       ccd.value AS base_media_url 
FROM catalog_product_entity AS parent 
JOIN catalog_product_super_link AS link ON parent.row_id = link.parent_id 
JOIN catalog_product_entity AS simple ON link.product_id = simple.entity_id 
LEFT JOIN core_config_data AS ccd ON path = 'catalog/product/base_media_url' 
WHERE parent.entity_id IN (9244) LIMIT 1 

你的7条查询返回的字段数量、字段类型都不一致，所以UNION肯定不适用。要把所有结果合并成一行返回，直接把每个独立查询作为标量子查询放在SELECT列表中即可。我修正了原查询的语法错误（原第6条多了一个多余的单引号），对返回多行的查询做了合并处理，最终单条查询如下：

SELECT
    -- 第1条查询结果：基础媒体URL
    (SELECT `value` from `core_config_data` WHERE `path` = 'catalog/product/base_media_url') AS base_media_url,
    -- 第2条查询结果：媒体标签
    (SELECT `label` FROM `catalog_product_entity_media_gallery_value` WHERE `value_id` = (SELECT `value_id` FROM `catalog_product_entity_media_gallery` WHERE `value` LIKE '%test%') LIMIT 1) AS media_label,
    -- 第3条查询结果：子产品ID
    (SELECT `child_id` FROM `catalog_product_relation` WHERE `parent_id` = 9622 LIMIT 1) AS child_product_id,
    -- 第4条查询结果：规则价格
    (SELECT `rule_price` FROM `catalogrule_product_price` WHERE  `product_id` = 9622 LIMIT 1) AS rule_price,
    -- 第5条查询结果：价格/特价，用GROUP_CONCAT合并两个值
    (SELECT GROUP_CONCAT(attribute_code, ':', value SEPARATOR ',') FROM `catalog_product_entity_decimal` d LEFT JOIN eav_attribute a ON d.attribute_id = a.attribute_id WHERE `row_id` = 9622 AND a.`attribute_code` IN ('price','special_price')) AS price_info,
    -- 第6条查询结果：默认货币
    (SELECT `value` FROM `core_config_data` WHERE `scope` = 'websites' AND `path` = 'currency/options/default' AND `scope_id` = 3) AS default_currency,
    -- 第7条查询结果：产品名称/SKU，合并返回
    (SELECT GROUP_CONCAT(attribute_code, ':', value SEPARATOR ',') FROM catalog_product_entity e INNER JOIN catalog_product_entity_varchar v on e.entity_id = v.row_id LEFT JOIN eav_attribute a ON v.attribute_id = a.attribute_id WHERE e.entity_id = 9622 AND a.attribute_code IN ('name','sku')) AS product_base_info;

如果需要把价格、名称、SKU拆成单独的字段，把对应的子查询拆成多个，单独按attribute_code筛选即可。

内容的提问来源于stack exchange，提问作者user15233374