sql字符串函数说明

死亡循环淡定如果有一天晚上，你从家里出去，你发现楼梯一直往下延伸，永远没有尽头，你永远找不到出口，你会怎么想？爱情没有永恒，只有彼此的慰藉和责任。

{0:P}百分号格式化字符串A brother is only a fame//legend哥只是个传说CREATE PROCEDURE proc_parameterNumber@SingleNumberId varchar(10),@SingleNumber varchar(10) outputASBEGINif not exists (select * from SingleNumber where SingleNumberId=@SingleNumberId) beginset @SingleNumber=''returnendelsebegindeclare @Prefixion varchar(10)declare @Number bigintdeclare @Width tinyintselect @Prefixion=Prefixion,@Number=Number,@Width=Width from SingleNumber where SingleNumberId=@SingleNumberIdupdate SingleNumber set Number=Number+1 where SingleNumberId=@SingleNumberId declare @currentNumber varchar(100)@currentNumber='00000000000000000000000'+@Numberset @SingleNumber=@currentNumberendENDGO/// <summary>/// 添加项目对应的课程/// </summary>/// <param name="courseIds">课程Id数组</param>/// <param name="projectId">项目id</param>public static void AddCourseSettings(int[] courseIds, string projectId){SqlParameterList splist = new SqlParameterList();splist.AddAParameter("@ProjectId", SqlDbType.Char, 8, projectId);string strSql = "";for (int i = 0; i < courseIds.Length; i++){splist.AddAParameter("@CourseId" + i.ToString(), SqlDbType.Int, courseIds[i]);strSql += string.Format(@"if exists(select * from CourseSetting where ProjectId=@ProjectId and CourseId=@CourseId{0})return;elseinsert into CourseSetting(ProjectId,CourseId) values (@ProjectId,@CourseId{0}); end", i);DataAccessor.ExecuteSqlOnly(strSql, false, splist);}}declare @PayAbleTuition int --应交费用select @PayAbleTuition=PayAbleTuition from PayCondition where PayConditionId=1if not exists (select TuitionId from Tuition where PayConditionId=1)--预交一次交清保留 @TuitionAmount：当前所交费用beginif(@TuitionAmount<@PayAbleTuition)select '预交'else if(@TuitionAmount=@PayAbleTuition)select '一次交清'elseselect '保留'endelse--补交补齐保留begindeclare @PaidTuition int --已交费用select @PaidTuition=sum(TuitionAmount) from Tuition where PayConditionId=1if(@PaidTuition+@TuitionAmount<@PayAbleTuition)select '补交'else if(@PaidTuition+@TuitionAmount=@PayAbleTuition)select '补齐'elseselect '保留'endwinForm窗体中，快捷键是(&字母) 如：显示(&D) 则表示"显示"的快捷键是字母Ddatediff(datepart,startTime,endTime)说明：(endtime-starttime的结果转化为datepart)该函数返回int类型如：select datediff(ss,'2010-4-14 10:30:20','2010-4-15 10:30:21')结果是86401(结果是一天零一秒，即86401秒)dateadd(datepart,number,date)说明：在datepart部分增加number该函数返回datetime类型如：select dateadd(mi,-20,'2010-3-4 12:45:25')返回2010-3-4 12:25:25update exam set ExamScore=null where id=5select top 2 TestLibraryId,SubjectName,exerciseTypeName,ExerciseCount,perscore,IsImpersonalEx ercise,es.ExerciseId as 'ExerciseIdNew',tl.subjectId as 'subjectIdNew',testtitle,subjectiveAnswer,ImpersonalAnswer from Subject as sinner join Testlibrary as tlon s.subjectId=tl.subjectIdinner join ExamSetting as eson tl.ExerciseId=es.ExerciseIdinner join ExerciseType as eton et.exerciseId=tl.exerciseIdwhere s.subjectId=4 and es.exerciseId=5 and tl.subjectId=es.subjectId and es.exerciseId=et.exerciseIdorder by newid()selectExamApplyId,ExamineeId,ExamineeName,SubjectName,StartTime,SubmitTime,sum(PerScore) as 'TotalScore',sum(Examscore) as 'FactScore' from(select ea.ExamApplyId as 'ExamApplyId',em.ExamineeId as 'ExamineeId',ExamineeName,SubjectName,StartTime,SubmitTime,PerScore,ExamScore from Exam as einner join ExamApply as eaon e.ExamApplyId=ea.ExamApplyId and e.ExamineeId=ea.ExamineeIdinner join Examinee as emon em.ExamineeId=e.ExamineeId and em.ExamineeId=ea.ExamineeIdinner join Subject as son s.SubjectId=ea.SubjectIdwhere e.ExerciseTypeName is not null) as tempTablegroup by ExamApplyId,ExamineeId,ExamineeName,SubjectName,StartTime,SubmitTime闷骚:敢想不敢做select ExerciseTypeName,min(examId) as 'aa' from Exam where examapplyId=26 and ExerciseTypeName is not null group by ExerciseTypeName order by aaselect em.ExamineeId as 'ExamineeIdTemp',ExamineeName,StartTime,SubmitTime,SubjectName,ea.ExamApplyId as 'ExamApplyIdTemp' from Exam as einner join ExamApply as eaon e.ExamApplyid=ea.ExamApplyIdinner join Examinee as emon em.ExamineeId=e.ExamineeIdinner join Subject as son s.SubjectId=ea.subjectIdwhere examid in(select ExamId from(select min(ExamId) as 'ExamId',examApplyid from exam group by examApplyid) as jj) and e.ExamineeId=ea.ExamineeId and ea.ExamineeId=em.ExamineeId找出最小的ExamId，通过examApplyid来分组select *from Examwhere examid in(select ExamId from(select min(ExamId) as 'ExamId',examApplyid from exam group by examApplyid) as jj)----select min(ExamId),examApplyid from exam group by examApplyidselectExamApplyIdTemp,ExamineeIdTemp,ExamineeName,SubjectName,StartTime,SubmitTime,Sum (perScore) as 'TotalScore',sum(ExamScore) as 'FactScore' from(select ea.ExamApplyId as 'ExamApplyIdTemp',e.ExamineeId as 'ExamineeIdTemp',ExamineeName,SubjectName,StartTime,SubmitTime,PerScore,Exercise Count,ExamScorefrom ExamApply as ea left join Examinee as eon ea.ExamineeId=e.ExamineeIdinner join Subject as son s.SubjectId=ea.SubjectIdinner join Exam as exon ea.ExamApplyId=ex.ExamApplyIdinner join ExamSetting as eson es.SubjectId=s.SubjectIdwhere ex.ExamineeId=e.ExamineeId and es.SubjectId=ea.SubjectId and es.ExerciseId=ex.ExerciseId) as newTablewhere ExamApplyIdTemp='17'group by ExamApplyIdTemp,ExamineeIdTemp,ExamineeName,SubjectName,StartTime,SubmitTime--变态sql语句select top 10 TestTitle,exerciseTypeName,IsImpersonalExercise,perscore,ExerciseCount,aa,subjec tiveAnswer,ImpersonalAnswer from (select exerciseTypeName,IsImpersonalExercise,es.ExerciseId,tl.subjectId,es.ExerciseId as 'aa',perscore,testtitle,subjectiveAnswer,ImpersonalAnswer,ExerciseCount from Subject as sinner join Testlibrary as tlon s.subjectId=tl.subjectIdinner join ExamSetting as eson tl.ExerciseId=es.ExerciseIdinner join ExerciseType as eton et.exerciseId=tl.exerciseIdwhere s.subjectId=4 and tl.subjectId=es.subjectId and es.exerciseId=et.exerciseId) as jjorder by newid()Response.Write(Server.HtmlEncode("<br />"));//可以强制输出<br />,而不是换行Response.Write("<script>alert(\"alert('你好')\")</script>");//可以强制输出alert('你好')Response.Write(Server.HtmlEncode("<script>alert(\"你好\")</script>"));//可以强制输出<script>alert('你好')</script><![CDATA['aa']]> 可以允许放入"<a>连接</a>"这样的内容<asp:listbox>控件相当于<select multiple="multiple"><option>(允许多行选中。