转置特定值的SQL查询：需返回全部doc_id而非仅含64、65值的记录

嘿，我懂你现在的困扰了——你的查询现在只返回那些带有64和65对应值的doc_id，但你想要覆盖所有存在的doc_id，哪怕某个doc_id没有64或65的相关记录，对吧？下面给你几个通用的解决方案，适配常见的数据库场景：

核心思路 #

先拿到所有存在的doc_id集合，再通过左连接（LEFT JOIN）关联到64/65的属性数据，这样即使某个doc_id没有对应属性，也会被保留，缺失的属性值会显示为NULL。

方案1：使用CTE+CASE WHEN（通用所有数据库） #

假设你的数据分布在两张表：docs（存储所有doc的主表）和doc_attributes（存储doc的属性，字段为doc_id, attr_id, attr_value），可以这么写：

WITH all_docs AS (
    -- 先获取所有唯一的doc_id，确保不遗漏任何记录
    SELECT DISTINCT doc_id FROM docs
)
SELECT 
    ad.doc_id,
    -- 用CASE WHEN把64和65的属性值分别列出来
    MAX(CASE WHEN da.attr_id = 64 THEN da.attr_value END) AS attribute_64,
    MAX(CASE WHEN da.attr_id = 65 THEN da.attr_value END) AS attribute_65
FROM all_docs ad
-- 左连接确保所有doc_id都被保留
LEFT JOIN doc_attributes da 
    ON ad.doc_id = da.doc_id 
    AND da.attr_id IN (64, 65) -- 只关联需要的属性
GROUP BY ad.doc_id;

方案2：单表场景下的处理 #

如果所有数据都在一张表（比如doc_data，字段为doc_id, attr_id, value），可以直接基于全量doc_id左连：

SELECT 
    ad.doc_id,
    dd.attr_id,
    dd.value
FROM (
    -- 先获取全量doc_id
    SELECT DISTINCT doc_id FROM doc_data
) ad
LEFT JOIN doc_data dd 
    ON ad.doc_id = dd.doc_id 
    AND dd.attr_id IN (64, 65)
ORDER BY ad.doc_id;

如果想把64和65的属性放在同一行，同样可以用CASE WHEN分组：

SELECT 
    ad.doc_id,
    MAX(CASE WHEN dd.attr_id = 64 THEN dd.value END) AS attr_64,
    MAX(CASE WHEN dd.attr_id = 65 THEN dd.value END) AS attr_65
FROM (
    SELECT DISTINCT doc_id FROM doc_data
) ad
LEFT JOIN doc_data dd 
    ON ad.doc_id = dd.doc_id 
    AND dd.attr_id IN (64, 65)
GROUP BY ad.doc_id;

方案3：使用PIVOT（支持的数据库：Oracle、SQL Server等） #

如果你的数据库支持PIVOT语法，可以更简洁地实现列转行，同时保留全量doc_id：

SELECT *
FROM (
    SELECT doc_id, attr_id, value
    FROM doc_data
    WHERE attr_id IN (64, 65)
)
PIVOT (
    MAX(value) FOR attr_id IN (64 AS attr_64, 65 AS attr_65)
) pivoted
-- 右连接全量doc_id，确保没有遗漏
RIGHT JOIN (SELECT DISTINCT doc_id FROM doc_data) all_docs 
    ON pivoted.doc_id = all_docs.doc_id;

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