SQL语句集合

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  
  

12.sql2005的翻页

WITH OrderedOrders AS
(
    SELECT 11 as 'ddd' FROM Employees 
)
SELECT  COUNT(1) FROM OrderedOrders;
WITH OrderedOrders AS
(
    SELECT Employees.*, ROW_NUMBER() OVER(ORDER BY empid) AS ROW_NUMBER FROM Employees 
)
SELECT * FROM OrderedOrders WHERE ROW_NUMBER > 2 AND ROW_NUMBER <= 5;