请教一个sql查询(select语句) (50分)

N

NewLearner

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-20

• #1

在member表中有数字型字段unit,取值1-3,代表3种不同的类别
我想通过一条select查出3种unit的前3条记录(每种unit内按ID号升序),
比如
id unit
------------
10 1
9 1
8 1
14 2
13 2
11 2
5 3
4 3
3 3

注意: unit的类别只是目前有3种,也可能是更多种

 

S

shisam

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-20

• #2

用union就可以了。
如（oracle）
select * from menber where id=1 and rownumber<=3
union
select * from menber where id=2 and rownumber<=3
union
select * from menber where id=3 and rownumber<=3
不过这样好象比较土 ^_^

 

P

pengjinlongex

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-20

• #3

select top 3 d1.id,d1.unit from (select id as id,unit as unit from number where unit=1 order by id) d1
union
select top 3 d2.id,d1.unit from (select id as id,unit as unit from number where unit=2 order by id) d2
union
select top 3 d3.id,d1.unit from (select id as id,unit as unit from number where unit=3 order by id) d3

 

N

NewLearner

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-20

• #4

注意: unit的类别只是目前有3种,也可能是更多种无法固定的情况
为了节省资源,最好只用一个SELECT

 

Q

QuickSilver

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-20

• #5

Select unit,id
From Table1 AA
Where id in (Select Top 3 id From Table1 where unit=AA.unit Order by id)
Order by unit,id

 

N

NewLearner

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-20

• #6

接受答案了.