插入值中子查询的 mysql 语法错误

Question

当我运行时，这个查询有什么问题，他们得到了语法错误

INSERT IGNORE INTO an_catalogsearch_fulltext (product_id, store_id, data_index) 
SELECT DISTINCT ca_ent.entity_id as product_id, 
4 as store_id, 
CONCAT( 
ifnull(ca_var.value,''),' ',
ifnull(ca_ent.sku,''), ' ', 
ifnull(
    (
        SELECT GROUP_CONCAT( ca_ent2.sku) FROM `an_catalog_product_entity` ca_ent2 LEFT JOIN `an_catalog_product_super_link` ca_sup 
        ON ca_sup.product_id = ca_ent2.entity_id WHERE ca_sup.parent_id = ca_ent.entity_id GROUP BY ca_sup.parent_id
    ),'') ,' ', 
ifnull(
    (
        SELECT GROUP_CONCAT( ca_ent3.sku) FROM `an_catalog_product_entity` ca_ent3 LEFT JOIN `an_catalog_product_link` ca_lin 
        ON ca_lin.linked_product_id = ca_ent3.entity_id WHERE ca_lin.product_id = ca_ent.entity_id GROUP BY ca_lin.product_id
    ),'') ,' ', 
ifnull(ca_text.value,'') , ' ', 
ifnull(
    select option_value.value from `an_eav_attribute_option_value` as option_value where option_value.option_id = an_alt_norm_form.value, '') 
) as data_index FROM `an_catalog_product_entity` ca_ent 

LEFT JOIN `an_catalog_product_entity_varchar` ca_var ON ca_var.entity_id = ca_ent.entity_id AND ca_var.attribute_id = 71 
LEFT JOIN `an_catalog_product_entity_text` ca_text ON ca_text.entity_id = ca_ent.entity_id AND ca_text.attribute_id = 72 
LEFT JOIN `an_catalog_product_entity_int` an_alt_norm_form ON an_alt_norm_form.entity_id = ca_ent.entity_id AND an_alt_norm_form.attribute_id = 306 ;

当我添加时

ifnull(select option_value.value from `an_eav_attribute_option_value` as option_value where option_value.option_id = an_alt_norm_form.value, '')

他们给了我一个语法错误

当我用简单的 ifnull(an_alt_norm_form.value, '') 替换这一行时，工作正常，我做错了什么

Answer 1

将您使用的语法与现有的 ifnull 语句进行比较:

ifnull(
    (
        SELECT GROUP_CONCAT( ca_ent2.sku) FROM `an_catalog_product_entity` ca_ent2 LEFT JOIN `an_catalog_product_super_link` ca_sup 
        ON ca_sup.product_id = ca_ent2.entity_id WHERE ca_sup.parent_id = ca_ent.entity_id GROUP BY ca_sup.parent_id
    ),'')

在这里，您将子查询包装在一组附加的括号中，但带有新添加的

ifnull(select option_value.value from `an_eav_attribute_option_value` ..., '')

那些失踪了。