select a.* from yourTable a inner jion (select Class,max(ID) as maxID from yourTable group by Class) b on a.ID=b.ID
CREATE TABLE #news ( [ID] INT IDENTITY(1,1), [Class] VARCHAR(10), [Title] VARCHAR(20), [Content] VARCHAR(50) ) GO INSERT INTO #news([Class],[Title],[Content]) SELECT '类别1','新闻71','内容1' UNION ALL SELECT '类别1','新闻2','内容1' UNION ALL SELECT '类别1','新闻3','内容1' UNION ALL SELECT '类别1','新闻4','内容1' UNION ALL SELECT '类别1','新闻5','内容1' UNION ALL SELECT '类别1','新闻6','内容1' UNION ALL SELECT '类别1','新闻7','内容1' UNION ALL SELECT '类别2','新闻8','内容1' UNION ALL SELECT '类别2','新闻9','内容1' UNION ALL SELECT '类别2','新闻10','内容1' GO /*查询*/ SELECT[#news].*FROM[#news]JOIN(SELECT MIN([id])AS[fId]FROM[#news]GROUP BY[Class])[t]ON[#news].[ID]=[t].[fID] GO DROP TABLE #news/* ID Class Title Content ----------- ---------- -------------------- -------------------------------------------------- 1 类别1 新闻71 内容1 8 类别2 新闻8 内容1(所影响的行数为 2 行) */
--借用楼上的数据 CREATE TABLE #news ( [ID] INT IDENTITY(1,1), [Class] VARCHAR(10), [Title] VARCHAR(20), [Content] VARCHAR(50) ) GO INSERT INTO #news([Class],[Title],[Content]) SELECT '类别1','新闻71','内容1' UNION ALL SELECT '类别1','新闻2','内容1' UNION ALL SELECT '类别1','新闻3','内容1' UNION ALL SELECT '类别1','新闻4','内容1' UNION ALL SELECT '类别1','新闻5','内容1' UNION ALL SELECT '类别1','新闻6','内容1' UNION ALL SELECT '类别1','新闻7','内容1' UNION ALL SELECT '类别2','新闻8','内容1' UNION ALL SELECT '类别2','新闻9','内容1' UNION ALL SELECT '类别2','新闻10','内容1' GOselect * from #news as tmp where not exists(select * from #news where Class=tmp.Class and ID<tmp.ID)--result ID Class Title Content ----------- ---------- -------------------- -------------------------------------------------- 1 类别1 新闻71 内容1 8 类别2 新闻8 内容1(2 行受影响)
from yourTable a inner jion (select Class,max(ID) as maxID from yourTable group by Class) b
on a.ID=b.ID
(
[ID] INT IDENTITY(1,1),
[Class] VARCHAR(10),
[Title] VARCHAR(20),
[Content] VARCHAR(50)
)
GO
INSERT INTO #news([Class],[Title],[Content])
SELECT '类别1','新闻71','内容1' UNION ALL
SELECT '类别1','新闻2','内容1' UNION ALL
SELECT '类别1','新闻3','内容1' UNION ALL
SELECT '类别1','新闻4','内容1' UNION ALL
SELECT '类别1','新闻5','内容1' UNION ALL
SELECT '类别1','新闻6','内容1' UNION ALL
SELECT '类别1','新闻7','内容1' UNION ALL
SELECT '类别2','新闻8','内容1' UNION ALL
SELECT '类别2','新闻9','内容1' UNION ALL
SELECT '类别2','新闻10','内容1'
GO
/*查询*/
SELECT[#news].*FROM[#news]JOIN(SELECT MIN([id])AS[fId]FROM[#news]GROUP BY[Class])[t]ON[#news].[ID]=[t].[fID]
GO
DROP TABLE #news/*
ID Class Title Content
----------- ---------- -------------------- --------------------------------------------------
1 类别1 新闻71 内容1
8 类别2 新闻8 内容1(所影响的行数为 2 行)
*/
--借用楼上的数据
CREATE TABLE #news
(
[ID] INT IDENTITY(1,1),
[Class] VARCHAR(10),
[Title] VARCHAR(20),
[Content] VARCHAR(50)
)
GO
INSERT INTO #news([Class],[Title],[Content])
SELECT '类别1','新闻71','内容1' UNION ALL
SELECT '类别1','新闻2','内容1' UNION ALL
SELECT '类别1','新闻3','内容1' UNION ALL
SELECT '类别1','新闻4','内容1' UNION ALL
SELECT '类别1','新闻5','内容1' UNION ALL
SELECT '类别1','新闻6','内容1' UNION ALL
SELECT '类别1','新闻7','内容1' UNION ALL
SELECT '类别2','新闻8','内容1' UNION ALL
SELECT '类别2','新闻9','内容1' UNION ALL
SELECT '类别2','新闻10','内容1'
GOselect * from #news as tmp
where not exists(select * from #news where Class=tmp.Class and ID<tmp.ID)--result
ID Class Title Content
----------- ---------- -------------------- --------------------------------------------------
1 类别1 新闻71 内容1
8 类别2 新闻8 内容1(2 行受影响)