SQL 考试啦。。。

hokor 2010-07-16 02:36:19 
-->测试数据

IF OBJECT_ID('tempdb..#TB') IS NULL

CREATE TABLE #TB (NAME VARCHAR(10),COURSE VARCHAR(10),SCORE DECIMAL(8,2))

ELSE TRUNCATE TABLE #TB

INSERT INTO #TB (NAME,COURSE,SCORE)

SELECT '张三','数学',98 UNION ALL

SELECT '张三','语文',89 UNION ALL

SELECT '张三','物理',78 UNION ALL

SELECT '张三','化学',67 UNION ALL

SELECT '李四','数学',97 UNION ALL

SELECT '李四','语文',78 UNION ALL

SELECT '李四','物理',89 UNION ALL

SELECT '李四','化学',87 UNION ALL

SELECT '王五','数学',98 UNION ALL

SELECT '王五','语文',87 UNION ALL

SELECT '王五','物理',86 UNION ALL

SELECT '王五','化学',87 UNION ALL

SELECT '赵六','数学',86 UNION ALL

SELECT '赵六','语文',97 UNION ALL

SELECT '赵六','物理',92 UNION ALL

SELECT '赵六','化学',90 



--SELECT * FROM #TB 

1. 按照课程分组，取分数最高的前两名

结果：

COURSE NAME	SCORE

语文   赵六	97.00

语文   张三	89.00

数学   张三	98.00

数学   王五	98.00

数学   李四	97.00

化学   赵六	90.00

化学   王五	87.00

化学   李四	87.00

物理   赵六	92.00

物理   李四	89.00



2.有三科以上成绩在前两名

NAME COURSE SCORE COUNT

赵六 语文   97.00  

赵六 物理   92.00

赵六 化学   90.00

赵六               3

李四 数学   97.00

李四 化学   87.00

李四 物理   89.00

李四               3


看你们了，我没有答案o(╯□╰)o。。

有别的变态题目的尽管提啊，有的是牛人！！
...全文
157 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
hokor 2010-07-17
  • 打赏
  • 举报
 回复
第二题一种算法：


WITH tb AS(

SELECT COURSE,NAME,SCORE FROM (

SELECT DENSE_RANK() OVER(PARTITION BY COURSE ORDER BY SCORE DESC) AS SCORE_ORDER ,* 

  FROM #TB ) AS t

 WHERE t.SCORE_ORDER <=2 

 --ORDER BY COURSE,SCORE DESC,NAME

 )

 SELECT a.NAME,a.COURSE,CONVERT(VARCHAR,a.SCORE) SCORE,'' COUNT FROM tb a 

  WHERE a.NAME IN (SELECT NAME FROM tb GROUP BY NAME HAVING COUNT(1) >= 3)

  UNION ALL

 SELECT NAME,'' COURSE,'' SCORE,CONVERT(VARCHAR,COUNT(1)) COUNT FROM tb GROUP BY NAME HAVING COUNT(1) >= 3

  ORDER BY NAME DESC,SCORE DESC

/*

NAME       COURSE     SCORE                          COUNT

---------- ---------- ------------------------------ ------------------------------

赵六         语文         97.00                          

赵六         物理         92.00                          

赵六         化学         90.00                          

赵六                                                   3

李四         数学         97.00                          

李四         物理         89.00                          

李四         化学         87.00                          

李四                                                   3



(8 row(s) affected)





*/
da21 2010-07-17
  • 打赏
  • 举报
 回复
学习学习
飘零一叶 2010-07-17
  • 打赏
  • 举报
 回复
路过学习
jaydom 2010-07-16
  • 打赏
  • 举报
 回复 


-->测试数据

IF OBJECT_ID('tempdb..#TB') IS NULL

CREATE TABLE #TB (NAME VARCHAR(10),COURSE VARCHAR(10),SCORE DECIMAL(8,2))

ELSE TRUNCATE TABLE #TB

INSERT INTO #TB (NAME,COURSE,SCORE)

SELECT '张三','数学',98 UNION ALL

SELECT '张三','语文',89 UNION ALL

SELECT '张三','物理',78 UNION ALL

SELECT '张三','化学',67 UNION ALL

SELECT '李四','数学',97 UNION ALL

SELECT '李四','语文',78 UNION ALL

SELECT '李四','物理',89 UNION ALL

SELECT '李四','化学',87 UNION ALL

SELECT '王五','数学',98 UNION ALL

SELECT '王五','语文',87 UNION ALL

SELECT '王五','物理',86 UNION ALL

SELECT '王五','化学',87 UNION ALL

SELECT '赵六','数学',86 UNION ALL

SELECT '赵六','语文',97 UNION ALL

SELECT '赵六','物理',92 UNION ALL

SELECT '赵六','化学',90 



SELECT * FROM #TB 





1. 按照课程分组，取分数最高的前两名

select course, NAME,SCORE

from #TB

order by COURSE desc,SCORE desc

结果：

COURSE NAME    SCORE

语文   赵六    97.00

语文   张三    89.00

数学   张三    98.00

数学   王五    98.00

数学   李四    97.00

化学   赵六    90.00

化学   王五    87.00

化学   李四    87.00

物理   赵六    92.00

物理   李四    89.00



2.有三科以上成绩在前两名



;with t

as

(

	select *,rn= dense_RANK() over(partition by course order by score desc)

	from #TB

),t1

as

(

	select NAME,COURSE,SCORE 

	from t 

	where rn<3

),t3

as

(

select NAME,COURSE,SCORE

from t1 a

where exists(select 1 from t1 where a.NAME=NAME group by NAME having COUNT(COURSE)>=3) 

group by NAME,COURSE,SCORE with rollup

)

select name,isnull(cast(course as varchar(10)),'') course, isnull(cast(score as varchar(10)),'') score, 

       case when SCORE IS not null then '' 

       else cast((select COUNT(course) from t1 where name=t3.name) as varchar(5)) end as COUNT

from t3

where SCORE is not null or  (SCORE is null and (COURSE is null and NAME is not null))





name	course	score	COUNT

李四	化学	87.00	

李四	数学	97.00	

李四	物理	89.00	

李四			         3

赵六	化学	90.00	

赵六	物理	92.00	

赵六	语文	97.00	

赵六			        3
jellodgd 2010-07-16
  • 打赏
  • 举报
 回复
不会= =
hokor 2010-07-16
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 xys_777 的回复:]
SQL code
-->测试数据
IF OBJECT_ID('tempdb..#TB') IS NULL
CREATE TABLE #TB (NAME VARCHAR(10),COURSE VARCHAR(10),SCORE DECIMAL(8,2))
ELSE TRUNCATE TABLE #TB
INSERT INTO #TB (NAME,COURSE,SCORE)
SELECT ……
[/Quote]
这个为什么会少一条
李四 化学 87.00
骑驴快跑 2010-07-16
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 yuan20091017 的回复:]
楼上贴了。
我就贴个连接
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?48422
[/Quote]

我喜欢这个。。。
永生天地 2010-07-16
  • 打赏
  • 举报
 回复 


with a as(

select rn=dense_rank()over(partition by COURSE order by score desc),* from #tb

) 

select * from a where rn<3

order by course

/*

rn                   NAME       COURSE     SCORE

-------------------- ---------- ---------- ---------------------------------------

1                    赵六         化学         90.00

2                    王五         化学         87.00

2                    李四         化学         87.00

1                    张三         数学         98.00

1                    王五         数学         98.00

2                    李四         数学         97.00

1                    赵六         物理         92.00

2                    李四         物理         89.00

1                    赵六         语文         97.00

2                    张三         语文         89.00



(10 行受影响)



*/
请叫我潇洒哥 2010-07-16
  • 打赏
  • 举报
 回复
楼上贴了。
我就贴个连接
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?48422
hokor 2010-07-16
  • 打赏
  • 举报
 回复 
SELECT COURSE,NAME,SCORE FROM (

SELECT DENSE_RANK() OVER(PARTITION BY COURSE ORDER BY SCORE DESC) AS SCORE_ORDER ,* 

FROM #TB ) t 

WHERE t.SCORE_ORDER <=2 

ORDER BY COURSE,SCORE DESC,NAME



/*

COURSE     NAME       SCORE

---------- ---------- ---------------------------------------

化学         赵六         90.00

化学         李四         87.00

化学         王五         87.00

数学         王五         98.00

数学         张三         98.00

数学         李四         97.00

物理         赵六         92.00

物理         李四         89.00

语文         赵六         97.00

语文         张三         89.00



(10 row(s) affected)

*/

第一题的一种答案。。。
永生天地 2010-07-16
  • 打赏
  • 举报
 回复 
-->测试数据

IF OBJECT_ID('tempdb..#TB') IS NULL

CREATE TABLE #TB (NAME VARCHAR(10),COURSE VARCHAR(10),SCORE DECIMAL(8,2))

ELSE TRUNCATE TABLE #TB

INSERT INTO #TB (NAME,COURSE,SCORE)

SELECT '张三','数学',98 UNION ALL

SELECT '张三','语文',89 UNION ALL

SELECT '张三','物理',78 UNION ALL

SELECT '张三','化学',67 UNION ALL

SELECT '李四','数学',97 UNION ALL

SELECT '李四','语文',78 UNION ALL

SELECT '李四','物理',89 UNION ALL

SELECT '李四','化学',87 UNION ALL

SELECT '王五','数学',98 UNION ALL

SELECT '王五','语文',87 UNION ALL

SELECT '王五','物理',86 UNION ALL

SELECT '王五','化学',87 UNION ALL

SELECT '赵六','数学',86 UNION ALL

SELECT '赵六','语文',97 UNION ALL

SELECT '赵六','物理',92 UNION ALL

SELECT '赵六','化学',90 



SELECT * FROM #TB t

where (select count(1) from #TB where COURSE=t.COURSE and SCORE>t.SCORE)<=1

order by COURSE

/*



(16 行受影响)

NAME       COURSE     SCORE

---------- ---------- ---------------------------------------

李四         化学         87.00

王五         化学         87.00

赵六         化学         90.00

王五         数学         98.00

张三         数学         98.00

李四         物理         89.00

赵六         物理         92.00

赵六         语文         97.00

张三         语文         89.00



(9 行受影响)





*/
JSP+mysql（sqlserver2000或2005）在线考试系统源码
在线考试系统源码，包括题库管理和在线考试功能模块。即支持MYsql，也支持sqlserver2005数据库。
sql2000学生管理数据库
sql2000学生管理数据库 这是个备份的数据库，用于练习SQL语句，在SQL2000中恢复该数据库就可以使用里面的数据了，注意恢复时在选项里要更改一下恢复的路径，还要更改恢复后的数据库的名字，里面有三张表student,...
一道题涉及到50个SQL语句
一道题包含50句SQL 数据库公司招收员工考试必备
Asp.net在线考试系统源码
一个功能比较全的Asp.net在线考试系统源码，可以实现单选题、多选题维护、填空题、判断题的在线维护和管理，添加生成试卷后可以自动从题库进行调用试题，考试完成后自动打分，后台可以进行统计。 后台模块包括： ...
在线考试系统C# 源代码
（C# 2.0+Microsoft SQL Server 2000 实现） 传统考试要求老师打印试卷、安排考试、监考、收集试卷、评改试卷、讲评试 卷和分析试卷。这是一个漫长而复杂的过程，已经越来越不适应现代教学的需要。 在线考试系统是...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章