SQLite行转列查询：将产品属性转为列并关联多表

嘿，这个需求其实就是常见的**行转列（透视表）**场景，SQLite虽然没有像部分商业数据库那样内置PIVOT关键字，但咱们用基础的SQL语法就能搞定，分两种场景给你详细说明：

先明确你的表结构 #

先把你给出的三张表结构用代码块整理下，方便对照：

Attributes表 #

CREATE TABLE Attributes (
    id INTEGER PRIMARY KEY,
    Name TEXT
);

数据示例：

Product表 #

CREATE TABLE Product (
    id INTEGER PRIMARY KEY,
    Name TEXT
);

数据示例：

ProductAttribute表 #

CREATE TABLE ProductAttribute (
    id INTEGER PRIMARY KEY,
    productId INTEGER,
    attributeId INTEGER,
    value TEXT,
    FOREIGN KEY(productId) REFERENCES Product(id),
    FOREIGN KEY(attributeId) REFERENCES Attributes(id)
);

数据示例：

场景1：已知所有属性名称（固定属性） #

如果你的属性是固定不变的（比如就att1、att2这几个），直接使用CASE WHEN配合聚合函数（比如MAX）就能实现行转列：

SELECT
    p.id AS product_id,
    p.Name AS product_name,
    -- 为每个属性生成对应的列
    MAX(CASE WHEN a.Name = 'att1' THEN pa.value END) AS att1,
    MAX(CASE WHEN a.Name = 'att2' THEN pa.value END) AS att2
    -- 如果有更多属性，继续添加类似的CASE WHEN行即可
FROM Product p
-- 左连接保证没有属性的产品也能被查询出来
LEFT JOIN ProductAttribute pa ON p.id = pa.productId
LEFT JOIN Attributes a ON pa.attributeId = a.id
-- 按产品分组，把同一产品的属性聚合到一行
GROUP BY p.id, p.Name;

查询结果示例 #

小说明

• 用MAX是因为每个产品对应单个属性只会有一条记录（假设productId+attributeId是唯一组合），聚合函数只是用来过滤掉NULL值，换成MIN或者SUM（如果值是数字类型）也能达到效果。
• 如果只需要查询有属性的产品，把LEFT JOIN换成INNER JOIN即可。

场景2：属性名称动态不确定（可能新增） #

如果属性会随时新增，没办法提前写死所有属性列，SQLite作为静态SQL数据库，没法直接用一条SQL实现动态行转列，这时候需要分两步操作：

第一步：查询所有存在的属性名称 #

先获取当前所有的属性名，用来动态生成SQL：

SELECT Name FROM Attributes;

第二步：用程序动态生成行转列SQL #

比如用Python、Java等后端语言，遍历第一步查询到的属性名，拼接出对应的CASE WHEN子句，最终生成类似场景1的SQL语句，再执行查询。

举个Python伪代码示例：

import sqlite3

conn = sqlite3.connect('your_db.db')
cursor = conn.cursor()

# 获取所有属性名
cursor.execute("SELECT Name FROM Attributes")
attributes = [row[0] for row in cursor.fetchall()]

# 拼接SQL语句
base_sql = """
SELECT
    p.id AS product_id,
    p.Name AS product_name,
    {case_clauses}
FROM Product p
LEFT JOIN ProductAttribute pa ON p.id = pa.productId
LEFT JOIN Attributes a ON pa.attributeId = a.id
GROUP BY p.id, p.Name;
"""

case_clauses = ",\n    ".join([f"MAX(CASE WHEN a.Name = '{attr}' THEN pa.value END) AS {attr}" for attr in attributes])
final_sql = base_sql.format(case_clauses=case_clauses)

# 执行查询
cursor.execute(final_sql)
results = cursor.fetchall()

这样就能自动适配新增的属性，无需手动修改SQL语句。

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