1. N到M条记录(要有主索引ID)

SelectTop M-N *From [Table] Where ID in (SelectTop M ID From [Table]) Orderby ID  Desc

2. 查询逻辑顺序

(8) SELECT (9) DISTINCT (11) <TOP_specification><select_list>

(1) FROM<left_table>

(3) <join_type>JOIN<right_table>

(2) ON<join_condition>

(4) WHERE<where_condition>

(5) GROUPBY<group_by_list>

(6) WITH {CUBE | ROLLUP}

(7) HAVING<having_condition>

(10) ORDERBY<order_by_list>

3. 日期转换参数,值得收藏

selectCONVERT(varchar, getdate(), 120 )

2004-09-1211:06:08

selectreplace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),'',''),':','')

20040912110608

selectCONVERT(varchar(12) , getdate(), 111 )

2004/09/12

selectCONVERT(varchar(12) , getdate(), 112 )

20040912

selectCONVERT(varchar(12) , getdate(), 102 )

2004.09.12

4. 一个月第一天

SELECT   DATEADD(mm,   DATEDIFF(mm,0,getdate()),   0) 

--  2009-06-01 00:00:00.000

5. 分页SQL语句

select*from(select (row_number() OVER (ORDERBY tab.ID Desc)) as rownum,tab.*from 表名As tab) As t where rownum between 起始位置And 结束位置

6. 获取当前数据库中的所有用户表

select*from sysobjects where xtype='U'and category=0

7. 获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

8. 查看与某一个表相关的视图、存储过程、函数

select a.*from sysobjects a, syscomments b where a.id = b.id and b.textlike'%表名%'

9. 查看当前数据库中所有存储过程

select name as 存储过程名称from sysobjects where xtype='P'

10. 查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns where table_name ='表名'

11. 删除重复值