文章分类 -  SQL2005

摘要：if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pro_BBs_SELECT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Pro_BBs_SELECT] GO CREATE PROCEDURE Pro_BBs_SELECT ( @Flag int ) AS SELECT * FROM bbs where Flag=@Flag GO exec Pro_BBs_SELECT 3 阅读全文

摘要：前台的置顶SQL语句: string sql = "select top " + count + " * from NewInf where ID -1 and zdnum=(select max(zdnum) from NewInf where ID -1" + str + " ) order by ID desc " 底层的置顶SQL语句 if (num1 == "0") { sql.Add("update ProcessInf set hotinf = " + num1 + " where ID = '" + list[i].ToString() + "' "); } else { sql.Add("update ProcessInf set hot 阅读全文

摘要：select top "+pagesize+" *,case IsPass when 1 then '√' when 0 then '×' end as IsPass_2,"+ "case hotinf when 1 then '√' when 0 then '×' end as hotinf_2," + "case focusinf when 1 then '√' when 0 then '×' end as focusinf_2," + "case IsState when 1 then '√' when 0 then '×' end as IsState_2, " + "case when cast(zdnum as float)0 then '√' else '×' end as zdnum_2, " + "(select typename from BidInfType where ID = typ 阅读全文

摘要：前面一篇随笔,我提到了Sql Server 2000的自定义分页,但是在sql server 2000中,要实现显示某一页,就返回那一页数据的效果的方法实在不尽人意.网上很多通用的分页存储过程,但看着就头大.如果使用我前面提到的使用in,not in,top来进行返回特定页,特殊的限制又会比较多(比如ID要递增).现在Sql Server 2005中提供了一个函数ROW_NUMBER(),可以使自定义分页变得简单许多. 我们先来看看ROW_NUMBER()是干什么的.执行下面这段SQL语句: SELECT [ReportID],[UserName], [ReportID], [TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo FROM [ExecutionLog] 执行结果如下图所示: 很简单,ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OV 阅读全文