1.用一個(gè)表中的一個(gè)字段更新另一個(gè)表中的字段

update ?TableA? set ?name? = ?b.name? from ?TableA?a,TableB?b? where ?a.idA? = b.idB

-- 錯(cuò)誤語(yǔ)句(An?aggregate?may?not?appear?in?the?set?list?of?an?UPDATE?statement.)
update ?yaf_Topic? set ?LastPosted? = ? max (posted),NumPosts = count ( * )? from ?yaf_Message?a,yaf_Topic?b? where ?a.TopicID? = b.TopicID? and ?b.ForumID? = ? 10

-- 正確語(yǔ)句
update ?yaf_Topic?
???? set ??LastPosted? = ?maxLastPosted,NumPosts? = ?NumPostscount??
from ?( select ?maxLastPosted? = ? max (posted),NumPostscount = count ( * ),TopicID? from ?yaf_Message? group ? by ?topicID)a,yaf_Topic?b?
where ?a.TopicID? = b.TopicID? and ?b.ForumID? = ? 10

2.判斷符合某個(gè)條件的記錄是否存在,存在則不insert,不存在則Insert

insert ? into ?yaf_ProduceReviewPostHis(TopicID,Created,Flag,ReplyCount)? select ? 12345678 ,? ' 23 ' ,? 1 ,? 20 ? where ? not ? exists ( select ? 1 ? from ?yaf_ProduceReviewPostHis? where ?TopicID = 12345678 ? and ?Created = ' 23 ' ? and ?Flag = 1 )

3.判斷數(shù)據(jù)重復(fù)

select ? count ( * )? from ?
( select ? count ( * )? as ?user_count,userID,ForumID
from ?yaf_vaccess
group ? by ?userID,ForumID
having ? count ( * ) > 1 )?a

4.找重復(fù)列

select ?a. * ? from ?test?a,( select ? count = count ( * ),string = min (string),test_id = min (test_id)? from ?test? group ? by ?string)?b? where ?a.string = b.string? and ?a.test_id <> b.test_id

5.刪除重復(fù)數(shù)據(jù)

delete ?test? where ?test.test_id? in ?(
select ?a.test_id? from ?test?a,( select ? count = count ( * ),string = min (string),test_id = min (test_id)? from ?test? group ? by ?string)?b? where ?a.string = b.string? and ?a.test_id <> b.test_id
)

6.having
?HAVING 子句運(yùn)做起來(lái)非常象 WHERE 子句， 只用于對(duì)那些滿足 HAVING 子句里面給出的條件的組進(jìn)行計(jì)算。 其實(shí)，WHERE 在分組和聚集之前過(guò)濾掉我們不需要的輸入行， 而 HAVING 在 GROUP 之后那些不需要的組． 因此，WHERE 無(wú)法使用一個(gè)聚集函數(shù)的結(jié)果． 而另一方面，我們也沒(méi)有理由寫(xiě)一個(gè)不涉及聚集函數(shù)的 HAVING． 如果你的條件不包含聚集，那么你也可以把它寫(xiě)在 WHERE 里面， 這樣就可以避免對(duì)那些你準(zhǔn)備拋棄的行進(jìn)行的聚集運(yùn)算．

? *聚集函數(shù) 指的是象count,max,sum,AVG等函數(shù)

?如果我們想知道那些銷售超過(guò)2個(gè)部件的供應(yīng)商，使用下面查詢：

? SELECT ?S.SNO,?S.SNAME,? COUNT (SE.PNO)?
? FROM ?SUPPLIER?S,?SELLS?SE?
? WHERE ?S.SNO? = ?SE.SNO???
? GROUP ? BY ?S.SNO,?S.SNAME??
? HAVING ? COUNT (SE.PNO)? > ? 2 ;

5.帶有子查詢的insert
當(dāng)帶有子查詢是不能用values和括號(hào)。例如：

insert ?test2(id,string,string1, number )
select ?test_id,string,string1,test. number ? from ?test,test1? where ?test.test_id = test1.id

6.not exists

select ? * ? from ?test1? where ?? not ? exists ( select ? * ? from ?test? where ?test1.id? = ?test.test_id)

7.關(guān)于在SQL中插入數(shù)據(jù)并返回ID的方法??

INSERT ? INTO ?test? values ( ' sss ' )
SELECT ? SCOPE_IDENTITY ()??

8.多子查詢

SELECT ?A.NUM,?A.NAME,?B.UPD_DATE,?B.PREV_UPD_DATE

?? FROM ?TABLE1?A,?

????( SELECT ?X.NUM,?X.UPD_DATE,?Y.UPD_DATE?PREV_UPD_DATE

???????? FROM ?( SELECT ?NUM,?UPD_DATE,?INBOUND_QTY,?STOCK_ONHAND

???????????????? FROM ?TABLE2

?????????????? WHERE ?TO_CHAR(UPD_DATE, ' YYYY/MM ' )? = ?TO_CHAR(SYSDATE,? ' YYYY/MM ' ))?X,?

????????????( SELECT ?NUM,?UPD_DATE,?STOCK_ONHAND

???????????????? FROM ?TABLE2

?????????????? WHERE ?TO_CHAR(UPD_DATE, ' YYYY/MM ' )? = ?

????????????????????TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,? ' YYYY/MM ' )? || ? ' /01 ' , ' YYYY/MM/DD ' )? - ? 1 ,? ' YYYY/MM ' )?)?Y,?

???????? WHERE ?X.NUM? = ?Y.NUM?（ + ）

?????????? AND ?X.INBOUND_QTY? + ?NVL(Y.STOCK_ONHAND, 0 )? <> ?X.STOCK_ONHAND?)?B

WHERE ?A.NUM? = ?B.NUM

select??*?from?bbs.dbo.yaf_topic?a?
?full?join??bbs_temp_20050830.dbo.yaf_topic?b
on?a.topicid=b.topicid
where?a.topicid?is?null?
?
-----------------------------------
set??identity_insert?yaf_topic?on

INSERT?INTO?[bbs].[dbo].[yaf_Topic]([TopicID],?[ForumID],?[UserID],?[Posted],?[Topic],?[Views],?[IsLocked],?[Priority],?[PollID],?[TopicMovedID],?[LastPosted],?[LastMessageID],?[LastUserID],?[LastUserName],?[NumPosts],?[PhotoTypeID],?[PhotoFilmName],?[PhotoCamera],?[ActionDate],?[CheckFlag],?[NoReply],?[Hide])
select??b.*?from?bbs.dbo.yaf_topic?a?
?full?join??bbs_temp_20050830.dbo.yaf_topic?b
on?a.topicid=b.topicid
where?a.topicid?is?null?
?
set??identity_insert?yaf_topic?off

create?table?#data(TopicID?bigint,?MessageID?bigint?)??????????????????????????

insert?#data?exec?yaf_topic_save?@ForumID,@topic,@UserID,@Message,@Priority,@IP,@PollID,@ActionDate,@TopicMovedID,@Country,@Sheng,@Shi,@JinQu,@PhotoTypeID,@PhotoFilmName,@PhotoCamera,@Posted

??Create?Proc?[dbo].cs_GetAnonymousUserID??
(??
?@SettingsID?int,??
?@UserID?int?output??
)??
as??
SET?Transaction?Isolation?Level?Read?UNCOMMITTED??
Select?@UserID?=?cs_UserID?FROM?cs_vw_Users_FullUser?where?SettingsID?=?@SettingsID?and?IsAnonymous?=?1?