如何关联数据表按公司+月份分年度展示数据？解决查询缺失记录问题

看起来你在做行转列的透视查询时遇到了数据丢失的问题，我来帮你分析下问题所在，然后给出两种可行的解决方案。

问题分析 #

你的现有查询存在几个关键问题：

• WHERE条件限制了年份：你只筛选了2018年的记录，这直接导致2017年独有的月份（比如公司123的2月）根本不会出现在结果集中
• 关联方式不够合理：用concat(r.company_number,datepart(month,r.date))来关联两个字段不是最优写法，直接用r.company_number = s.company_number AND DATEPART(month, r.date) = DATEPART(month, s.date)更清晰，性能也更好
• 不必要的表拆分：其实没必要把2017年的数据单独存到另一张表，直接从原表处理会更简洁

解决方案一：条件聚合（最常用的透视方法） #

这种方法通过分组和条件判断来实现行转列，兼容性好，几乎所有SQL数据库都支持：

SELECT
    Company_number,
    -- 确保月份是两位格式（如01、02）
    RIGHT('0' + CAST(DATEPART(month, Date) AS VARCHAR(2)), 2) AS Month,
    -- 提取2017年对应月份的值，无数据则为NULL
    MAX(CASE WHEN DATEPART(year, Date) = 2017 THEN Value END) AS Value_2017,
    -- 提取2018年对应月份的值，无数据则为NULL
    MAX(CASE WHEN DATEPART(year, Date) = 2018 THEN Value END) AS Value_2018
FROM YourOriginalTable -- 替换为你的原表名称
GROUP BY Company_number, DATEPART(month, Date), RIGHT('0' + CAST(DATEPART(month, Date) AS VARCHAR(2)), 2)
ORDER BY Company_number, Month;

说明 #

通过GROUP BY按公司编号和月份分组，用CASE语句分别筛选对应年份的数值，MAX()函数用来聚合（因为每个公司+月份+年份只有一条记录，用MAX/MIN/SUM都可以）。这样不管某个月份有没有2017或2018年的数据，都会生成对应的行，不会丢失记录。

解决方案二：生成所有公司+月份组合后左连接 #

这种方法先获取所有存在的公司和月份组合，再分别关联对应年份的数据，逻辑更直观：

-- 第一步：用CTE获取所有唯一的公司+月份组合
WITH AllCompanyMonths AS (
    SELECT DISTINCT
        Company_number,
        RIGHT('0' + CAST(DATEPART(month, Date) AS VARCHAR(2)), 2) AS Month
    FROM YourOriginalTable
)
SELECT
    acm.Company_number,
    acm.Month,
    y2017.Value AS Value_2017,
    y2018.Value AS Value_2018
FROM AllCompanyMonths acm
-- 左连接2017年对应公司和月份的数据
LEFT JOIN YourOriginalTable y2017
    ON acm.Company_number = y2017.Company_number
    AND RIGHT('0' + CAST(DATEPART(month, y2017.Date) AS VARCHAR(2)), 2) = acm.Month
    AND DATEPART(year, y2017.Date) = 2017
-- 左连接2018年对应公司和月份的数据
LEFT JOIN YourOriginalTable y2018
    ON acm.Company_number = y2018.Company_number
    AND RIGHT('0' + CAST(DATEPART(month, y2018.Date) AS VARCHAR(2)), 2) = acm.Month
    AND DATEPART(year, y2018.Date) = 2018
ORDER BY acm.Company_number, acm.Month;

说明 #

先用CTE生成所有出现过的公司+月份组合，然后分别左连接2017和2018年的数据。这种方法能保证所有存在的月份都被包含在结果里，即使某个月份只有2017或只有2018年的数据。

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