按年份统计Office创建数量的SQL查询方案咨询

需求与问题 #

• 需求：按年份统计Office的创建数量
• 问题：Offices表无CreationDate字段，需通过LicensePurchases表中对应Office的最早License采购日期判定创建时间，之前的查询未达成目标，需修正逻辑。

表结构 #

Offices表 #

Id  Name
--------------------
1   Office One
133 Maria's Office
230 John's Office

LicensePurchases表 #

Id  OfficeId    PurchaseDate
---------------------------------------------
1      1        2016-11-17 00:00:00.000
2    133        2016-12-23 00:00:00.000
3      1        2017-01-27 00:00:00.000
4    230        2023-04-11 00:00:00.000
5    133        2023-04-12 00:00:00.000
6      1        2023-04-13 00:00:00.000
7      1        2023-04-17 00:00:00.000

之前尝试的查询（修正拼写错误并添加中文注释） #

DECLARE @year int = 2020;

SELECT DISTINCT
    cc.OfficeId AS Office_Id,
    c.Name AS NAME,
    -- 获取指定年份内该Office的首次采购日期
    (SELECT TOP 1 cc.PurchaseDate
     FROM LicensePurchases cc 
     WHERE cc.OfficeId = c.OfficeId 
       AND YEAR(cc.PurchaseDate) = @year 
     ORDER BY cc.PurchaseDate) AS First_Purchase_Date,
    -- 获取该Office的最后一次采购日期
    (SELECT TOP 1 cc.PurchaseDate
     FROM LicensePurchases cc
     WHERE cc.OfficeId = c.OfficeId 
     ORDER BY cc.PurchaseDate DESC) AS Last_Purchase_Date
FROM 
    LicensePurchases cc 
LEFT JOIN 
    Offices c ON c.Id = cc.OfficeId
WHERE 
    c.Status = 18 -- 状态为活跃
    AND YEAR(cc.PurchaseDate) >= @year

原查询的问题 #

1. 未聚焦核心逻辑：没有提取每个Office的最早采购日期作为创建时间，而是关联所有采购记录，用DISTINCT去重效率低且偏离需求
2. 筛选条件错误：YEAR(cc.PurchaseDate) >= @year会漏掉最早采购日期在目标年份之前的Office，无法统计这些Office的创建年份
3. 表名拼写错误：原表名为LicensePurchases，子查询中误写为LicensesPurchase

正确查询方法 #

方法1：统计所有活跃Office的创建年份分布 #

SELECT
    YEAR(FirstPurchaseDate) AS CreationYear,
    COUNT(*) AS OfficeCount
FROM (
    -- 子查询：获取每个活跃Office的最早采购日期（即创建时间）
    SELECT
        o.Id AS OfficeId,
        MIN(lp.PurchaseDate) AS FirstPurchaseDate
    FROM Offices o
    JOIN LicensePurchases lp ON o.Id = lp.OfficeId
    WHERE o.Status = 18 -- 筛选活跃状态的Office
    GROUP BY o.Id
) AS OfficeCreationDates
GROUP BY YEAR(FirstPurchaseDate)
ORDER BY CreationYear;

方法2：查询指定年份创建的活跃Office数量 #

DECLARE @targetYear int = 2016;

SELECT COUNT(*) AS OfficeCountInTargetYear
FROM (
    SELECT o.Id
    FROM Offices o
    JOIN LicensePurchases lp ON o.Id = lp.OfficeId
    WHERE o.Status = 18
    GROUP BY o.Id
    -- 筛选最早采购日期在目标年份的Office
    HAVING YEAR(MIN(lp.PurchaseDate)) = @targetYear
) AS TargetYearOffices;

逻辑说明 #

1. 通过MIN(lp.PurchaseDate)提取每个Office的最早采购日期，以此作为创建时间
2. 关联Offices表筛选出活跃状态（Status=18）的Office
3. 外层查询按年份分组统计数量，或通过HAVING精准筛选指定年份的Office

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