项目中的几个SQL程序

一、行转列存储过程

CreatePROCEDURE[dbo].[Get_RowToCol] 
@LeaderIDvarchar(20),
@PlanIDint
AS
BEGIN
declare@sqlvarchar(max); 


set@sql='select distinct [EmployeeID],[EmployeeName],[Department]';
select@sql=@sql+',max(case [ID] when '''+str([ID])
+''' then [Value] else 0 end) ['+[Name]+']'
from (selectdistinct[ID],[Name]from[dbo].[V_RowTbl] 
) as cs;
--print @sql;
select@sql=@sql+' from [dbo].[V_RowTbl]'
+' where [LeaderID]='''+@LeaderID
+''' and [PlanID]='+str(@PlanID)
+'group by [EmployeeID],[EmployeeName],[Department]';
--print @sql;
exec(@sql);

二、一个比较好的分页存储过程

createPROCEDURE Sp_Conn_Sort
(
@tblNamevarchar(255), -- 表名

@strGetFieldsvarchar(1000) ='*', -- 需要返回的列 

@fldNamevarchar(255)='', -- 排序的字段名

@PageSizeint=40, -- 页尺寸

@PageIndexint=1, -- 页码

@doCountbit=0, -- 返回记录总数, 非 0 值则返回

@OrderTypebit=0, -- 设置排序类型, 非 0 值则降序
@strWherevarchar(1500)=''-- 查询条件 (注意: 不要加 where)
)
AS

declare@strSQLvarchar(5000) -- 主语句

declare@strTmpvarchar(110) -- 临时变量

declare@strOrdervarchar(400) -- 排序类型 

if@doCount!=0

begin

if@strWhere!=''

set@strSQL='select count(*) as Total from '+@tblName+' where '+@strWhere

else

set@strSQL='select count(*) as Total from '+@tblName 

end 

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin 

if@OrderType!=0

begin

set@strTmp='<(select min'

set@strOrder=' order by '+@fldName+' desc'

--如果@OrderType不是0，就执行降序，这句很重要！

end

else

begin

set@strTmp='>(select max'

set@strOrder=' order by '+@fldName+' asc'

end 

if@PageIndex=1

begin

if@strWhere!='' 

set@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from '+@tblName+' where '+@strWhere+''+@strOrder

else

set@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from '+@tblName+''+@strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from '

+@tblName+' where '+@fldName+''+@strTmp+'('+@fldName+') from (select top '+str((@PageIndex-1)*@PageSize) +''+@fldName+' from '+@tblName+''+@strOrder+') as tblTmp)'+@strOrder

if@strWhere!=''

set@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from '

+@tblName+' where '+@fldName+''+@strTmp+'('

+@fldName+') from (select top '+str((@PageIndex-1)*@PageSize) +''

+@fldName+' from '+@tblName+' where '+@strWhere+''

+@strOrder+') as tblTmp) and '+@strWhere+''+@strOrder
end 
end 
exec (@strSQL)

CREATE PROCEDURE P_DataPagination
                @tblName      VARCHAR(255),        -- 查询的表名
                @returnFields VARCHAR(1000) = '*', -- 需要返回的数据列
                @sortField      VARCHAR(255) = '', -- 排序的字段名
                @sortType    BIT = 0,              -- 设置排序类型, 非 0 值则降序
                @pageSize     INT = 10,            -- 页尺寸
                @pageIndex    INT = 1,             -- 页码
                @totalCount      BIT = 0,          -- 返回记录总数, 非 0 值则返回
                @strWhere     VARCHAR(1500) = ''   -- 查询条件 (注意: 不要加 where)                                           
AS
  DECLARE  @strSQL VARCHAR(5000)  -- 主语句
  DECLARE  @strTmp VARCHAR(110)   -- 临时变量
  DECLARE  @strOrder VARCHAR(400) -- 排序语句
  IF @totalCount != 0             --如果@totalCount传递过来的不是0，就执行总数统计。
    BEGIN
      SET @strSQL = 'select count(*) as TotalCount from [' + @tblName + ']'
      IF @strWhere != ''
      begin
        SET @strSQL = @strSQL + ' where ' + @strWhere
      end
    END
    
  ELSE
    BEGIN
      IF @sortType != 0
        BEGIN
          SET @strTmp = '<(select min'
          SET @strOrder = ' order by [' + @sortField + '] desc'
        END
       ELSE
        BEGIN
          SET @strTmp = '>(select max'
          SET @strOrder = ' order by [' + @sortField + '] asc'
        END
      IF @pageIndex = 1
        BEGIN
          Set @strSql = 'select top ' + Str(@pageSize) + ' ' + @returnFields + '  from [' + @tblName + '] '
          IF @strWhere != ''
          begin
            SET @strSQL = @strSql + ' where ' + @strWhere 
          end
          SET @strSql = @strSql + ' ' + @strOrder        
        END
       ELSE
        BEGIN
          SET @strSQL = 'select top ' + Str(@pageSize) + ' ' + @returnFields + '  from [' + @tblName + '] where [' + @sortField + ']' + @strTmp + '([' + @sortField + ']) from (select top ' + Str((@pageIndex - 1) * @pageSize) + ' [' + @sortField + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder
          IF @strWhere != ''
          begin
            SET @strSQL = 'select top ' + Str(@pageSize) + ' ' + @returnFields + '  from [' + @tblName + '] where [' + @sortField + ']' + @strTmp + '([' + @sortField + ']) from (select top ' + Str((@pageIndex - 1) * @pageSize) + ' [' + @sortField + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
          end
        END
    END
  EXEC(@strSQL)
GO

SELECT TOP PageSize *
FROM TableName
WHERE ID NOT IN
        (SELECT TOP PageSize*(PageIndex-1) id  FROM TableName ORDER BY id)
ORDER BY ID

 

DECLARE @pageIndex INT = 1
DECLARE @pageSize INT = 10
DECLARE @queryPar NVARCHAR(50) = '张辽'

SELECT COUNT(0) FROM raw.dbo.Raw_UserInfo
        WHERE  RealName  LIKE '%' + @queryPar + '%'

;WITH cte AS(
    SELECT ID
    , LoginName
    , RealName
    , Mobile
    , HousingAddr
    , rn = ROW_NUMBER() OVER(ORDER BY  ID) 
        FROM raw.dbo.Raw_UserInfo
            WHERE  RealName  LIKE '%' + @queryPar + '%'
)
SELECT ID
    , LoginName
    , RealName
    , Mobile
    , HousingAddr FROM cte
    WHERE rn > (@pageIndex - 1) * @pageSize
        AND rn <= @pageIndex * @pageSize

DECLARE @pageIndex INT = 1
DECLARE @pageSize INT = 10
DECLARE @queryPar NVARCHAR(50) = '张辽'

;WITH cte AS(
    SELECT *
    , rn = ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY ID) 
    FROM raw.dbo.Raw_UserInfo
            WHERE  RealName  LIKE '%' + @queryPar + '%'
),sc AS (
     SELECT TOP 1 cn = COUNT(0) OVER()  -- 开窗功能， 这一段非常固定， 请放心使用。对，位置就在这里，放在 cte 中功能效果一样，但不能提速。
        FROM cte
  )     -- 记录数
SELECT  ID
    , LoginName
    , RealName
    , Mobile
    , HousingAddr
    , sc.cn FROM cte
  CROSS APPLY sc
    WHERE cte.rn > (@pageIndex - 1) * @pageSize
        AND cte.rn <= @pageIndex * @pageSize

 三、查询库里的所有表的使用情况

exec sp_MSforeachtable 'select ''?''','?', 'sp_spaceused ''?''', 'SELECT count(*) FROM ? ' 

 四、删除当前数据库所有表中的数据

sp_MSforeachtable 'Delete from ?'
sp_MSforeachtable 'Truncate Table ?'

 五、NVarchar无法查询的过滤函数

create function FilterNChar(@str nvarchar(255))
returns nvarchar(255)
as 
begin
declare @ret nvarchar(255);
set @ret = LOWER(REPLACE(REPLACE(REPLACE(LTrim(RTrim(@str)),CHAR(9),''),CHAR(10),''),CHAR(13),''));
return @ret;
end

 六、两表同步

-- 查询已删除的记录  
02.SELECT * FROM [OLD] WHERE [ID] IN((SELECT [ID] FROM [OLD]) EXCEPT (SELECT [ID] FROM [NEW]));  
03.  
04.-- 查询已增加的记录  
05.SELECT * FROM [NEW] WHERE [ID] IN((SELECT [ID] FROM [NEW]) EXCEPT (SELECT [ID] FROM [OLD]));  
06.  
07.-- 查询已更新的记录  
08.SELECT * FROM [NEW] WHERE [ID] IN (((SELECT [ID] FROM [OLD]) INTERSECT (SELECT [ID] FROM [NEW])) EXCEPT (SELECT [ID] FROM((SELECT * FROM [OLD]) INTERSECT (SELECT * FROM [NEW]))AS T));  

 七、自增ID

select @@identity

select SCOPE_IDENTITY()

select IDENT_CURRENT("tablename")

 八、排名/百分比

BEGIN

#创建Index临时表
DROP TEMPORARY TABLE IF EXISTS New_INDEX_Table;
Create TEMPORARY TABLE New_INDEX_Table 
    (
            SELECT t.BaseDetailId,t.INo,
                    (CASE WHEN @I=t.INo
                        THEN @R:=@R+1
                    ELSE
                        @R:=@R+1 AND @I:=t.INo
                    END)+1 AS ProdIndex
            FROM 
            BS_BaseDetail t,(SELECT @R:=0,@I:=NULL) tt
            WHERE t.BaseId=BSId
            ORDER BY INo, IndicatorAmnt DESC
  );
#创建Contri临时表
DROP TEMPORARY TABLE IF EXISTS New_CONTRI_Table;
Create TEMPORARY TABLE New_CONTRI_Table 
    (
            SELECT a.BaseDetailId,a.INo,a.IndicatorAmnt,
            a.IndicatorAmnt/b.TOTAL AS ProdContri 
            FROM BS_BaseDetail a,            
            (
                    SELECT INo, BaseDetailId, BSId, BaseId, SUM(IndicatorAmnt) TOTAL FROM BS_BaseDetail 
                    WHERE BaseId=BSId 
                    GROUP BY INo
                    ORDER BY INo, IndicatorAmnt DESC
            ) b
            WHERE a.BaseId=BSId and a.INo = b.INo
  );

    #ProdIndex Calculate
    UPDATE BS_BaseDetail T0
    INNER JOIN New_INDEX_Table T1 
        ON T0.BaseDetailId=T1.BaseDetailId
    SET T0.ProdIndex=T1.ProdIndex
    WHERE T0.BaseId=BSId;
    
    #ProdContri Calculate        
    UPDATE BS_BaseDetail D0
    INNER JOIN New_CONTRI_Table D1
        ON D0.BaseDetailId=D1.BaseDetailId
    SET D0.IndicatorContri=D1.ProdContri
    WHERE D0.BaseId=BSId;

DROP TEMPORARY TABLE IF EXISTS New_INDEX_Table;
DROP TEMPORARY TABLE IF EXISTS New_CONTRI_Table;

END

 九、死锁排查

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName 
from sys.dm_tran_locks where resource_type='OBJECT'

kill spid