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

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.

 

SELECT distinct so.name

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id = so.id

WHERE charindex('t_swrequests', text) > 0

 

select distinct specific_name from information_schema.routines

where lower(routine_definition) like '%t_swrequests%'

1、

代码

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 WHERE CHARINDEX(','+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