MYSQL GROUP_CONCAT CONCAT_WS并处理NULL值

How to handle rows with NULL values especial from here (this is only one place when values can be NULL, because products don't have any attributes):

如何处理具有NULL值的行(特别是从这里开始)(当值可以为NULL时,这只是一个位置,因为产品没有任何属性):

GROUP_CONCAT(DISTINCT (CONCAT_WS (': ', GroupAttrLang.`name`, AttrLang.`name`)) ORDER BY AttrLang.`name` ASC

This is part from my BIG query:

这是我的BIG查询的一部分:

SELECT
TProduct.`reference` AS ProdReference,
ProdLang.`name` AS ProductName,
ProdLang.`description` AS ProdDesc,
ROUND((TProduct.`price`*1.23)/4.1989,2) AS ProductPrice,

CASE 
    WHEN COUNT(ProdAttr.`id_product`) = 0 THEN 1 
    ELSE (GROUP_CONCAT(DISTINCT (CONCAT_WS (': ', GroupAttrLang.`name`, AttrLang.`name`)) ORDER BY AttrLang.`name` ASC)) 
END AS ProdAttributes,

/*IF ((COUNT(ProdAttr.`id_product`) > 0), GROUP_CONCAT(DISTINCT (CONCAT_WS (': ', GroupAttrLang.`name`, AttrLang.`name`)) ORDER BY AttrLang.`name` ASC) , 'n/a' ) AS ProdAttributes,*/

GROUP_CONCAT(DISTINCT (CONCAT_WS (' - ', ParLang.`name`, CatLang.`name` ))) AS ProdFit 
    FROM `ps_product` AS TProduct
        LEFT JOIN `ps_category_product` AS CatProd ON CatProd.`id_product` = TProduct.`id_product`

        LEFT JOIN `ps_category` AS Cat ON Cat.`id_category` = CatProd.`id_category`
        LEFT JOIN `ps_category_lang` AS ParLang ON Cat.`id_parent` = ParLang.`id_category`
        LEFT JOIN `ps_product_lang` AS ProdLang ON ProdLang.`id_product` = TProduct.`id_product`
        LEFT JOIN `ps_category_lang` AS CatLang ON CatLang.`id_category` = CatProd.`id_category`
        LEFT JOIN `ps_product_attribute` AS ProdAttr ON ProdAttr.`id_product` = TProduct.`id_product`
        LEFT JOIN `ps_product_attribute_combination` AS ProdAttrComb ON ProdAttrComb.`id_product_attribute` = ProdAttr.`id_product_attribute`
        LEFT JOIN `ps_attribute` AS TAttr ON TAttr.`id_attribute` = ProdAttrComb.`id_attribute`
        LEFT JOIN `ps_attribute_group_lang` AS GroupAttrLang ON GroupAttrLang.`id_attribute_group` = TAttr.`id_attribute_group`
        LEFT JOIN `ps_attribute_lang` AS AttrLang ON AttrLang.`id_attribute` = TAttr.`id_attribute`
    WHERE 
        Cat.`id_parent` != 1 AND 
        ProdLang.`id_lang`=1 AND
        CatLang.`id_lang` = 1 AND
        GroupAttrLang.`id_lang` = 1 AND
        AttrLang.`id_lang` = 1
             GROUP BY TProduct.`reference` ORDER BY TProduct.`reference` ASC;

Everything working except handle a NULL values from a listing above. I tried IF statement result was the same, query skip rows with NULL values. I try make a single query with only one thing to print:

除了处理上面列表中的NULL值之外的所有工作。我试过IF语句结果是一样的,查询跳过具有NULL值的行。我尝试只打印一个要打印的单个查询:

SELECT 
CASE 
    WHEN COUNT(ProdAttr.`id_product`) > 0 THEN COUNT(ProdAttr.`id_product`)
    ELSE 'n/a'
END AS ProdAttrList
FROM `ps_product` AS TProduct 
    LEFT JOIN `ps_product_attribute` AS ProdAttr ON ProdAttr.`id_product`=TProduct.`id_product` 
        GROUP BY TProduct.`reference`;

And that working perfect, see image PrtSc. of last listing

而且工作完美,请参见图片PrtSc。上次上市

1 个解决方案

#1

Must change WHERE statement:

必须更改WHERE语句:

...WHERE
GroupAttrLang.`id_lang` = 1 AND
AttrLang.`id_lang` = 1...

To this:

LEFT JOIN `ps_attribute_group_lang` AS GroupAttrLang ON GroupAttrLang.`id_attribute_group` = TAttr.`id_attribute_group` AND GroupAttrLang.`id_lang` = 1
LEFT JOIN `ps_attribute_lang` AS AttrLang ON AttrLang.`id_attribute` = TAttr.`id_attribute` AND AttrLang.`id_lang` = 1

Problem SOLVED, you can now export catalog of products from your prestashop.

问题已解决,您现在可以从prestashop导出产品目录。

#1