SELECT SITEID,YEAR,max(CLASS) AS CLASS FROM [dbo].[TestTable] GROUP BY YEAR,SITEID

您可以使用窗口功能：

select t.*
from (select t.*,lag(year) over (partition by siteid,class order by year) as prev_year
      from t
     ) t
where prev_year is null or prev_year <> year - 1

这将返回2007年的“ A”和“ B”。这不是您提供的结果，而是您的问题似乎在询问的问题。

请尝试这样做（请用您的表替换临时表）：

如果您使用的是SQL Server，则可以使用通用表表达式（CTE）作为打击：

 IF (OBJECT_ID('tempdb..#temp_table') IS NOT NULL)
    BEGIN
      DROP TABLE #temp_table
    END;

CREATE TABLE #temp_table (SiteId INT NOT NULL,[year] INT,[class] [Char] NOT NULL)

INSERT INTO #temp_table (SiteId,[year],[class])
       values 
(1,2007,'A'),(1,'B'),2008,'C')


;with temp_cte as
  (SELECT  siteid,[year]-1 as [yearbefore],STUFF((SELECT '|' + CAST([class] AS VARCHAR(MAX)) [text()]
            FROM #temp_table 
            WHERE SiteId = t.SiteId and [year] = t.[year]
            FOR XML PATH(''),TYPE)
            .value('.','NVARCHAR(MAX)'),1,2,' ') [class_list]
            FROM #temp_table t
            GROUP BY SiteId,[year]
)
select c.SiteId,c.[year],case when t.yearbefore = null then null else right(REPLACE(c.class_list,t.class_list,''),LEN(REPLACE(c.class_list,''))-1) end as [class_added],case when t.yearbefore = null then null else stuff(REPLACE(c.class_list,charindex('|',REPLACE(c.class_list,'')),'') end as [class_added_using_stuff]
 from temp_cte c
left join temp_cte t on c.[yearbefore] = t.[year]

如果您不使用SQL Server（以下内容也适用于SQL Server），则可以使用联接（可能需要调整所使用的RDBMS的查询），如下所示：

select distinct t1.SiteId,t1.[year],case when t1.[year] = m.[year] then null else t1.[class] end as class_added
    from #temp_table t1
    left join #temp_table t2 on t1.SiteId = t2.SiteId and t1.class = t2.class and t1.[year] -1 = t2.[year]
    left join (select top 1 * from #temp_table order by [year] ) m on t1.[year] = m.[year]
where t2.SiteId is null

MYSQL更新：

CREATE TABLE test_table(
    SiteId INT NOT NULL,year INT,class CHAR(1)
);

INSERT INTO 
    test_table(SiteId,year,class)
VALUES
    (1,'C');
select distinct t1.SiteId,t1.year,case when t1.year = m.year then null else t1.class end as class_added
    from test_table t1
    left join test_table t2 on t1.SiteId = t2.SiteId and t1.class = t2.class and t1.year -1 = t2.year
    left join (select * from test_table order by year limit 1) m on t1.year = m.year
where t2.SiteId is null;

MY SQL小提琴here（http://sqlfiddle.com/#!9/c570d57/1/0）