风中灵药
我的眼泪划伤了夜,那么的脆弱...
2010-04-21 21:08

表:
id   name type

1     a      t1 
2     b      t2
3     c      t3

我想查 各类别的前10条记录 SQL怎么写

测试如下:

create table Product
(
Id int identity primary key,
[Name] varchar(20),
[Type] int not null
)

insert into Product values('a',1)
insert into Product values('b',1)
insert into Product values('c',1)
insert into Product values('d',2)
insert into Product values('e',2)
insert into Product values('f',2)
insert into Product values('g',3)
insert into Product values('h',3)
insert into Product values('i',3)
insert into Product values('j',3)

--//方法1
select * from (
select row_number() over(partition by [Type] order by Id) as RowID,
    [Name],Id,[Type]
     from Product
) t
where t.rowid < 3

--//方法2
select * from Product as t where 
id in (select top 2 id from Product where [type]=t.[Type] order by id)

posted on 2012-02-23 22:27  风中灵药  阅读(280)  评论(0编辑  收藏  举报