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、

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '% in (@%)%' 
    AND ROUTINE_TYPE='PROCEDURE'

4、How to use id comma-seperated string in static sql not dynamic sql?

4.1

--- 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