第三章 行转列 cast end while

--------------行转列---------------------------IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tbgoCREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)insert into tb VALUES ('张三','语文',74)insert into tb VALUES ('张三','数学',83)insert into tb VALUES ('张三','物理',93)insert into tb VALUES ('李四','语文',74)insert into tb VALUES ('李四','数学',84)insert into tb VALUES ('李四','物理',94)goSELECT * FROM tbgoSELECT  m.* ,        n.总分 ,        n.平均分FROM    ( SELECT    *          FROM      tb PIVOT( MAX(分数) FOR 课程 IN ( 语文, 数学, 物理 ) ) a        ) m ,        ( SELECT    姓名 ,                    SUM(分数) 总分 ,                    CAST(AVG(分数 * 1.0) AS DECIMAL(18, 2)) 平均分          FROM      tb          GROUP BY  姓名        ) nWHERE   m.姓名 = n.姓名--面试题作业  行转列 --创建 tmp 表  并添加数据 create table tmp(riqi varchar(32),shengfu nchar(1)) select *  from tmp insert into tmp values('2015-05-09','胜') insert into tmp values('2015-05-09','胜') insert into tmp values('2015-05-09','负') insert into tmp values('2015-05-09','负') insert into tmp values('2015-05-10','胜') insert into tmp values('2015-05-10','负') insert into tmp values('2015-05-10','负') --行转列 select * from tmpselect riqi as 日期,  SUM(   case     when shengfu='胜' then 1  else 0  end   ) as 胜,  SUM(   case     when shengfu='负' then 1  else 0  end   ) as 负   from  tmp   group by riqi--while循环 输出1-100之间偶数之和declare @sum intset @sum=0declare @num intset @num=1while(@num<=100)begin    if(@num%2=0)beginset @sum=@num+@sumend  set @num=@num+1endprint @sum--while 输出直角三角形----------------declare @i int----------------set @i=0----------------            while (@i <=5) begin           ----------------declare @j int----------------                      set @j=0----------------                while (@j < @i) begin----------------                   print '*'----------------            set @j=@j+1---------------- print '  '----------------                end----------------            set @i=@i+1----------------          end           --------1-------------    declare @a int set @a=1while @a<=5beginprint(replicate('*',@a))set @a=@a+1end---------------2------------------declare @star nvarchar(255)set @star='★'declare @i intset @i=1while(@i<6)beginprint @starset @star+='★'set @i+=1endgo-----------------3-------------------declare @star nvarchar(255)set @star='★'declare @i intset @i=1declare @j intset @j=0while(@i<6)beginwhile(@j<@i)beginprint @starset @star+='★'set @j+=1endset @i+=1endgo--cast end declare @date datetime select @date=max(examdate) from Result inner join subject on result.Subjectid=subject.Subjectid where subjectName='oop' select 学号=StudentNo,成绩= case    when StudentResult<60 then 'E'when StudentResult between 60 and 69 then 'D'when StudentResult between 70 and 79 then 'C'when StudentResult between 80 and 89 then 'B'when StudentResult between 90 and 99 then 'A'end from Resultinner join subject on Result.Subjectid=Subject.Subjectidwhere subjectName='oop' and ExamDate=@date