帮我写一个SQL语句
我有两个表
表1
stu_num course_code course_score
0001 123 85
0001 125 75
0001 126 98
0002 123 78
0002 125 74
0002 126 84
表2
stu_num course_code course_score
我想将表1中每个人的course_score的最高2条记录给插入到表2中
结果为:
stu_num course_code course_score
0001 123 85
0001 126 98
0002 123 78
0002 126 84
问题点数：0、回复次数：8Top
1 楼yesterday2000（一笑而过）回复于 2004-09-04 13:06:04 得分 0
insert into 表2
select stu_num,course_code,course_score from 表1 a where a.stu_num in (select top 2 stu_num from 表1 where stu_num=a.stu_num
order by stu_num desc,course_score desc) order by stu_num desc,course_score descTop
2 楼yesterday2000（一笑而过）回复于 2004-09-04 13:11:47 得分 0
我上面的写错了
select stu_num,course_code,course_score from pm a where a.course_code in (select top 2 course_code from pm where stu_num=a.stu_num
order by stu_num desc,course_score desc) order by stu_num ,course_score desc
Top
3 楼yesterday2000（一笑而过）回复于 2004-09-04 13:13:05 得分 0
create table pm (stu_num char (4),course_code char (3),course_score decimal (12,2))
insert into pm
select 0001 , 123 , 85
union
select 0001 , 125 , 75
union
select 0001 , 126 , 98
union
select 0002 , 123 , 78
union
select 0002 , 125 , 74
union
select 0002 , 126 , 84
insert into 表2
select stu_num,course_code,course_score from pm a where a.course_code in (select top 2 course_code from pm where stu_num=a.stu_num
order by stu_num desc,course_score desc) order by stu_num ,course_score
select * from 表2
-----------
stu_num course_code course_score
------- ----------- ------------
0001 123 85
0001 126 98
0002 123 78
0002 126 84Top
4 楼wenqiangs1（aa）回复于 2004-09-04 15:18:56 得分 0
一笑而过，厉害了，用子查询调出stu_num 的TOP 2 简直高人一筹，佩服佩服Top
5 楼zhouxinjie（zhouwei）回复于 2004-09-04 17:30:02 得分 0
假如表1中记录中stu_num,course_code中有重复的情况怎么办?
stu_num course_code course_score
0001 123 85
0001 125 75
0001 126 98
0001 123 60 后有增加的记录
0002 123 78
0002 125 74
0002 126 84
不知道这样改怎么处理!Top
6 楼qiliu（痴心求学）回复于 2004-09-04 17:55:55 得分 0
建立临时表,增加不重复的一列
替换course_codeTop
7 楼yesterday2000（一笑而过）回复于 2004-09-04 18:03:05 得分 0
你是达到你想要的效果是吗？
insert into 表2
select stu_num,course_code,max(course_score) as course_score from pm a where a.course_code in (select top 2 course_code from pm where stu_num=a.stu_num
order by stu_num desc,course_score desc)
group by stu_num,course_code order by stu_num ,course_scoreTop
8 楼bernice99（宝宝）回复于 2004-09-06 18:17:28 得分 0
insert into table2
select stu_num,course_code,course_score from table1 where course_score in (select top 2 course_score from table1 group by stu_num order by stu_num,couese_score desc)
order by stu_num,course_scoreTop
