SQL 考试啦。。。

Question

-->测试数据
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.002.有三科以上成绩在前两名
NAME COURSE SCORE COUNT
赵六 语文   97.00  
赵六 物理   92.00
赵六 化学   90.00
赵六               3
李四 数学   97.00
李四 化学   87.00
李四 物理   89.00
李四               3看你们了，我没有答案o(╯□╰)o。。有别的变态题目的尽管提啊，有的是牛人！！

Answer 1

-->测试数据
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 行受影响)
*/

Answer 2

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)
*/
第一题的一种答案

Answer 3

楼上贴了。
 我就贴个连接
   http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?48422

Answer 4

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 行受影响)*/

Answer 5

-->测试数据
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.002.有三科以上成绩在前两名;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

Answer 6

第二题一种算法：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)
*/