Case ..when..End
首先讓我們看一下?CASE?的語法。在一般的?SELECT?中，其語法如下：?
?
SELECT?<myColumnSpec>?=?
CASE?
WHEN?<A>?THEN?<somethingA>?
WHEN?<B>?THEN?<somethingB>?
ELSE?<somethingE>?
END??
?
在上面的代碼中需要用具體的參數(shù)代替尖括號中的內容。下面是一個簡單的例子：?
?
USE?pubs?
GO?
SELECT?
????Title,?
????'Price?Range'?=?
????CASE?
????????WHEN?price?IS?NULL?THEN?'Unpriced'?
????????WHEN?price?<?10?THEN?'Bargain'?
????????WHEN?price?BETWEEN?10?and?20?THEN?'Average'?
????????ELSE?'Gift?to?impress?relatives'?
????END?
FROM?titles?
ORDER?BY?price?
GO??
?
這是?CASE?的典型用法，但是使用?CASE?其實可以做更多的事情。比方說下面的?GROUP?BY?子句中的?CASE：?
?
SELECT?'Number?of?Titles',?Count(*)?
FROM?titles?
GROUP?BY?
????CASE?
????????WHEN?price?IS?NULL?THEN?'Unpriced'?
????????WHEN?price?<?10?THEN?'Bargain'?
????????WHEN?price?BETWEEN?10?and?20?THEN?'Average'?
????????ELSE?'Gift?to?impress?relatives'?
????END?
GO??
?
你甚至還可以組合這些選項，添加一個?ORDER?BY?子句，如下所示：?
?
USE?pubs?
GO?
SELECT?
????CASE?
????????WHEN?price?IS?NULL?THEN?'Unpriced'?
????????WHEN?price?<?10?THEN?'Bargain'?
????????WHEN?price?BETWEEN?10?and?20?THEN?'Average'?
????????ELSE?'Gift?to?impress?relatives'?
????END?AS?Range,?
????Title?
FROM?titles?
GROUP?BY?
????CASE?
????????WHEN?price?IS?NULL?THEN?'Unpriced'?
????????WHEN?price?<?10?THEN?'Bargain'?
????????WHEN?price?BETWEEN?10?and?20?THEN?'Average'?
????????ELSE?'Gift?to?impress?relatives'?
????END,?
????Title?
ORDER?BY?
????CASE?
????????WHEN?price?IS?NULL?THEN?'Unpriced'?
????????WHEN?price?<?10?THEN?'Bargain'?
????????WHEN?price?BETWEEN?10?and?20?THEN?'Average'?
????????ELSE?'Gift?to?impress?relatives'?
????END,?
????Title?
GO??
?
注意，為了在?GROUP?BY?塊中使用?CASE，查詢語句需要在?GROUP?BY?塊中重復?SELECT?塊中的?CASE?塊。?

把某一字段相同的數(shù)據(jù)去掉
select???id=identity(int,1,1)?,attid=cast(attachmentid?as?int),contentid??into???#t3???from???attachment???
??where???contentid???=???402??????
???
??select???contentid,attid
??from???#t3???
??where???id???in???(select???min(id)???from???#t3???group???by???contentid)?
其中，attachmentid為自增字段,把contentid字段相同的數(shù)據(jù)，只取出一條?
寫SQL字符串
通過寫SQL語句來執(zhí)行存儲過程
declare?@Sql????nvarchar(1000)
set?@sql=@sql+'?and?C.Author?like?''%'+cast(@Author?as?varchar)+'%''?'
set?@sql=@sql+'?and?(datediff(s,cast('''+cast(@startTime?as?varchar)+'''?as?datetime)?,CommitDate)>0)?'
exec(@sql)
like?匹配查詢時，第一個%前面要加倆個''，第二個%后面要加倆個''
字符串連接時間類型時，首先把datetime類型參數(shù)轉換成字符串類型，注意，也要加倆個''.

轉載于:https://www.cnblogs.com/nonsuch/archive/2007/10/09/918123.html

《新程序員》：云原生和全面數(shù)字化實踐50位技術專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的SqlServer一些用法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。