求个sql

[「已注销」]

create table msdevice (
msid INT not null,
dcid INT,
weight DOUBLE default 0.000,
name VARCHAR(255),
primary key (msid)
);

怎样用一个sql查出，每种dcid中，weight最大的前三个msid(也就是说，数据条数，应该是dcid的种数×3)

[ABLE__323]

insert into msdevice values(1,1,1.0,'1')
insert into msdevice values(2,2,2.0,'2')
insert into msdevice values(3,3,3.0,'3')
insert into msdevice values(4,4,4.0,'4')
insert into msdevice values(5,5,5.0,'5')
insert into msdevice values(6,6,6.0,'6')

select top 3 dcid from msdevice order by weight desc

--------------------------------
dcid
6
5
4

[wzy_love_sly]

楼上错了

[liangCK]

select * from tb a where weight in(select top 3 weight from tb where a.dcid=dcid order by weight desc)

[wzy_love_sly]

create   table   tt   (
a int,b int, c varchar(10)

)
insert into tt select 1,1,'aa'
insert into tt select 1,2,'aa'
insert into tt select 1,3,'aa'
insert into tt select 1,4,'aa'
insert into tt select 2,1,'bb'
insert into tt select 2,2,'bb'
insert into tt select 2,3,'bb'
insert into tt select 2,4,'bb'

select distinct a ,b,c from tt a
where b in(select top 3 b from tt where a=a.a)

1 1 aa
1 2 aa
1 3 aa
2 1 bb
2 2 bb
2 3 bb

[ABLE__323]

insert into msdevice values(1,1,1.0,'1')
insert into msdevice values(2,1,2.0,'2')
insert into msdevice values(3,1,3.0,'3')
insert into msdevice values(4,1,4.0,'4')
insert into msdevice values(5,1,5.0,'5')
insert into msdevice values(6,1,6.0,'6')

insert into msdevice values(7,2,1.0,'1')
insert into msdevice values(8,2,2.0,'2')
insert into msdevice values(9,2,3.0,'3')
insert into msdevice values(10,2,4.0,'4')
insert into msdevice values(11,2,5.0,'5')
insert into msdevice values(12,2,6.0,'6')

insert into msdevice values(13,3,1.0,'1')
insert into msdevice values(14,3,2.0,'2')
insert into msdevice values(15,3,3.0,'3')
insert into msdevice values(16,3,4.0,'4')
insert into msdevice values(17,3,5.0,'5')
insert into msdevice values(18,3,6.0,'6')

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select top 3 [msid] from (select top 3 [msid],weight from msdevice where dcid='
+cast(dcid as varchar(16))+' order by [weight] desc)tb'+cast(dcid as varchar(16))
from msdevice
group by dcid
exec (@s)
--------------------------------
msid
6
5
4
12
11
10
18
17
16

[ABLE__323]

我看错题目了。。

[「已注销」]

wzy_love_sly:
我的mysql 5.0.45-community怎么不支持top，你用的是mysql吗，或者是mysql跟新的版本。
好像mysql不能用一个sql，实现这个功能。

有个哥们给了个oracle的：
select dcid,weight from (select dcid,weight,row_number() over(partition by dcid order by weight desc)rn from msdevice) where rn<4;

可惜mysql不支持partition这个功能

[wzy_love_sly]

是sql05,mysql的我不会 sorry