SQL 常用语句

整理下工作中遇到的SQL一些基本操作，免得下次要用忘记了

1.将int型格式化成字符串，不足位数的前面补0：

SELECT  id,'T'+RIGHT('00000000'+CAST(id AS NVARCHAR(8)),8) AS NUM FROM Test 

2.表连接更新：

update a set Number=b.NUM from  a
join  b on a.Id=b.Id

3.创建SQL 递归函数

Create FUNCTION [dbo].[F_GetChildChapterId](@ID int)
RETURNS @tbChildId TABLE (ID int )--定义返回的表结构
AS
BEGIN        
 WITH F_GetChildChapterId(ID)    
 AS    
 (        
 　　SELECT Id AS ID FROM chapter WHERE ParentID=@ID        
　　 UNION ALL        
　　 SELECT chapter .Id FROM chapter 
 　　INNER JOIN F_GetChildChapterId ON F_GetChildChapterId.ID =chapter.ParentID 

) 
INSERT INTO @tbChildId SELECT ID FROM F_GetChildChapterId

END

4.SQL 函数 实现split　，做字符串分割

CREATE function [dbo].[Split](
@source nvarchar(4000),--需要分隔的字符串
@splitstr nvarchar(20))--分隔字符
returns @temp table(a nvarchar(100))--分隔后以表格形式返回
as
begin
    declare @i int
    set @source=rtrim(ltrim(@source))--去除左右空格
    set @i=charindex(@splitstr ,@source)
    while @i>=1
    begin
        insert @temp values(left(@source,@i-1))
        set @source=substring(@source,@i+1,len(@source)-@i)
        set @i=charindex(@splitstr ,@source)
    end
    if @source<>'\'
       insert @temp values(@source)
    return
end

4.将SQL数据表中的某字段将一行数据拆成多行：先将需要拆分的行格式化成XML，再使用OUTER APPLY

SELECT eid,uid
	    , number= CONVERT(xml,'<root><v>' + REPLACE(number, ',', '</v><v>') +'</v></root>') 		
	    INTO  #Vtemp
	    FROM A 

格式化成XML后

 使用outer apply 后

SELECT a.eid,a.uid，B.number
FROM #VTemp A OUTER APPLY( SELECT number= N.v.value('.', 'int') FROM A.number.nodes('/root/v') N(v) )B

　　 

5.将SQL 数据表中根据某字段，将多行合并成一行，这个方法很多

select id,nameStr=stuff
  (
   (select ','+name
   from [test] where a.id=b.id for xml path('')),1,1,''

  ) 
  from [test]  b
  group by id

6.SQL 自增字段重置

--- 删除原表数据,并重置自增列
truncate table tablename  --truncate方式也可以重置自增字段
--重置表的自增字段,保留数据
DBCC CHECKIDENT (tablename,reseed,0) 
-- 设置允许显式插入自增列
SET IDENTITY_INSERT tablename  ON
-- 当然插入完毕记得要设置不允许显式插入自增列
SET IDENTITY_INSERT tablename  Off

7.SQL 某一字段相同的记录只取一条

SELECT  *  FROM [E_ApproveLog]  a
where ProcessID=24 and not exists( select 1 from [E_ApproveLog]  b where b.ProcessTaskID=a.ProcessTaskID and a.ApproveTime < b.ApproveTime)

　　

8.统计数据表中列数量，及某字段为空的行数　　

select count(name) from syscolumns where id=(select id from sysobjects where xtype='u' and name='tb')

 

DECLARE @s NVARCHAR(2000),@i INT
SET @i=0
declare @id nvarchar(100)
set @id='e3d0f16d-a520-4016-a84d-ef6195499691'
SELECT @s=ISNULL(@s+'+',' select cast(sum(')+'CASE WHEN NULLIF('+QUOTENAME(Name)+','''') is null then 1 else 0 end',@i=@i+1
FROM syscolumns 
WHERE ID=OBJECT_ID('tb ')
SELECT @s
EXEC(@s+')*1.0/ sum('+@i+') as decimal(18,2)) from tb where ID like ''' + @id + '''')