SQL小白点滴（一）含”聚合函数“等

绝地灬酷狼 2021-12-12 22:55 225阅读 0赞

0、http://www.mssqltips.com/tip.asp?tip=1294

This is VERY helpful, thanks a lot

During the Data Migration of last release, first I use “View Dependencies” , but it doesn’t cover all for omit those behind dynamic SQL.

SELECTdistinct so.name

FROM syscomments sc

INNERJOIN sysobjects so ON sc.id = so.id

WHEREcharindex('t\_swrequests',text)> 0

selectdistinct specific\_name frominformation\_schema.routines

wherelower(routine\_definition)like'%t\_swrequests%'

1、

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

ALTER  PROCEDURE  \[ dbo \] . \[ Usp\_processswgrouprequest \]  @GrpSWRequestId  INT   
 AS   
 DECLARE  @UpdateTime  DATETIME   
 DECLARE  @Count  INT   
 DECLARE  @CompanyId  INT   
 DECLARE  @SWRequestStatusId  INT   
 DECLARE  @SWApprovalStatusPending  INT ;  
 DECLARE  @SWRequestId  INT   
 DECLARE  @Err  INT   
  
 SELECT  @UpdateTime  =  Getdate ()  
  
 SET  @Err  =  **0**   
 SET  @SWRequestStatusId  =  **1**  \--  RequestStatusPendingApproval    
  SET  @SWApprovalStatusPending  =  **2**   
 SET  @Count = **0**   
  
 DECLARE  curSWRequest  CURSOR  FOR   
 SELECT  DISTINCT  V\_Users.CompanyId  
 FROM  T\_SWRequestUsers  
 INNER  JOIN  V\_Users  
 ON  T\_SWRequestUsers.UserId  =  V\_Users.UserId  
 WHERE  ( T\_SWRequestUsers.SWRequestId  =  @GrpSWRequestId  )  
  
 BEGIN  TRANSACTION   
  
 OPEN  curSWRequest  
  
 FETCH  NEXT  FROM  curSWRequest  INTO  @CompanyId   
  
 WHILE  (  **@@FETCH\_STATUS**  =  **0**  )  
 BEGIN   
 \--  insert the SWRequest for each company    
  \--  PRINT @CompanyId    
  SET  @UpdateTime  =  Dateadd (ms,  @Count ,  Getdate ())  
  
 INSERT  INTO  T\_SWRequests  
(RequesterId,  
RequestDate,  
SWRequestStatusId,  
Routed,  
CompanyId,  
GroupId,  
ContactFirstName,  
ContactLastName,  
ContactEmail,  
CCEmails,  
OtherInformation,  
UpdateBy,  
UpdateTime,  
ParentSWRequestId,  
NotifyUsers)  
 SELECT  RequesterId,  
 @UpdateTime ,  
 @SWRequestStatusId ,  
 ' Y ' ,  
 @CompanyId ,  
 NULL ,  
ContactFirstName,  
ContactLastName,  
ContactEmail,  
CCEmails,  
OtherInformation,  
UpdateBy,  
 @UpdateTime ,  
 @GrpSWRequestId ,  
NotifyUsers  
 FROM  T\_SWRequests  
 WHERE  SWRequestId  =  @GrpSWRequestId   
  
 IF  **@@ERROR**  !=  **0**   
 BEGIN   
 ROLLBACK  WORK   
  
 RAISERROR ( ' usp\_ProcessGroupRequest:Error In Inserting record to T\_SWRequest. Transaction is aborted. ' ,  
 **16** ,  
 **1** )  
  
 GOTO  end\_program  
 END   
  
 SET  @SWRequestId  =  **@@IDENTITY**   
  
 \--  PRINT @SWRequestId    
  \--  Insert Users    
  INSERT  INTO  T\_SWRequestUsers  
(SWRequestId,  
UserId,  
UpdateBy,  
UpdateTime)  
 SELECT  @SWRequestId ,  
T\_SWRequestUsers.UserId,  
T\_SWRequestUsers.UpdateBy,  
 @UpdateTime   
 FROM  T\_SWRequestUsers  
 INNER  JOIN  V\_Users  
 ON  T\_SWRequestUsers.UserId  =  V\_Users.UserId  
 WHERE  ( T\_SWRequestUsers.SWRequestId  =  @GrpSWRequestId  )  
 AND  V\_Users.CompanyId  =  @CompanyId   
  
 IF  **@@ERROR**  !=  **0**   
 BEGIN   
 ROLLBACK  WORK   
  
 RAISERROR ( ' usp\_ProcessGroupRequest:Error In Inserting record to T\_SWRequestUsers. Transaction is aborted. ' ,  
 **16** ,  
 **1** )  
  
 GOTO  end\_program  
 END   
  
 \--  Insert SLAs    
  INSERT  INTO  T\_SWRequestSLAs  
(SWRequestId,  
SLAId,  
UpdateBy,  
UpdateTime)  
 SELECT  DISTINCT  @SWRequestId ,  
T\_SWRequestSLAs.SLAId,  
T\_SWRequestSLAs.UpdateBy,  
 @UpdateTime   
 FROM  T\_SWRequestSLAs  
 INNER  JOIN  T\_GroupSLA  
 ON  T\_SWRequestSLAs.SLAId  =  T\_GroupSLA.SLAId  
 WHERE  ( T\_SWRequestSLAs.SWRequestId  =  @GrpSWRequestId  )  
 AND  T\_GroupSLA.CompanyId  =  @CompanyId   
  
 IF  **@@ERROR**  !=  **0**   
 BEGIN   
 ROLLBACK  WORK   
  
 RAISERROR ( ' usp\_ProcessGroupRequest:Error In Inserting record to T\_SWRequestSLA. Transaction is aborted. ' ,  
 **16** ,  
 **1** )  
  
 GOTO  end\_program  
 END   
  
 \--  Insert Softwares    
  INSERT  INTO  T\_RequestSWs  
(SWRequestId,  
DocumentId,  
SWApprovalStatusId,  
RequestedBy,  
UpdateBy,  
UpdateTime)  
 SELECT  @SWRequestId ,  
DocumentId,  
 @SWApprovalStatusPending ,  
RequestedBy,  
UpdateBy,  
 @UpdateTime   
 FROM  T\_RequestSWs  
 WHERE  SWRequestId  =  @GrpSWRequestId   
  
 IF  **@@ERROR**  !=  **0**   
 BEGIN   
 ROLLBACK  WORK   
  
 RAISERROR ( ' usp\_ProcessGroupRequest:Error In Inserting record to T\_RequestSWs. Transaction is aborted. ' ,  
 **16** ,  
 **1** )  
  
 GOTO  end\_program  
 END   
  
 SET  @Count  =  @Count  \+  **1**   
  
 FETCH  NEXT  FROM  curSWRequest  INTO  @CompanyId   
 END   
  
 UPDATE  T\_SWRequests  
 SET  Routed  =  ' Y ' ,  
SWRequestStatusId  =  **2**   
 WHERE  SWRequestId  =  @GrpSWRequestId   
  
 COMMIT  WORK   
  
END\_PROGRAM:  
  
 CLOSE  curSWRequest  
  
 DEALLOCATE  curSWRequest  
  
 RETURN

2、declare @A as varchar(1000) SET @A = replace(( select top 10 lastname as 'data()' from t\_users for xml path('')), ' ',';') print @A

但是这个replace是替换所有，这个不是我想要的，除非是ID串，名称串就做不到了...究竟要如何做MSSQL的聚合呢？

[11:29:08 AM] James: http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

CREATE  TABLE  \[ dbo \] . \[ Table1 \] (  
 \[ id \]  \[ int \]  NULL ,  
 \[ name \]  \[ varchar \] ( **50** )  NULL ,  
 \[ school \]  \[ varchar \] ( **50** )  NULL ,  
 \[ year \]  \[ int \]  NULL ,  
 \[ score \]  \[ int \]  NULL   
)  ON  \[ PRIMARY \]   
  
 GO   
  
 INSERT  INTO  table1 (id,name,school, year ,score)  VALUES  ( **1** , ' Jerra ' , ' sc1 ' ,  **2002** ,  **100** )  
 INSERT  INTO  table1 (id,name,school, year ,score)  VALUES  ( **2** , ' Jerra ' , ' sc2 ' ,  **2004** ,  **60** )  
 INSERT  INTO  table1 (id,name,school, year ,score)  VALUES  ( **3** , ' Tom ' , ' sc3 ' ,  **2002** ,  **40** )  
 INSERT  INTO  table1 (id,name,school, year ,score)  VALUES  ( **4** , ' Jerry ' , ' sc4 ' ,  **2004** ,  **90** )  
 INSERT  INTO  table1 (id,name,school, year ,score)  VALUES  ( **5** , ' Tom ' , ' sc5 ' ,  **2005** ,  **80** )  
 GO   
  
 select  name,  
 replace ((  select  school  as  ' data() '   
 from  table1  
 for   
xml path( '' )  
),  '  ' ,  ' / ' )  as  school,  
 replace ((  select  year  as  ' data() '   
 from  table1  
 for   
xml path( '' )  
),  '  ' ,  ' / ' )  as  year ,  
 sum (score)  as  score  
 from  table1  
 group  by  name

有一个QQ群友提供的方法，解决了空格问题！

UPDATE  table1  SET  school = REPLACE (school, ' sc ' , ' sc  ' )  
  
 GO   
  
 SELECT  \*   
 FROM   
(  
 SELECT  name, SUM (score)  AS  score  FROM  table1  GROUP  BY  name  
)  AS  A  
 OUTER  APPLY  
(  
 SELECT  school  =  STUFF ( REPLACE (( REPLACE (( SELECT  school  FROM  dbo.table1 N  WHERE  NAME  =  A.name  FOR  XML AUTO), ' <N school=" ' , ' / ' )), ' "/> ' , '' ), **1** , **1** , '' )  
, \[ year \]  =  STUFF ( REPLACE (( REPLACE (( SELECT  \[ year \]  FROM  dbo.table1 N  WHERE  NAME  =  A.name  FOR  XML AUTO), ' <N year=" ' , ' / ' )), ' "/> ' , '' ), **1** , **1** , '' )  
  
) N

3、

\-- \- converts ntext list to individual item in a table    
 ALTER  FUNCTION  \[ dbo \] . \[ udf\_TxtList2Tbl \]  ( @list  NTEXT , @separator  CHAR ( **1** ))  
 RETURNS  @tbl  TABLE  (element  NVARCHAR ( **300** )  NOT  NULL )  AS   
 BEGIN   
 DECLARE  @pos  INT ,  
 @textpos  INT ,  
 @nextpos  INT ,  
 @str  NVARCHAR ( **4000** ),  
 @tmpstr  NVARCHAR ( **4000** ),  
 @leftover  NVARCHAR ( **4000** )  
  
 SET  @textpos  =  **1**   
 SET  @leftover  =  ''   
  
 WHILE  @textpos  <=  DATALENGTH ( @list )  /  **2**   
 BEGIN   
 SET  @nextpos  =  @textpos  \+  **4000**  \-  DATALENGTH ( @leftover )  /  **2**   
 SET  @tmpstr  =  LTRIM ( @leftover  \+  SUBSTRING ( @list ,  @textpos ,  @nextpos  \-  **1** ))  
 SET  @textpos  =  @nextpos   
 SET  @pos  =  CHARINDEX ( @separator ,  @tmpstr )  
  
 WHILE  @pos  >  **0**   
 BEGIN   
 SET  @str  =  substring ( @tmpstr ,  **1** ,  @pos  \-  **1** )  
 INSERT  @tbl  (element)  VALUES ( RTRIM ( LTRIM ( @str )))  
 SET  @tmpstr  =  LTRIM ( SUBSTRING ( @tmpstr ,  @pos  \+  **1** ,  LEN ( @tmpstr )))  
 SET  @pos  =  CHARINDEX ( @separator ,  @tmpstr )  
 END   
  
 SET  @leftover  =  @tmpstr   
 END   
  
 SET  @leftover  =  LTRIM ( RTRIM ( @leftover ))  
 IF  @leftover  <>  ''   
 INSERT  @tbl  (element)  VALUES ( @leftover )  
  
 RETURN   
  
 END

AND EXISTS (SELECT ST.element FROM udf\_TxtList2Tbl(@SoftwareIds,',') ST WHERE ST.element = S.DocumentId )

4.2

WHERE CHARINDEX(',' + convert(varchar(max),D.DocumentId )+ ',', ',' + @DocumentIDs + ',') > 0

SELECT\*FROM tbname WHERECHARINDEX(','\+RTRIM(fdname)\+',',','\+@idlist\+',')>**0**

SELECT  \*  FROM  tbname  WHERE  PATINDEX ( ' %, ' \+ RTRIM (fdname) \+ ' ,% ' , ' , ' \+ @idlist \+ ' , ' ) > **0**   
 SELECT  \*  FROM  tbname  WHERE  ' , ' \+ @idlist \+ ' , '  LIKE  ' %, ' \+ RTRIM (fdname) \+ ' ,% '

5、

WHERE p.\[Name\] LIKE ISNULL('%'+@ProductName+'%', p.\[Name\])

小白的我觉得它很有趣 ...

6、小白的我觉得shen的这套思路很赞 ...

update  T\_ProductPLMs  
 set  IsPrimary  =  ' Y '   
 from  ( Select  ProductId  from  T\_ProductPLMs  group  by  ProductId  having  sum ( case  IsPrimary  when  ' Y '  then  **1**  else  **0**  end )  =  **0** ) a  
 where  T\_ProductPLMs.PLMId = @NewPLMId  and  T\_ProductPLMs.ProductId  =  a.ProductId

转载于:https://www.cnblogs.com/syveen/archive/2010/04/05/1704461.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211210/ce7ed13035704f58981e61c672a69da7.png