-- SQL?Server：
Select ? TOP ?N? * ? From ? TABLE ? Order ? By ? NewID ()?

-- 開頭到N條記錄
Select ? Top ?N? * ? From ?表

-- N到M條記錄(要有主索引ID)
Select ? Top ?M - N? * ? From ?表Where?ID? in ?( Select ? Top ?M?ID? From ?表)? Order ? by ?ID?? Desc

-- 選擇10從到15的記錄
select ? top ? 5 ? * ? from ?( select ? top ? 15 ? * ? from ? table ? order ? by ?id? asc ) A? order? by ?id? desc ?

-- N到結(jié)尾記錄
Select ? Top ?N? * ? From ?表Order? by ?ID? Desc
?

? ?

-- 顯示最后5條記錄,但是顯示的順序必須為5,6,7,8,9,10,而不是10,9,8,7,6,5 如下解決方法:

select top 5 from test where id in ( select top 5 from test order by id desc ) order by id asc


-- 通過這個問題也能總結(jié)出4-10條,5-100條這種限定一定范圍內(nèi)的sql語句的寫法:

select top < 末端ID - 頂端ID + 1 > * from < 表名 > where ID not in ( select top < 頂端ID - 1 > ) ID from < 表名 > )

-- 例如:4-10條就應(yīng)該寫成
select top 10 - 4 + 1 * from test where id not in ( select top 4 - 1 id from test)

? ?

?上一篇: select top 1 * from [news_table] where [新聞標識列]<當(dāng)前id號 where ......
?下一篇: select top 1 * from [news_table] where [新聞標識列]>當(dāng)前id號 where ...... order by [新聞標識列] desc

?

?

?

一個表中的 Id 有多個記錄，把所有這個 id 的記錄查出來，并顯示共有多少條記錄數(shù)。
------------------------------------------
select id, Count （ *) from tb group by id having count(*)>1


-- 兩條記錄完全相同，如何刪除其中一條
set ? rowcount = 1 ?
delete ? from ?thetablename? where ?id = @duplicate_id -- @duplicate_id為重復(fù)值的id?


-- 模糊查詢
select ? * ?? from ?product? where ?detail? like ? ' %123.jpg% '
-- 替換字段里面部分內(nèi)容
update ?product? set ?detail = replace ( cast (detail? as ? varchar ( 8000 )), ' abc.jpg ' , ' efg.jpg ' )?

?

-- 日期轉(zhuǎn)換參數(shù),值得收藏
select ? CONVERT ( varchar ,? getdate (),? 120 ?)
2004 - 09 - 12 ? 11 : 06 : 08 ?

select ? replace ( replace ( replace ( CONVERT ( varchar ,? getdate (),? 120 ?), ' - ' , '' ), ' ? ' , '' ), ' : ' , '' )
20040912110608

select ? CONVERT ( varchar ( 12 )?,? getdate (),? 111 ?)
2004 / 09 / 12

select ? CONVERT ( varchar ( 12 )?,? getdate (),? 112 ?)
20040912

select ? CONVERT ( varchar ( 12 )?,? getdate (),? 102 ?)
2004.09 . 12

?

-- 一個月第一天
SELECT ??? DATEADD (mm,??? DATEDIFF (mm, 0 , getdate ()),??? 0 )?? -- ??2009-06-01?00:00:00.000

-- 當(dāng)天
select ?? * ? from ?product?? where ? DateDiff ( day ,modiDate, GetDate ()) > 1 ?

-- 如何查詢本日、本月、本年的記錄SQL
本年：
select ? * ? from ?loanInfo? where ? year (date) = year ( getdate ())?
本月：
select ? * ? from ?loanInfo? where ? year (date) = year ( getDate ())? And ? month (date) = month ( getdate ())?
本日：
select ? * ? from ?loanInfo? where ? year (date) = year ( getDate ())? And ? month (date) = month ( getdate ())? and ? Day (date) = Day ( getDate ())???


本周的星期一??? SELECT ?? DATEADD (wk,?? DATEDIFF (wk, 0 , getdate ()),?? 0 )??
?
一年的第一天?? SELECT ?? DATEADD (yy,?? DATEDIFF (yy, 0 , getdate ()),?? 0 )??
?
季度的第一天??? SELECT ?? DATEADD (qq,?? DATEDIFF (qq, 0 , getdate ()),?? 0 )??
?
當(dāng)天的半夜??? SELECT ?? DATEADD (dd,?? DATEDIFF (dd, 0 , getdate ()),?? 0 )??
?
上個月的最后一天?
?
???????這是一個計算上個月最后一天的例子。它通過從一個月的最后一天這個例子上減去毫秒來獲得。有一點要記住，在Sql??Server中時間是精確到毫秒。這就是為什么我需要減去毫秒來獲得我要的日期和時間。?
?
??????? SELECT ?? dateadd (ms, - 3 , DATEADD (mm,?? DATEDIFF (mm, 0 , getdate ()),?? 0 ))??
?
???????計算出來的日期的時間部分包含了一個Sql??Server可以記錄的一天的最后時刻(“: 59 : 59 : 997 ”)的時間。?
?
去年的最后一天?
?
???????連接上面的例子，為了要得到去年的最后一天，你需要在今年的第一天上減去毫秒。?
?
??????? SELECT ?? dateadd (ms, - 3 , DATEADD (yy,?? DATEDIFF (yy, 0 , getdate ()),?? 0 ))??
?
本月的最后一天?
?
???????現(xiàn)在，為了獲得本月的最后一天，我需要稍微修改一下獲得上個月的最后一天的語句。修改需要給用DATEDIFF比較當(dāng)前日期和“ - 01 - 01 ”返回的時間間隔上加。通過加個月，我計算出下個月的第一天，然后減去毫秒，這樣就計算出了這個月的最后一天。這是計算本月最后一天的SQL腳本。?
?
??????? SELECT ?? dateadd (ms, - 3 , DATEADD (mm,?? DATEDIFF (m, 0 , getdate ()) + 1 ,?? 0 ))??
?
本年的最后一天?
?
???????你現(xiàn)在應(yīng)該掌握這個的做法，這是計算本年最后一天腳本?
?
??????? SELECT ?? dateadd (ms, - 3 , DATEADD (yy,?? DATEDIFF (yy, 0 , getdate ()) + 1 ,?? 0 ))。?
?
本月的第一個星期一?
?
???????好了，現(xiàn)在是最后一個例子。這里我要計算這個月的第一個星期一。這是計算的腳本。?
?
????????? select ?? DATEADD (wk,?? DATEDIFF (wk, 0 ,??????????????????????????????????????????????????????????
??????????????????????????????? dateadd (dd, 6 - datepart ( day , getdate ()), getdate ())????????
?????????????????????????????????????????????????????????????????????????????????????????????????),?? 0 )??????????????????????????
?
???????在這個例子里，我使用了“本周的星期一”的腳本，并作了一點點修改。修改的部分是把原來腳本中“ getdate ()”部分替換成計算本月的第天，在計算中用本月的第天來替換當(dāng)前日期使得計算可以獲得這個月的第一個星期一。?


-- 刪除一個月前,三個月前,?6個月前,一年前的數(shù)據(jù)?

DELETE ? FROM ?表名WHERE? datediff (MM,?AddTime, GETDATE ())? > ? 1
DELETE ? FROM ?表名WHERE? datediff (MM,?AddTime, GETDATE ())? > ? 3
DELETE ? FROM ?表名WHERE? datediff (MM,?AddTime, GETDATE ())? > ? 6
DELETE ? FROM ?表名WHERE? datediff (YY,?AddTime, GETDATE ())? > ? 1

-- -------------------------------------------------------------??
附錄，其他日期處理方法?
?
1 )去掉時分秒?
declare ??@?? datetime ??
set ??@?? = ?? getdate ()?? -- '2003-7-1??10:00:00'??
SELECT ??@, DATEADD ( day ,?? DATEDIFF ( day , 0 ,@),?? 0 )??
?
2 ）顯示星期幾?
select ?? datename (weekday, getdate ())????
?
3 ）如何取得某個月的天數(shù)?
declare ?? @m ?? int ??
set ?? @m = 2 ?? -- 月份?
select ???? datediff ( day , ' 2003- ' + cast ( @m ?? as ?? varchar ) + ' -15 ' ??, ' 2003- ' + cast ( @m + 1 ???? as ?? varchar ) + ' -15 ' )??

另外，取得本月天數(shù)?
select ???? datediff ( day , cast ( month ( GetDate ())?? as ?? varchar ) + ' - ' + cast ( month ( GetDate ())?? as ?? varchar ) + ' -15 ' ??, cast ( month ( GetDate ())?? as ?? varchar ) + ' - ' + cast ( month ( GetDate ()) + 1 ???? as ?? varchar ) + ' -15 ' )??

任意月份的最大天數(shù)
select ?? day ( dateadd (dd, - 1 , dateadd (mm, 1 , Dateadd (mm, datediff (mm, 0 , getdate ()), 0 ))))?


或者使用計算本月的最后一天的腳本，然后用DAY函數(shù)區(qū)最后一天?
SELECT ?? Day ( dateadd (ms, - 3 , DATEADD (mm,?? DATEDIFF (m, 0 , getdate ()) + 1 ,?? 0 )))??
?
4 ）判斷是否閏年：?

SELECT ?? case ?? day ( dateadd (mm,?? 2 ,?? dateadd (ms, - 3 , DATEADD (yy,?? DATEDIFF (yy, 0 , getdate ()),?? 0 ))))?? when ?? 28 ?? then ?? ' 平年 ' ?? else ?? ' 閏年 ' ?? end ??

或者?
select ?? case ?? datediff ( day , datename ( year , getdate ()) + ' -02-01 ' , dateadd (mm, 1 , datename ( year , getdate ()) + ' -02-01 ' ))??
when ?? 28 ?? then ?? ' 平年 ' ?? else ?? ' 閏年 ' ?? end ??
?
5 ）一個季度多少天?
declare ?? @m ?? tinyint , @time ?? smalldatetime ??
select ?? @m = month ( getdate ())??
select ?? @m = case ?? when ?? @m ?? between ?? 1 ?? and ?? 3 ?? then ?? 1 ??
??????????????????????? when ?? @m ?? between ?? 4 ?? and ?? 6 ?? then ?? 4 ??
??????????????????????? when ?? @m ?? between ?? 7 ?? and ?? 9 ?? then ?? 7 ??
??????????????????????? else ?? 10 ?? end ??
select ?? @time = datename ( year , getdate ()) + ' - ' + convert ( varchar ( 10 ), @m ) + ' -01 ' ??
select ?? datediff ( day , @time , dateadd (mm, 3 , @time ))???


?

? 1 、確定某年某月有多少天

實現(xiàn)原理：先利用DATEDIFF取得當(dāng)前月的第一天，再將月份加一取得下月第一天，然后減去分鐘，再取日期的天數(shù)部分，即為當(dāng)月最大日期，也即當(dāng)月天數(shù)

CREATE ? FUNCTION ?DaysInMonth?(? @date ? datetime ?)? Returns ? int
AS
BEGIN
? RETURN ? Day ( dateadd (mi, - 3 , DATEADD (m,? DATEDIFF (m, 0 , @date ) + 1 , 0 )))
END

調(diào)用示例：

select ?dbo.DaysInMonth?( ' 2006-02-03 ' )?

( 2 )計算哪一天是本周的星期一

SELECT ? DATEADD (week,? DATEDIFF (week, ' 1900-01-01 ' , getdate ()),? ' 1900-01-01 ' )?? -- 返回-11-06?00:00:00.000
或
SELECT ? DATEADD (week,? DATEDIFF (week, 0 , getdate ()), 0 )??????

( 3 )當(dāng)前季度的第一天

SELECT ? DATEADD (quarter,? DATEDIFF (quarter, 0 , getdate ()),? 0 )—返回 - 10 - 01 ? 00 : 00 : 00.000 ?

( 4 )一個季度多少天

declare ? @m ? tinyint , @time ? smalldatetime ?

select ? @m = month ( getdate ())?
select ? @m = case ? when ? @m ? between ? 1 ? and ? 3 ? then ? 1 ?
??????????????????????? when ? @m ? between ? 4 ? and ? 6 ? then ? 4 ?
??????????????????????? when ? @m ? between ? 7 ?? and ? 9 ? then ? 7 ?
??????????????????????? else ? 10 ? end ?

select ? @time = datename ( year , getdate ()) + ' - ' + convert ( varchar ( 10 ), @m ) + ' -01 ' ?
select ? datediff ( day , @time , dateadd (mm, 3 , @time ))?—返回



?

1 .按姓氏筆畫排序:?
Select ? * ? From ?TableName? Order ? By ?CustomerName?Collate?Chinese_PRC_Stroke_ci_as?

2 .分頁SQL語句
select ? * ? from ( select ?(row_number()? OVER ?( ORDER ? BY ?tab.ID? Desc ))? as ?rownum,tab. * ? from ?表名As?tab)? As ?t? where ?rownum? between ?起始位置And?結(jié)束位置

8 .如何修改數(shù)據(jù)庫的名稱:

sp_renamedb? ' old_name ' ,? ' new_name ' ?


3 .獲取當(dāng)前數(shù)據(jù)庫中的所有用戶表
select ? * ? from ?sysobjects? where ?xtype = ' U ' ? and ?category = 0 ?

4 .獲取某一個表的所有字段
select ?name? from ?syscolumns? where ?id = object_id ( ' 表名 ' )?

5 .查看與某一個表相關(guān)的視圖、存儲過程、函數(shù)
select ?a. * ? from ?sysobjects?a,?syscomments?b? where ?a.id? = ?b.id? and ?b. text ? like ? ' %表名% ' ?

6 .查看當(dāng)前數(shù)據(jù)庫中所有存儲過程
select ?name? as ?存儲過程名稱from?sysobjects? where ?xtype = ' P ' ?

7 .查詢用戶創(chuàng)建的所有數(shù)據(jù)庫
select ? * ? from ?master..sysdatabases?D? where ?sid? not ? in ( select ?sid? from ?master..syslogins? where ?name = ' sa ' )?
或者
select ?dbid,?name? AS ? DB_NAME ? from ?master..sysdatabases? where ?sid? <> ? 0x01 ?

8 .查詢某一個表的字段和數(shù)據(jù)類型
select ?column_name,data_type? from ?information_schema.columns? where ?table_name? = ? ' 表名 ' ?

?

?

9 .使用事務(wù)
在使用一些對數(shù)據(jù)庫表的臨時的SQL語句操作時，可以采用SQL?SERVER事務(wù)處理，防止對數(shù)據(jù)操作后發(fā)現(xiàn)誤操作問題

開始事務(wù)
Begin ? tran ?
Insert ? Into ?TableName? Values (…)?
SQL語句操作不正常，則回滾事務(wù)。

回滾事務(wù)
Rollback ? tran ?
SQL語句操作正常，則提交事務(wù)，數(shù)據(jù)提交至數(shù)據(jù)庫。

提交事務(wù)
Commit ? tran ?


計算執(zhí)行SQL語句查詢時間

declare ? @d ? datetime ?
set ? @d = getdate ()?
select ? * ? from ?SYS_ColumnProperties? select ? [ 語句執(zhí)行花費時間(毫秒) ] = datediff (ms, @d , getdate ())?



【關(guān)閉SQL?Server?數(shù)據(jù)庫所有使用連接】

use ??master?
go ?
create ?? proc ??KillSpByDbName( @dbname ?? varchar ( 20 ))??
as ??
begin ??
declare ?? @sql ?? nvarchar ( 500 ), @temp ? varchar ( 1000 )?
declare ?? @spid ?? int ??
set ?? @sql = ' declare??getspid??cursor??for????
select??spid??from??sysprocesses??where??dbid=db_id( ''' + @dbname + ''' ) ' ??
exec ??( @sql )??
open ??getspid??
fetch ?? next ?? from ??getspid?? into ?? @spid ??
while ?? @@fetch_status ? <>- 1 ??
begin ??
?? set ? @temp = ' kill?? ' + rtrim ( @spid )?
?? exec ( @temp )?
fetch ?? next ?? from ??getspid?? into ?? @spid ??
end ??
close ??getspid??
deallocate ??getspid??
end ??

-- 舉例使用，關(guān)閉數(shù)據(jù)庫下的所有連接操作
Use ??master??
Exec ??KillSpByDbName?? ' 數(shù)據(jù)庫名稱 ' ?


（一）掛起操作
在安裝Sql或sp補丁的時候系統(tǒng)提示之前有掛起的安裝操作，要求重啟，這里往往重啟無用，解決辦法：
到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession?Manager
刪除PendingFileRenameOperations

（二）收縮數(shù)據(jù)庫
-- 重建索引
DBCC ?REINDEX
DBCC ?INDEXDEFRAG
-- 收縮數(shù)據(jù)和日志
DBCC ?SHRINKDB
DBCC ?SHRINKFILE

（三）壓縮數(shù)據(jù)庫
dbcc ?shrinkdatabase(dbname)

（四）轉(zhuǎn)移數(shù)據(jù)庫給新用戶以已存在用戶權(quán)限
exec ?sp_change_users_login?update_one,newname,oldname
go

（五）檢查備份集
RESTORE ?VERIFYONLY? from ? disk = Evbbs.bak

（六）修復(fù)數(shù)據(jù)庫
ALTER ? DATABASE ? [ dvbbs ] ? SET ?SINGLE_USER
GO
DBCC ?CHECKDB(dvbbs,repair_allow_data_loss)? WITH ?TABLOCK
GO
ALTER ? DATABASE ? [ dvbbs ] ? SET ?MULTI_USER
GO

?

?


select ? top ?m? * ? from ?tablename? where ?id? not ? in ?( select ? top ?n?id? from ?tablename)?


select ? top ?m? * ? into ?臨時表(或表變量)? from ?tablename? order ? by ?columnname? -- ?將top?m筆插入
set ? rowcount ?n?
select ? * ? from ?表變量order? by ?columnname? desc ?


select ? top ?n? * ? from ?
( select ? top ?m? * ? from ?tablename? order ? by ?columnname)?a?
order ? by ?columnname? desc ?





復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a?新表名：b)?(Access可用)?
法一： select ? * ? into ?b? from ?a? where ? 1 ? <> 1 ?
法二： select ? top ? 0 ? * ? into ?b? from ?a?

拷貝表(拷貝數(shù)據(jù),源表名：a?目標表名：b)?(Access可用)?
insert ? into ?b(a,?b,?c)? select ?d,e,f? from ?b;?

跨數(shù)據(jù)庫之間表的拷貝(具體數(shù)據(jù)使用絕對路徑)?(Access可用)?
insert ? into ?b(a,?b,?c)? select ?d,e,f? from ?b? in ?‘具體數(shù)據(jù)庫’ where ?條件
例子：.. from ?b? in ? ' "&Server.MapPath(".")&"/data.mdb"?&" ' ? where ..?

子查詢(表名：a?表名：b)?
select ?a,b,c? from ?a? where ?a? IN ?( select ?d? from ?b?)?或者:? select ?a,b,c? from ?a? where ?a? IN ?( 1 , 2 , 3 )?

顯示文章、提交人和最后回復(fù)時間
select ?a.title,a.username,b.adddate? from ? table ?a,( select ? max (adddate)?adddate? from ? table ? where ? table .title = a.title)?b?

外連接查詢(表名：a?表名：b)?
select ?a.a,?a.b,?a.c,?b.c,?b.d,?b.f? from ?a? LEFT ?OUT? JOIN ?b? ON ?a.a? = ?b.c?

在線視圖查詢(表名：a?)?
select ? * ? from ?( SELECT ?a,b,c? FROM ?a)?T? where ?t.a? > ? 1 ;?

between的用法,between限制查詢數(shù)據(jù)范圍時包括了邊界值, not ?between不包括
select ? * ? from ?table1? where ?time? between ?time1? and ?time2?
select ?a,b,c,? from ?table1? where ?a? not ? between ?數(shù)值and?數(shù)值

in ?的使用方法
select ? * ? from ?table1? where ?a? [ not ] ? in ?(‘值’,’值’,’值’,’值’)?

兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息
delete ? from ?table1? where ? not ? exists ?(? select ? * ? from ?table2? where ?table1.field1 = table2.field1?)?

四表聯(lián)查問題：
select ? * ? from ?a? left ? inner ? join ?b? on ?a.a = b.b? right ? inner ? join ?c? on ?a.a = c.c? inner ? join ?d? on ?a.a = d.d? where ?..?

日程安排提前五分鐘提醒
SQL:? select ? * ? from ?日程安排where? datediff ( ' minute ' ,f開始時間, getdate ()) > 5 ?

一條sql?語句搞定數(shù)據(jù)庫分頁
select ? top ? 10 ?b. * ? from ?( select ? top ? 20 ?主鍵字段,排序字段from?表名order? by ?排序字段desc)?a,表名b? where ?b.主鍵字段 = ?a.主鍵字段order? by ?a.排序字段


選擇在每一組b值相同的數(shù)據(jù)中對應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績排名,等等.)?
select ?a,b,c? from ?tablename?ta? where ?a = ( select ? max (a)? from ?tablename?tb? where ?tb.b = ta.b)?

包括所有在TableA?中但不在TableB和TableC?中的行并消除所有重復(fù)行而派生出一個結(jié)果表
( select ?a? from ?tableA?)? except ?( select ?a? from ?tableB)? except ?( select ?a? from ?tableC)?

隨機取出條數(shù)據(jù)
select ? top ? 10 ? * ? from ?tablename? order ? by ? newid ()?

隨機選擇記錄
select ? newid ()?

?

? ?

刪除重復(fù)記錄
Delete ? from ?tablename? where ?id? not ? in ?( select ? max (id)? from ?tablename? group ? by ?col1,col2,)?

select ? distinct ? * ? into ?#Tmp? from ?TB
drop ? table ?TB
select ? * ? into ?TB? from ?#Tmp
drop ? table ?#Tmp

?

在幾千條記錄里,存在著些相同的記錄,如何能用SQL語句,刪除掉重復(fù)的呢?
1 、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（peopleId）來判斷???
select ??? * ??? from ???people???
where ???peopleId??? in ???( select ?????peopleId????? from ?????people????? group ????? by ?????peopleId????? having ????? count (peopleId)??? > ??? 1 )???
????
2 、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（peopleId）來判斷，只留有rowid最小的記錄???
delete ??? from ???people?????
where ???peopleId????? in ???( select ?????peopleId????? from ???people????? group ????? by ?????peopleId??????? having ????? count (peopleId)??? > ??? 1 )???
and ???rowid??? not ??? in ???( select ??? min (rowid)??? from ?????people????? group ??? by ???peopleId????? having ??? count (peopleId???) > 1 )???
????
3 、查找表中多余的重復(fù)記錄（多個字段）?????
select ??? * ??? from ???vitae???a???
where ???(a.peopleId,a.seq)??? in ?????( select ???peopleId,seq??? from ???vitae??? group ??? by ???peopleId,seq????? having ??? count ( * )??? > ??? 1 )???
????
4 、刪除表中多余的重復(fù)記錄（多個字段），只留有rowid最小的記錄???
delete ??? from ???vitae???a???
where ???(a.peopleId,a.seq)??? in ?????( select ???peopleId,seq??? from ???vitae??? group ??? by ???peopleId,seq??? having ??? count ( * )??? > ??? 1 )???
and ???rowid??? not ??? in ???( select ??? min (rowid)??? from ???vitae??? group ??? by ???peopleId,seq??? having ??? count ( * ) > 1 )???
????
5 、查找表中多余的重復(fù)記錄（多個字段），不包含rowid最小的記錄???
select ??? * ??? from ???vitae???a???
where ???(a.peopleId,a.seq)??? in ?????( select ???peopleId,seq??? from ???vitae??? group ??? by ???peopleId,seq??? having ??? count ( * )??? > ??? 1 )???
and ???rowid??? not ??? in ???( select ??? min (rowid)??? from ???vitae??? group ??? by ???peopleId,seq??? having ??? count ( * ) > 1 )?




-- float字段保留一位小數(shù)，四舍五入

SELECT ? CONVERT ( DECIMAL ( 18 , 1 ), 1024.791454 )? -- -----?1024.8?（所影響的行數(shù)為1?行）
? <% #?Eval("字段") == null ?"":Eval("字段").toString(" 0.0 ")? %>