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. 刪除重複值