SQL 程序 查询 语句

来源：百度知道 编辑：UC知道 时间：2024/06/16 11:53:59
哪位大侠帮帮忙
我用SQL 2000
我建了一个Music表,表里有以下有属性:title(歌名)category(音乐类型),artist(艺术家),vote(得票)
数据如下
1 aaa aa 14
2 aaa dd 13
4 aaa aa 14
5 bbb ee 15
6 bbb bb 13
8 bbb aa 15
11 ccc dd 12
12 ccc dd 16
13 ccc aa 13
9 ddd bb 12
3 ddd cc 17
16 ddd aa 16
我要怎么写查询语句,才能得出每个category(音乐类型)中得票数最多的艺术家呢?
怎样才能得出的结果是:
aaa aa 28
bbb ee 15
ccc dd 28
ddd cc 17

mailking2001的正是我要的结果.xxxk-l,eart211的虽然报错,还是要说声谢谢,其实是我不是很会改,应该离结果不远了;lvphotoshop的得出了每个类型最高票的歌曲,为我下一个功能提供了帮助;gg152326谢谢你的建议,给我指明了解决问题的方向.

select b.* from (select b.category ,MAX(b.vote) as vote from (select category ,artist , sum(vote) as vote from Music group by
category ,artist) b group by b.category ) a ,(select category ,artist , sum(vote) as vote from Music group by category ,artist) b where a.category =b.category and a.vote =b.vote

你给出的数据会出现相同category(音乐类型)和vote(得票)，不同artist(艺术家)的情况，对于这种情况从你给出的最终结果看，是取artist字段的最大值
所以上面的语句可以改成

select b.category ,max(artist), b.vote from (select b.category ,MAX(b.vote) as vote from (select category ,artist , sum(vote) as vote from Music group by category ,artist) b group by b.category ) a ,(select category ,artist , sum(vote) as vote from Music group by category ,artist) b where a.category =b.category and a.vote =b.vote
group by b.category ,b.vote

select a.category,b.artist from

(select category,max(vote) as vote from
(select category,artist,sum(vote) as vote from music group by category,artist)) a,

(select ca