请教一条简单的select语句
如果我的表table1的结构如下：
studentid studentname score course
01 张三 90 math
01 张三 88 computer
02 李四 60 math
02 李四 61 computer
我想显示成如下形式，怎么写查询语句呢？
studentid studentname score course
01 张三 90 math
88 computer
02 李四 60 math
61 computer
[解决办法]
create table test(studentid varchar(10),studentname varchar(10),score int,course varchar(10))
insert test select '01 ', '张三 ',90, 'math '
union all select '01 ', '张三 ',88, 'computer '
union all select '02 ', '李四 ',60, 'math '
union all select '02 ', '李四 ',61, 'computer '
select studentid=case when not exists(select 1 from test where studentid=a.studentid and course> a.course) then studentid
else ' ' end,
studentname=case when not exists(select 1 from test where studentid=a.studentid and course> a.course) then studentname
else ' ' end,score,course from test a
drop table test
studentid studentname score course
---------- ----------- ----------- ----------
01 张三 90 math
88 computer
02 李四 60 math
61 computer
（所影响的行数为 4 行）
[解决办法]
select * ,IDENTITY(INT,1,1) AS AA INTO #AA from table1
SELECT (CASE WHEN A.AA=B.AA THEN A.studentid ELSE ' ' END ) AS studentid,
(CASE WHEN A.AA=B.AA THEN A.studentname ELSE ' ' END ) AS studentname,A.score,A.course
FROM #AA A LEFT OUTER JOIN (
SELECT studentid,MIN(AA) AS AA
FROM #AA
GROUP BY studentid) B ON A.AA=B.AA
ORDER BY A.AA
--RESULT
01张三88computer
90math
02李四60math
61computer
建议你再加一个ID列，唯一标识一行记录，也就是主键，要不然排序的时候很麻烦，会乱套
