SQL SERVER常用语句

1.以逗号分割参数

SELECT
    departmentid,
    code = (
        stuff(
            ( SELECT ',' + CONVERT ( VARCHAR, workcode ) FROM HrmResource WHERE departmentid = A.departmentid FOR XML PATH ( '' ) ),1,1,''
        )
    )
FROM
    HrmResource A
WHERE
    A.departmentid = 1406
GROUP BY
    departmentid

结果如下 ：

2.分割字段查询

SELECT
    f.requestid,
    stuff(
        (
        SELECT ',' + workcode FROM HrmResource e
        WHERE CAST ( e.id AS CHAR ) IN ( SELECT * FROM dbo.[Get_splitstr] ( '379,379,387,636,380,748', ',' ) ) FOR xml path ( '' )),1,1,''
    ) Reasons
FROM
    workflow_requestbase f
WHERE
    f.requestid = 831
GROUP BY
    requestid

查询结果：

注意Get_splitstr 是用的一个存储过程

-- 列如：select *  from [dbo].[Get_splitstr] ('1,2,3,4',',')
ALTER FUNCTION [dbo].[Get_splitstr] 
	(
		@SourceSql VARCHAR ( 8000 ), -- 需要分割的字符串
		@StrSeprate VARCHAR ( 100 ) -- 分隔符
	) 
returns @temp TABLE ( val VARCHAR ( 100 ) ) AS BEGIN
	DECLARE
		@ch AS VARCHAR ( 100 ) 
		SET @SourceSql = @SourceSql + @StrSeprate
	WHILE
			( @SourceSql <> '' ) BEGIN
				SET @ch = LEFT ( @SourceSql, charindex( ',',@SourceSql, 1 ) - 1 ) INSERT @temp
			VALUES
				( @ch ) 
				SET @SourceSql = stuff( @SourceSql, 1, charindex( ',',@SourceSql, 1 ), '' ) 
		END RETURN 
END

3.存储过程实现满足条件的存入临时表输出

游标循环 把满足条件存入临时表

ALTER PROCEDURE [dbo].[GetSex]
AS
BEGIN
 
	-- 定义错误返回信息
	declare @error int 
	
	--创建临时表#Tmp
create table #Tmp 
(
    ID int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1
		name_    varchar (200),
		sex_     int
    primary key (ID)      --定义ID为临时表#Tmp的主键      
);
	
	
	-- 定义接收参数
  declare @User_Name varchar(50)
  declare @Sex varchar(50)

  set @error=0
	
	 --定义游标
  declare demo_cursor cursor
	for (select lastname,sex from HrmResource)
	
	 --打开游标--
  open demo_cursor
 
	--开始循环游标变量--
  fetch next from demo_cursor into @User_Name,@Sex
	
	while @@FETCH_STATUS = 0  --返回被 FETCH语句执行的最后游标的状态--
	
	begin       
			
			-- 判断条件
			IF(@Sex = 0)
-- 				print @User_Name+'____'+@Sex
				insert into #Tmp(name_,sex_) VALUES (@User_Name,@Sex)


      set @error = @error + @@ERROR  --记录每次运行sql后是否正确，0正确

      fetch next from demo_cursor into @User_Name,@Sex  --转到下一个游标，没有会死循环

  end  

  close demo_cursor --关闭游标

  deallocate demo_cursor  --释放游标
	
	SELECT * FROM #Tmp;
 
END

4.存储过程封装分页查询

-- 分页查询
-- 条件查询案例： EXEC pageSearchList '20','1','uf_Account','id = 254147 AND name = ''安徽省万援医药有限公司'' '
ALTER PROC [dbo].[pageSearchList]
    @pageSize VARCHAR(50), -- 每页数量
    @pageNumber VARCHAR(20), -- 当前页码
    @tableName VARCHAR ( 50 ),  -- 表名
    @term VARCHAR ( 50 ) = NULL -- 条件
    AS
BEGIN
    DECLARE  @sqlfilter VARCHAR ( MAX ),
             @offset VARCHAR(20); --SQL语句起始索引
        
        SET @offset = convert(VARCHAR(20),((convert(int,@pageNumber) - 1) * convert(int,@pageSize)) + 1)  
        
        
        SET @sqlfilter = 'SELECT TOP ' + @pageSize + ' * FROM ' +  @tableName +
        
        ' WHERE 1 = 1 AND id >= ( SELECT MAX ( id ) FROM ( SELECT TOP ' + @offset + ' id FROM ' + @tableName + ' WHERE 1 = 1 ';
        
        -- 判断是否有条件
        IF(isnull(@term,'') <> '')
            SET @sqlfilter +=     @term + ' ORDER BY id ASC ) temp_max_ids ) ';
        ELSE
            SET @sqlfilter += ' ORDER BY id ASC ) temp_max_ids ) ';
        
        
        IF(isnull(@term,'') <> '')
            SET @sqlfilter +=     @term + ' ORDER BY id';
        ELSE
            SET @sqlfilter += ' ORDER BY id';
    
     -- print @sqlfilter
        
    
    EXEC ( @sqlfilter )
END

5.分割字符串 返回指定的参数

-- 列如：SELECT [dbo].[Get_stringBySplit]('1,2,3,4',',',1)
ALTER FUNCTION [dbo].[Get_stringBySplit] ( @List nvarchar ( 2000 ), --要分隔的字符串
	@SplitOn nvarchar ( 5 ), --分隔符
	@num1 INT -- 返回第几个参数
	) RETURNS VARCHAR ( 50 ) AS BEGIN
	DECLARE
		@i INT,
		@j INT,
		@p INT,
		@num INT,
		@aa VARCHAR ( 200 ) 
		SET @i = 0 
		SET @j = len( @SplitOn ) 
		SET @p = 0
	WHILE
			LEN( @List ) > 0 BEGIN
				
				SET @p = CHARINDEX( @SplitOn,@List )
			IF
				@p > 0 BEGIN
					
					SET @aa = SUBSTRING ( @List, 1,@p - 1 ) 
					SET @List = SUBSTRING ( @List,@p + @j, LEN( @List ) ) 
				END ELSE BEGIN
					
					SET @aa = @List 
					SET @List = '' 
				END 
					SET @i = @i + 1
				IF
					@i =@num1 BEGIN
					BREAK 
				END ELSE 
					SET @aa = '' 
				END RETURN @aa 
END

6.常用语句

-- 获取当前年
SELECT DateName( YEAR, GetDate( ) ) AS year
-- 获取当前月
SELECT DateName( month, GetDate( ) ) AS month
-- 获取当前日
SELECT DateName( day, GetDate( ) ) AS month
-- 获取上一个月
SELECT DATEPART( m, DATEADD( m, -1, GetDate( ) ) ) 

---替换字符串中的@字符
select replace('maomao365.com@blog.com',char(64),'') ---使用空格替换"@"字符
---替换字符串中tab
select replace('maomao365.com@blog.com',char(9),'') ---使用空格替换"tab"字符
---替换字符串中的回车键
select replace('maomao365.com@blog.com',char(13),'') ---使用空格替换"enter"字符


/为表加唯一索引
create unique index 索引名 on 表名(列名);
//创建覆盖索引
create index 索引名 on 表名(列名1,列名2);
create index 索引名 on 表名 (ID) include(Name);