[mysql]SQL 查询审讯问题

标签: sql MySQL
发布时间: 2017/1/9 22:05:45
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

这是我的查询︰

SELECT f.FieldID,fd.FieldName, p.ProductID,pd.ProductName, p.Price,p.Stoc, p.IsActive,TextValue
FROM products_products p
LEFT OUTER JOIN products_products_details pd
on pd.ProductID = p.ProductID
LEFT OUTER JOIN fld_chosenvalues f
on f.ProductID = p.ProductID
INNER JOIN fld_fields_details fd
ON f.FieldID = fd.FieldID
WHERE  pd.Locale = "ro-RO" and fd.Locale = "ro-RO"

在下面的图片中,您可以看到查询的结果︰enter image description here

问题是要把"容积","提示 inchidere","Amabalare paiet","Ambalare bax"作为列值赋给它"212 ml"......你可以看到在下面正是我想要的图像︰

enter image description here

对于图像,很抱歉,但是我不知道任何其他方式来解释的。Thx

这是与数据透视查询,显然不工作︰

SELECT f.FieldID,fd.FieldName, p.ProductID,pd.ProductName, p.Price,p.Stoc, p.IsActive,TextValue
FROM products_products p
LEFT OUTER JOIN products_products_details pd
on pd.ProductID = p.ProductID
LEFT OUTER JOIN fld_chosenvalues f
on f.ProductID = p.ProductID
INNER JOIN fld_fields_details fd
ON f.FieldID = fd.FIeldID
PIVOT
(
    FOR [f.FieldName] IN (['Volum'], ['Tip inchidere'], ['Amabalare palet'], ['Ambalare bax'])
) as pvt;
WHERE  pd.Locale = "ro-RO" and fd.Locale = "ro-RO"

解决方法 1:

也许这样的事情︰

SELECT 
    p.ProductID,
    pd.ProductName,
    SUM(p.Price) AS Price,
    SUM(p.Stoc) AS Stoc,
    p.IsActive,
    MAX(CASE WHEN fd.FieldName='Volum' THEN TextValue ELSE NULL END) AS Volum,
    MAX(CASE WHEN fd.FieldName='Tip inchidere' THEN TextValue ELSE NULL END) AS TipInchidere,  
    MAX(CASE WHEN fd.FieldName='Amabalare paiet' THEN TextValue ELSE NULL END) AS AmabalarePaiet,
    MAX(CASE WHEN fd.FieldName='Ambalare bax' THEN TextValue ELSE NULL END) AS Ambalarebax
FROM 
    products_products p
    LEFT JOIN products_products_details pd
        on pd.ProductID = p.ProductID
    LEFT JOIN fld_chosenvalues f
        on f.ProductID = p.ProductID
    INNER JOIN fld_fields_details fd
        ON f.FieldID = fd.FieldID
WHERE  
    pd.Locale = 'ro-RO' 
    and fd.Locale = 'ro-RO'
GROUP BY
    p.ProductID,
    pd.ProductName,
    p.IsActive
官方微信
官方QQ群
31647020