如果我的表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
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
解决方案 »
- SQL Server的表中怎样知道一个字段中保存的值有中文字符
- DB
- 通用报表问题
- SQL2000 一个最近简单的 排序,为什么不能成功?
- 这个条件为什么错啊where case when @stdate is null then '' when @stdate is not null then ( tradedate between @stdate and @enddat
- 创建作业的问题(十分古怪),泣求答案,大把散分
- [SQLServer 2000 Driver for JDBC]Error establishing socket
- 如何按自己想要的顺序排序?
- 继续:关于一条费解的Sql语句(2)
- 如何用SQL计算年龄
- sql统计问题!
- 动态sql语句
#t
Set
studentid = Null,
studentname = Null,
Where
score Not In (Select Min(score) From 表 Group By studentid, studentname)go
select * from #t
union
select null as studentid,null as studentname,score,course from t a where exists(select 1 from t where a.studentid=studentid and a.studentname=studentname and a.score>score)
order by scorestudentid studentname score course
--------- ----------- ------ ----------
2 李四 60 math
NULL NULL 61 computer
1 张三 88 computer
NULL NULL 90 math(所影响的行数为 4 行)
感觉不理想,等待高人
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 adrop table teststudentid studentname score course
---------- ----------- ----------- ----------
01 张三 90 math
88 computer
02 李四 60 math
61 computer(所影响的行数为 4 行)
(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 张三 88 computer
90 math
02 李四 60 math
61 computer建议你再加一个ID列,唯一标识一行记录,也就是主键,要不然排序的时候很麻烦,会乱套
set studentid=null,studentname=null
from t a
where exists(select 1 from t where a.studentid=studentid and a.studentname=studentname and a.score<score)就象lwl0606(寒泉) 所说 再加一个ID列,唯一标识一行记录,也就是主键,要不然排序的时候很麻烦,会乱套