1。行列转换问题:假设有学生成绩单(tb)如下:我想成为(得到如下结果):代码:WITHtb(name,course,score)AS(SELECTN'张三',N'国语',74UNIONALLSELECTN'张三',N'数学',83UNIONALLSELECTN'张三',N'物理',93UNIONALLSELECTN'李四',N'国语',79UNIONALLSELECTN'李Si',N'数学',86UNIONALLSELECTN'李思',N'物理',88)SELECTname,MAX(CASEcourseWHEN'Chinese'THENscoreELSE0END)Chinese,MAX(CASEcourseWHEN'数学'THENscoreELSE0END)Mathematics,MAX(CASEcourseWHEN'Physics'THENscoreELSE0END)PhysicsFROMtbGROUPBYname2,paginationscheme1:使用NOTIN和SELECTTOP分页语句形式SELECTTOP10*FROMTestTableWHEREIDNOTIN(SELECTTOP20IDFROMTestTableORDERBYID)ORDERBYID方案二:使用大于多少的ID和SELECTTOP分页语句形式SELECTTOP10*FROMTestTableWHEREID>(SELECTMAX(id)FROM(SELECTTOP20id来自TestTableORDERBYid)AST)ORDERBYID方案三:利用SQLServer中的ROW_NUMBER特性进行分页SELECT*FROM(SELECTROW_NUMBER()OVER(ORDERBYIDDESC)ASROWID,*FROMTestTable)ASmytablewhereROWIDbetween21和403.合并结果并合并重复行SELECT*FROMAUNIONSELECT*FROMB不合并重复行SELECT*FROMAUNIONALLSELECT*FROMB4、随机排序SELECT*FROMTestTableORDERBYNEWID()也可以结合TOP得到随机的前N条记录SELECTTOP100*FROMTestTableORDERBYNEWID()5、两边数据用任意符号隔开。比如我们用逗号(,)来拆分数据,将下面的数据拆分成下图:SELECTR,CASEWHENCHARINDEX(',',R)>1THENLEFT(R,CHARINDEX(',',R)-1)ELSENULLENDASR1,CASEWHENCHARINDEX(',',R)>1THENRIGHT(R,(LEN(R)-CHARINDEX(',',R)))ELSENULLENDASR2FROMt代码比较长,我们将代码拆解理解:SELECTCHARINDEX(',',',')--结果为1SELECTCHARINDEX(',','NULL')--结果为0SELECTCHARINDEX(',','')--结果为0SELECTCHARINDEX(',','A,B')--结果为2SELECTLEN('A,B')--结果为3SELECTLEN('A,B'')-CHARINDEX(',','A,B')--结果为3-2=1SELECTRIGHT('A,B',(LEN('A,B')-CHARINDEX(',','A,B')))--结果为B最后一步我们将'A,B'拆分出B,同样我们可以用类似的方法得到A6、WAITFOR延迟执行示例等待1小时2分钟以及执行SELECT语句前3秒WAITFORDELAY'01:02:03'SELECT*FROMEmployee其中DELAY是多长时间edelay将开始执行。示例等到晚上11:08之后执行SELECT语句WAITFORTIME'23:08:00'SELECT*FROMEmployee其中TIME是要等到特定时间才开始执行
