表 a
id title class
1 aa 1
2 bb 2
3 cc 3
4 dd 2
5 ee 3
6 ff 2
7 gg 2一句 sql 查询 class 1 2 3 每个类别 的前4条数据 order by id desc
select top 4 id,title from a where class=1
union all
select top 4 id,title from a where class=2 order by id desc
这样有错误 因为排序 只按 class=2 排
id title class
1 aa 1
2 bb 2
3 cc 3
4 dd 2
5 ee 3
6 ff 2
7 gg 2一句 sql 查询 class 1 2 3 每个类别 的前4条数据 order by id desc
select top 4 id,title from a where class=1
union all
select top 4 id,title from a where class=2 order by id desc
这样有错误 因为排序 只按 class=2 排
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-12 09:39:20
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[title] NVARCHAR(10),[class] INT)
INSERT [tb]
SELECT 1,'aa',1 UNION ALL
SELECT 2,'bb',2 UNION ALL
SELECT 3,'cc',3 UNION ALL
SELECT 4,'dd',2 UNION ALL
SELECT 5,'ee',3 UNION ALL
SELECT 6,'ff',2 UNION ALL
SELECT 7,'gg',2
GO
--SELECT * FROM [tb]-->SQL查询如下:
--2000/2005通用
select * from tb t where id in(select top 4 id from tb where [title]=t.[title] order by id desc)
SELECT * FROM a t1 WHERE id IN(SELECT TOP 4 id FROM a WHERE class=t1.class ORDER BY id DESC )
--1
select t.* from a t where id in (select top 4 from a where class = t.class order by id)--2
select id , title , class from
(
select t.* , px = (select count(1) from a where class = t.class and id < t.id) + 1 from a t
) m
where id <= 4--sql 2005
select id , title , class from
(
select t.* , px = row_number() over(partition by class order by id) from a t
) m
where id <= 4