sql实例：成绩排名，日期函数及格式转换

--1.按各种技术的平均分从高到低排列:平均分,师傅姓名,技术名称(检验哪个师傅教的好)
select AVG(result) as avg_result,master_name,course_name from TEST_TABLE
group by course_name,master_name
order by avg(result) desc;

--2.排列各种技术成绩最高和最低的相关记录: (就是各中技术的最高、最低分的徒弟和师傅) 
--技术ID,技术名称,师傅姓名,徒弟姓名,最低分,徒弟姓名,最高分
--select course_id,course_name,master_name,MIN(result) as min_result,MAX(result) as max_result from TEST_TABLE
--group by course_id,course_name,master_name;

SELECT Y.N1 AS course_id,Y.N2 as course_name,Y.N4 as master_name,x.m4 as apprentice_name,X.M3 AS min_result,Y.N3 as apprentice_name,Y.N5 as max_result from
(select t4.course_id as M1,t4.master_name AS M2,t3.b AS M3,t4.apprentice_name as M4
from TEST_TABLE t4,
(select course_id,min(result) as b from TEST_TABLE group by course_id)as t3
where t4.course_id=t3.course_id and t4.result=t3.b 
)as X,
(select t1.course_id AS N1,t1.course_name AS N2,t1.apprentice_name AS N3,t1.master_name AS N4,t2.a AS N5
from TEST_TABLE t1,
(select course_id,max(result) as a from TEST_TABLE group by course_id)as t2
where t1.course_id=t2.course_id and t1.result=t2.a 
)as Y 
WHERE X.M1=Y.N1

--3.按平均成绩从高到低顺序,排列所有徒弟的四种技术成绩: (就是每个徒弟的四种技术的成绩单)
--徒弟ID,徒弟姓名,探伤,除锈,涂镀,打磨,有效技术,有效平均分
select apprentice_id,apprentice_name,avg(Result)as av from TEST_TABLE
group by apprentice_id,apprentice_name

SELECT apprentice_id,apprentice_name,
      isnull(Sum(case course_id when 'C1' then result end),0) as crack_detection,
      isnull(Sum(case course_id when 'C2' then result end),0) as eliminate_rust,
      isnull(Sum(case course_id when 'C3' then result end),0) as plation,
      isnull(Sum(case course_id when 'C4' then result end),0) as polish,
      isnull(Sum(case when(result is not null) then 1 end),0) as amount,
      AVG(result)as av
  FROM TEST_TABLE
  group by apprentice_id,apprentice_name
  order by av
  
 --4.按各种技术平均成绩从低到高和不及格率的百分数从高到低顺序,统计各种技术平均成绩和不及格率的百分数: (就是分析哪种技术难)
 --技术ID,技术名称,平均成绩,不及格百分比
 select course_id,course_name,AVG(result) as av,
 cast(cast (sum(case when result<60 then 1 else 0 end)*100/(count(1)*1.0) as float) as nvarchar)+'%'as 不及格率 
 from TEST_TABLE
 group by course_id,course_name
 order by 不及格率 desc,av asc
 
 --5.排列涂镀成绩第3名到第5名的徒弟成绩单(要求用外连接做，左右不限):
 --徒弟ID,徒弟姓名,涂镀,成绩
 /*select top 2 * from(
 select top 2 *
 from TEST_TABLE X LEFT JOIN TEST_TABLE Y
 ON X.course_name=Y.course_name and X.apprentice_id=Y.apprentice_id
 where X.course_name='涂镀'
 order by X.result desc)*/
 
-----------------------------------------------------------------------
 select apprentice_id,apprentice_name,course_name,result
 from 
 (
 select *,ROW_NUMBER() over(order by result desc)as rid from TEST_TABLE
 where course_name='涂镀'
 )temp
 where rid between 3 and 5
 order by rid
 -------------------------------------------------------------------------
/*select ROW_NUMBER() over(order by result desc)as rid from TEST_TABLE

 select top 5* from TEST_TABLE
 where apprentice_id not in
 (
 select top 2* from TEST_TABLE
 order by result desc
 )
 order by result desc*/
------------------------------------------------------

--7.排列徒弟平均成绩及其名次(选作)

select ROW_NUMBER() over(order by avg(result) desc)as place,apprentice_name,AVG(result)as avg_result
from TEST_TABLE
group by apprentice_name
order by place

--6.任选两个实现，要求格式为yyyy/mm/dd

--一个月第一天
select   dateadd(dd,-day(getdate())+1,getdate()) 
--先获取当前日期的天数减1得到一个数,再由当前日期的天数减去它得到当月第一天
Select CONVERT(varchar(100), dateadd(dd,-day(getdate())+1,getdate()), 111)
------------------------------------------------------------------------------
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) 
--getdate()，返回当前的日期和时间的函数
--DATEDIFF(mm,0,getdate())是计算当前日期和“1900-01-01 00:00:00.000”这个日期之间的月数
--DATEADD，增加当前日期到“1900-01-01”的月数
Select CONVERT(varchar(100), DATEADD(mm, DATEDIFF(mm,0,getdate()), 0), 111)

--本周的星期一
select CONVERT(varchar(100),DATEADD(ww,DATEDIFF(ww,0,getdate()),0),111)
--一年的第一天
select CONVERT(varchar(100),DATEADD(yy,DATEDIFF(yy,0,getdate()),0),111)
--季度的第一天
select CONVERT(varchar(100),DATEADD(qq,DATEDIFF(qq,0,getdate()),0),111)
--上个月的最后一天
select CONVERT(varchar(100),dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0)),0),111)
--去年的最后一天
select CONVERT(varchar(100),DATEADD(yy,DATEDIFF(yy,0,getdate())-1,0),111)
select CONVERT(varchar(100),dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0)),111)
--本月的最后一天
select CONVERT(varchar(100),dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate())+1,0)),111)
--本年的最后一天
select CONVERT(varchar(100),dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0)),111)
--本月的第一个星期一
select CONVERT(varchar(100),dateadd(WK,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0),111)

--7.由于业务逻辑需要，要同时更新(update)两个table，如果第一个table更新成功，
--第二个更新失败，那么现在的做法是把第一个table再改回去。请重新设计，使之更合理。
DECLARE @sumerror INT
set @sumerror=0
BEGIN TRANSACTION
SET @sumerror=@sumerror+@@ERROR;

--修改table1的语句
update TEST_TABLE set result=1 where master_name='张大师'

SET @sumerror=@sumerror+@@ERROR;
IF(@sumerror<>0)--修改成功
BEGIN
set @sumerror=0
--修改table2的语句
update Result set result=0;

SET @sumerror=@sumerror+@@ERROR;
IF(@sumerror<>0)
BEGIN--修改成功就提交事务
ROLLBACK TRANSACTION;
END
ELSE
BEGIN--否则事务回滚
COMMIT TRANSACTION;
END
END
ELSE
BEGIN--否则事务回滚
COMMIT TRANSACTION;
END