SqlServer的各种语法

1.嵌套游标

declare my_cursor cursor for 
select Id,AreaName from areaInfo where ParentId=0
open my_cursor 
declare @AreaName varchar(50) = '123'
declare @Pid varchar(50) = '123'
declare @Index int = 1
fetch next from my_cursor into @Pid,@AreaName
while @@FETCH_STATUS=0
begin
set @AreaName=convert(varchar(1000),@Index) +'.'+ @AreaName
select @AreaName

declare my_cursor2 cursor for 
select Id,AreaName from areaInfo where ParentId=@Pid
open my_cursor2 
declare @AreaName2 varchar(50) = '123'
declare @Pid2 varchar(50) = '123'
declare @Index2 int = 1
fetch next from my_cursor2 into @Pid2,@AreaName2
while @@FETCH_STATUS=0
begin
set @AreaName2=convert(varchar(1000),@Index2) +'.'+ @AreaName2
select @AreaName2
set @Index2=@Index2+1
fetch next from my_cursor2 into @Pid,@NodeName
end
close my_cursor2
deallocate my_cursor2

set @Index=@Index+1
fetch next from my_cursor into @Pid,@NodeName
end
close my_cursor
deallocate my_cursor

2.递归查找

WITH TEMP   --递归
     AS (SELECT [Id]
				  ,[NodeName]
				  ,[ParentId]
				  ,[ParentIdList]
         FROM   [dbo].[cfgAreaInfo]
         WHERE  Id = 1   --查询当前部门
         UNION ALL
         SELECT B.[Id]
				  ,B.[NodeName]
				  ,B.[ParentId]
				  ,B.[ParentIdList]
         FROM   TEMP A
                INNER JOIN [dbo].[cfgAreaInfo] B
                        ON B.ParentId = A.Id)
SELECT [Id]
	,[NodeName]
	,[ParentId]
	,[ParentIdList]
FROM   TEMP   --获取递归后的集合

3.查找某列是最大或最小值的整行数据not exists

select * from teacher a where not exists(select 1 from teacher b where a.createDate < b.createDate)

4.stuff配合FOR XML PATH串起结果

STUFF((
	SELECT ',' + NAME
	FROM table
	WHERE table.Id>0
	FOR XML PATH('')
), 1, 1, '')

5.从execl导入数据到表

(1).安装支持

https://www.microsoft.com/zh-cn/download/details.aspx?id=13255

(2).开启服务

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

(3).语句

insert [表名] ([列名],t2,t3,t4)
select F2,F3,F4,F5 --execl中的列名 统一都是f1 f2
from OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 8.0;HDR=YES;DATABASE=D:\work\7788\文档\2021年公寓在住老人花名册.xls'-- 文件
,老人花名册$)--老人花名册$ 为表格中的具体表+$

(4).关闭服务

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

(5)清楚表数据并清楚自增列值

TRUNCATE TABLE kcAllKey;

6.查看执行过的语句

SELECT TOP 1000 
       ST.text AS '执行的SQL语句',
       QS.execution_count AS '执行次数',
       QS.total_elapsed_time AS '耗时',
       QS.total_logical_reads AS '逻辑读取次数',
       QS.total_logical_writes AS '逻辑写入次数',
       QS.total_physical_reads AS '物理读取次数',       
       QS.creation_time AS '执行时间' ,  
       QS.*
FROM   sys.dm_exec_query_stats QS
       CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE  QS.creation_time BETWEEN '2015-08-01 00:00:00' AND '2015-09-02 11:00:00' 
ORDER BY
     QS.total_elapsed_time DESC