如何在EAV结构表中筛选指定属性产品的全属性数据？

Hey there! Let's work through this problem where we need to pull all attributes for products with a red color, converting the EAV (Entity-Attribute-Value) table into a wide, row-per-product format—without altering the existing table structure.

方法1：通用条件聚合（适用于绝大多数数据库） #

This approach works across MySQL, PostgreSQL, SQL Server, and more, since it uses standard SQL syntax.

SELECT
    productId,
    MAX(CASE WHEN property = 'color' THEN value END) AS color,
    MAX(CASE WHEN property = 'shape' THEN value END) AS shape,
    -- 若value是字符串类型，可转换为数值类型保证格式正确，比如MySQL：
    -- CAST(MAX(CASE WHEN property = 'price' THEN value END) AS DECIMAL(10,2)) AS price
    MAX(CASE WHEN property = 'price' THEN value END) AS price
FROM
    products
WHERE
    -- 先筛选出所有color为red的产品ID
    productId IN (SELECT productId FROM products WHERE property = 'color' AND value = 'red')
GROUP BY
    productId;

怎么理解这段代码？ #

• The subquery first grabs all productIds where the color is red—this ensures we only focus on the products we care about.
• The outer query uses CASE WHEN paired with MAX to "pivot" the rows into columns. Since each product only has one value per property, MAX just filters out the NULL values from unmatched properties, leaving us with the correct value for each column.

方法2：PIVOT语法（SQL Server专用） #

If you're working with SQL Server, you can use the built-in PIVOT function for a more concise query:

SELECT
    productId,
    color,
    shape,
    price
FROM (
    -- 先过滤出目标产品的所有属性行
    SELECT productId, property, value
    FROM products
    WHERE productId IN (SELECT productId FROM products WHERE property = 'color' AND value = 'red')
) AS SourceTable
PIVOT (
    MAX(value)
    -- 指定要转换为列的属性
    FOR property IN (color, shape, price)
) AS PivotTable;

关键注意点 #

• Both methods require you to know all the properties you want to convert into columns upfront. If your properties are dynamic (e.g., new attributes get added over time), you'll need to use dynamic SQL to generate the column list automatically.
• If the value column stores all data as strings, don't forget to cast numeric fields like price to the appropriate data type to avoid formatting issues.

内容的提问来源于stack exchange，提问作者Artemios Antonio Balbach