1.用一個表中的一個字段更新另一個表中的字段
update
?TableA?
set
?name?
=
?b.name?
from
?TableA?a,TableB?b?
where
?a.idA?
=
b.idB
--
錯誤語句(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
--
正確語句
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.判斷符合某個條件的記錄是否存在,存在則不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.判斷數據重復
select
?
count
(
*
)?
from
?
(
select
?
count
(
*
)?
as
?user_count,userID,ForumID
from
?yaf_vaccess
group
?
by
?userID,ForumID
having
?
count
(
*
)
>
1
)?a
4.找重復列
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.刪除重復數據
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 子句運做起來非常象 WHERE 子句， 只用于對那些滿足 HAVING 子句里面給出的條件的組進行計算。 其實，WHERE 在分組和聚集之前過濾掉我們不需要的輸入行， 而 HAVING 在 GROUP 之后那些不需要的組． 因此，WHERE 無法使用一個聚集函數的結果． 而另一方面，我們也沒有理由寫一個不涉及聚集函數的 HAVING． 如果你的條件不包含聚集，那么你也可以把它寫在 WHERE 里面， 這樣就可以避免對那些你準備拋棄的行進行的聚集運算．
? *聚集函數 指的是象count,max,sum,AVG等函數
?如果我們想知道那些銷售超過2個部件的供應商，使用下面查詢：
?
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
當帶有子查詢是不能用values和括號。例如：
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.關于在SQL中插入數據并返回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
9.曾經挽救過我的語句
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 10.在存儲過程中執行一個返回表的存儲過程
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
11.帶有輸出參數的存儲過程
??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?posted on 2007-03-16 21:55
EricWong 閱讀(1827)
評論(0) 編輯 收藏 所屬分類:
Sql server