求一查询sql语句,我想了好久
现数据库结果是这样
类别 数据
A ooxx1
A ooxx2
..
..
A ooxxn
B ooxx1
B ooxx2
..
..
B ooxxn
C ooxx1
C ooxx2
..
..
C ooxxn
.
.
.
现在我想ABC类各出5条数据,现在我是每个类别查一次,觉得好没效率,有高手有一次查完的语句没,谢谢了
[解决办法]
declare @t table(type varchar(4),data varchar(8))
insert into @t select 'A ', 'ooxx1 '
insert into @t select 'A ', 'ooxx2 '
insert into @t select 'A ', 'ooxxn '
insert into @t select 'B ', 'ooxx1 '
insert into @t select 'B ', 'ooxx2 '
insert into @t select 'B ', 'ooxxn '
insert into @t select 'C ', 'ooxx1 '
insert into @t select 'C ', 'ooxx2 '
insert into @t select 'C ', 'ooxxn '
--示例:每个type选择列出两条记录
select
t.*
from
@t t
where
t.data in(select top 2 data from @t where type=t.type order by data)
/*
type data
---- --------
A ooxx1
A ooxx2
B ooxx1
B ooxx2
C ooxx1
C ooxx2
*/
[解决办法]
select a.*
from table a , (select 类别 from table group by 类别) b
where a.类别 = b.类别 and a.数据 in (select top 5 数据 from table where 类别 = b.类别)