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 阅读(1797)
评论(0) 编辑 收藏 所属分类:
Sql server